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.
| Author |
Topic |
|
BobRoberts
Posting Yak Master
109 Posts |
 Posted - 2011-12-30 : 17:07:09
|
| I'm working with an old table that has a date stored as 3 numeric field3 (FLDYR, FLDMO, FLDDA). I've added a column to this table defined as a smalldatetime field. I'm trying to write a script to load this field from the 3 date fields, but get an error whenever it encounters a year that's a single digit. The error is:"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."As it happens, there is no FLDYR in the table greater than "11", so the century would never be "19".If I could somehow create an edit mask for the FLDYR, then I could translate a "9" year to a "09", as well as an "11" to an "11", but I can't find out if SQL has that capability. As it works now, it's trying to create the year "209" for a "9" year.Is there a way of doing this that would work?Here is the update statement:UPDATE testfile SET SMALLDATEFIELD = '20'+cast(FLDYR as varchar)+'-'+cast(FLDMO as varchar)+'-'+cast(FLDDA as varchar) + ' 00:00:00' |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-30 : 17:15:27
|
Would this work for you?CAST('20' + RIGHT('0'+ CAST(FLDYR*10000+FLDMO*100+FLDDA AS VARCHAR(8)),6) AS SMALLDATETIME) |
 |
|
|
BobRoberts
Posting Yak Master
109 Posts |
Posted - 2011-12-30 : 17:28:28
|
quote:
Originally posted by sunitabeck
Would this work for you?CAST('20' + RIGHT('0'+ CAST(FLDYR*10000+FLDMO*100+FLDDA AS VARCHAR(8)),6) AS SMALLDATETIME)
Yes, that works great - thanks! |
|
|
|
|
|
|
Simple Way to Load a Date from Its Parts