| Author |
Topic |
|
mbskier12
Starting Member
7 Posts |
 Posted - 2007-12-04 : 10:19:20
|
| I extract a date value from and existing table with the SmallDateTime format. I put it into a variable with a declaration of SmallDateTime. I then want to use that variable as part of an insert statement. When I try to run the procedure and use the variable, I am given a conversion error. I have tried running the value through IsDate, and the value is a date. I have printed to the screen, and all I can see is that for some reason the format is changing to 'Nov 30 2007'. When I run a simple SELECT statement the format is '2007-11-30 00:00:00'I tried running through two Converts to change to a VarChar value then back to a SmallDateTime, but still get the error. I am inserting to a table with the date format of SmallDateTime.If someone could explain why this is occurring? |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-12-04 : 10:49:14
|
Not sure what the actual issue is but I do know that PRINT always formats the date in that way. Can you post the INSERT statement that is failing for you? Also, what datatype is the column you are inserting into?
Future guru in the making. |
 |
|
|
mbskier12
Starting Member
7 Posts |
Posted - 2007-12-04 : 10:56:30
|
| This is coming from the Proc:Declare @FILEDATE SmallDateTimeSELECT @FILEDATE = FileDate FROM DataLoad WHERE LoadID = 100SET @SQL = ' Insert DFTable( LoadID, LoadStatus, LoadDate, SysDate) Select n.LoadID, LoadStatus=0, LoadDate= IsNull(convert(varchar(11),'''+@FILEDATE+''',101),GetDate()), '''+@FILEDATE+''' From WorkTable n (NOLOCK) 'This is what is displayed: Insert DFTable( LoadID, LoadStatus, LoadDate, SysDate) Select n.LoadID, LoadStatus=0, LoadDate= IsNull(convert(varchar(11),'Nov 30 2007',101),GetDate()), 'Nov 30 2007' From WorkTable n (NOLOCK)I have double and triple checked the table formats and they are all at SmallDateTime.Thanks! |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-12-04 : 11:24:45
|
Can you also provide the specific error you get?
Future guru in the making. |
|
|
|
mbskier12
Starting Member
7 Posts |
Posted - 2007-12-04 : 11:28:16
|
| This is the error:The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.The statement has been terminated. |
|
|
|
mbskier12
Starting Member
7 Posts |
Posted - 2007-12-04 : 11:37:22
|
| I think I have a work around. If anyone can explain why I am getting the error though when the DataBase fields are formatted as SmallDateTime, and I create a variable with a SmallDateTime format, why does the format change and give the above error? |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-12-04 : 11:39:29
|
The field is Loaddate right? Are you just intending to insert the a single date value into this field because from the looks of it you have two going into it if you did: SELECT IsNull(convert(varchar(11),'Nov 30 2007',101),GetDate()),'Nov 30 2007' You get two date results.
Future guru in the making. |
|
|
|
mbskier12
Starting Member
7 Posts |
Posted - 2007-12-04 : 13:02:12
|
| No, there is a sysDate that I wanted to put the 'Nov 30 2007' value into, and another with the file date where if it is null, then we put the current date in.I had to strip the query down due to security concerns. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-12-04 : 13:20:36
|
Hmm.. not sure what the problem is then, I just tried doing the same thing and it worked fine, are you positive it isn't failing on one of the other columns you are loading, maybe a type mismatch there?
Future guru in the making. |
|
|
|
|
Date Conversion difficulties