datetime and condense

Question

Post reply

datetime and condense

philosophaie

SSC Eights!

Points: 963
More actions
January 27, 2014 at 5:56 pm

#303504

How do you write the date and time into datetime?
And correct my code.
Input a value for column Latin, add it to database allcount, if max-min<10 condense.
CREATE PROCEDURE [IncMax] (@Latin INT) AS
DECLARE @max-2 INT, @min-2 INT, @col3 INT,@datetime DATETIME;
@datetime=//input date and time//
INSERT INTO allcount (datetime,Latin) VALUES (@datetime,@Latin)
SELECT
@min-2=MIN(ida)
,@max=MAX(ida)
FROM allcount;
IF (@max-@min)>10
BEGIN
SELECT
@col3 = SUM(Latin)
FROM allcount;
DELETE FROM allcount;
INSERT INTO allcount (datetime, Latin) VALUES (@datetime,@col3);
END;--IF
END;--PROC
GO

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

philosophaie (1/27/2014)
How do you write the date and time into datetime?

You mean assigning a literal value to a datetime variable?

DECLARE @datetime DATETIME;

SET @datetime = '2014-01-28T08:26:14';

SELECT @datetime;

philosophaie (1/27/2014)
And correct my code.

You mean: "Can you check if there are errors in the code please?"

We're not your employees.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

Koen Verbeeck (1/28/2014)
philosophaie (1/27/2014)
How do you write the date and time into datetime?
You mean assigning a literal value to a datetime variable?
DECLARE @datetime DATETIME;
SET @datetime = '2014-01-28T08:26:14';
SELECT @datetime;
philosophaie (1/27/2014)
And correct my code.
You mean: "Can you check if there are errors in the code please?"
We're not your employees.

He could also use

DECLARE @datetime DATETIME;

SET @datetime = Getdate();

SELECT @datetime;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

philosophaie SSC Eights! Points: 963 More actions · Answer 3

I tried to use the keyword "CONVERT" to get form "120" or

yyyy-mm-dd hh:mm:ss

The time is in 24hour time.

SET @datetime = Getdate();

CONVERT (DATETIME, @datetime , 120);

It did not work. Any suggestions?

from

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

You forgot the SELECT and didn't declare the variable.

edit: and if you want to see the effect of the formatting, you should convert to string.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

philosophaie SSC Eights! Points: 963 More actions · Answer 5

Error code:

Error -2147217900
Incorrect syntax near the keyword 'CONVERT'.
CREATE P*****

Is there something wrong with:

CREATE PROCEDURE [IncMaxallcount] (@Latin INT) AS

DECLARE @max-2 INT, @min-2 INT, @col3 INT,@datetime DATETIME;

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

No.

The error talks about CONVERT. Your posted code doesn't contain CONVERT.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

philosophaie SSC Eights! Points: 963 More actions · Answer 7

If I take out"

CONVERT (DATETIME, @datetime , 120);

The error says:

CREATE P*****

Is there something wrong with:

CREATE PROCEDURE [IncMaxallcount] (@Latin INT) AS

Then we can concentrate on CONVERT.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

philosophaie (1/29/2014)
Is there something wrong with:
CREATE PROCEDURE [IncMaxallcount] (@Latin INT) AS

No. The error is, as the error message said, "Incorrect syntax near the keyword 'CONVERT'.", not near the keyword PROCEDURE

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

philosophaie SSC Eights! Points: 963 More actions · Answer 9

OK

Where is the error coming from in CONVERT

from Microsoft site:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] );

data_type is DATETIME

length is 8 because I formatted the datetime in my server as 8 (30 if left blank)

expression is @datetime

style is 120 for yyyy-mm-dd hh:mm:ss (The time is in 24hour time.)

I tried changing style to 10 mm-dd-yy to fit DATETIME length 8

I tried changing DATETIME to VARCHAR(50) FOR STYLE 120.

where am I going wrong?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 10

We would be able to help if you would post the entire CREATE PROC statement, not just the first line. My guess is that you still forgot to put SELECT before CONVERT.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP