Converting INT to DATETIME 3

[KizMar]

How would you convert an 8 digit int '20020912' (represents a date) into a datetime data type?

 

[ClaireHCM]

declare @v1 int
set @v1 = 20020912

select convert(datetime,CONVERT(VARCHAR(20),@V1))

OR

select convert(datetime,CONVERT(VARCHAR(20),20001201))

 

[swampBoogie]

cast(cast(@intVariable as varchar(8)) as datetime)

 

[SQLBill]

see the BOL for CONVERT.

Try this:

SELECT CONVERT(DATETIME, '20020912')

-SQLBill

 

[KizMar]

Still getting Syntax errors. Here are more specifics on what I'm trying to do:

SQL Statement:
Declare @String2 Table (date2 datetime)

Insert @String2

Select convert(datetime, (convert(char(8), w.ActiveDate)))

From DateTable w

Note: ActiveDate (int, Null)

The error I'm getting:
Server: Msg 241, Level 16, State 1, Line 6
Syntax error converting datetime from character string.

 

[SQLSister]

Are you absolutely sure that all the values in the field will convert to a valid date? Usually when people don;t use datetime fields for dates, the users enter incorrect information in the database. You might have one or more values that says something like 20020230 which is acceptable integer data but is not a real date.

 

[ClaireHCM]

But the syntax error is not the same when user insert 20020230 or 20020350

If the error is because of this kind of junk insert,it should return

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
-------------------------------------------------------
I think the query KizMar wrote is wrong.You should check the correct syntax

 

[KizMar]

Very good point... That was the problem, there were invalid fields. THANKS!!!!!

 

[KizMar]

They were Zero's by the way. =)