trouble converting smalldatetime column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

trouble converting smalldatetime column

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2007-08-21 : 15:24:27

I have a data field that's datatype int and I need to convert it to smalldatetime, however, I get one row erroring out due to a typo in the data that has a date of 21190101. What's wrong with my case statement to set that one typo to 20790101?

'SD' = Case When pro_date > 20790000 Then
pro_date = '20790101'
Else CAST(CAST(pro_date AS VARCHAR) AS SMALLDATETIME) End,

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-08-21 : 15:50:28

You still need to cast your substitution value:

set 'SD' = Case When pro_date > 20790000 Then '2079-01-01'
Else CAST(CAST(pro_date AS VARCHAR) AS SMALLDATETIME) End

e4 d5 xd5 Nf6

sshelper
Posting Yak Master

216 Posts

Posted - 2007-08-21 : 15:50:41

Try this:

'SD' = Case When pro_date > 20790000 Then
CAST('20790101' AS SMALLDATETIME)
Else CAST(CAST(pro_date AS VARCHAR) AS SMALLDATETIME) End,

SQL Server Helper
http://www.sql-server-helper.com

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-08-21 : 15:52:25

Revision: to get the correct datatype returned you may need to do it this way:

set 'SD' = Case When pro_date <= 20790000 Then CAST(CAST(pro_date AS VARCHAR) AS SMALLDATETIME) else '2079-01-01' end

e4 d5 xd5 Nf6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 16:01:15

set 'SD' = Case When pro_date BETWEEN 19000101 AND 20781231 Then CAST(CAST(pro_date AS VARCHAR) AS SMALLDATETIME) else '2079-01-01' end

Dates before 19000101 can't be translated as SMALLDATETIME

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources