| Author |
Topic |
|
bh0526
Yak Posting Veteran
71 Posts |
 Posted - 2011-06-03 : 07:28:08
|
| I have a column defined as varchar(16) though it contains a date and time value. How do I convert this to datetime?Thanks,Bob |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-03 : 07:39:09
|
| convert(datetime,charfld)have a look at the convert function as you might need a style and/or to reformat the string.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
bh0526
Yak Posting Veteran
71 Posts |
Posted - 2011-06-03 : 08:28:43
|
| Nigel,That didn't work. I used this: Convert(datetime, myDate) as myNewDate I get an error reading: Conversion failed when converting datetime from character field.Thanks,Bob |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-03 : 08:34:44
|
| Read the second line of my previous post.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
bh0526
Yak Posting Veteran
71 Posts |
Posted - 2011-06-03 : 08:36:21
|
| I did read this. But I tried several of the numbers available like 101. They all failed. I have no idea which one to use.Bob |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-06-03 : 08:43:51
|
| have you any rubbish (non-date) data in there? |
|
|
|
bh0526
Yak Posting Veteran
71 Posts |
Posted - 2011-06-03 : 08:48:06
|
| Andrew,Yeah, I think that's the problem. The column is defined as varchar(16) and the time is being cut off. I inherited this database and I would like to convert this column to datetime like it should be. But I don't have time right now. But I will change it to varchar(19) and see what happens.Bob |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-03 : 08:55:13
|
| >> and the time is being cut off>> you might need a style and/or to reformat the stringDon't see that I can say any more unless you post some data.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
bh0526
Yak Posting Veteran
71 Posts |
Posted - 2011-06-03 : 09:11:41
|
| I fixed the actual data in the varchar column. I had to put in the leading zeros for month and day if they were single digits. I used:CONVERTdatetime, myDate, 101) as myNewDateThis works except when I have a blank value in myDate. This now appears on my report as "01/01/1900 00:00" which is the SQL default value I think. How can I suppress this when mydate is blank?Bob |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-03 : 09:23:55
|
| What value do you want when it is blank?That datetime is just converting to 0. It is held as the number of days from 19000101 so is the conversion of an empty string to a datetime.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
bh0526
Yak Posting Veteran
71 Posts |
Posted - 2011-06-03 : 09:33:36
|
| Nigel,I'm using this date in a report built with Reporting Services. I want the date to be blank if no date exist. I found a solution. I convert the date like you guys said. Then in my report, I use the IIF function to see if the date contains "01/01/1900 00:00". If so, I replace with blanks. So it all works now. Thanks for all your help!Bob |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-03 : 09:40:18
|
| could also return null using a case statement.The main thing is to define what you want then you will usually find the implementation easy. If you don't know what yoiu want or what is there it makes things a lot more tricky.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
How to convert a string column to datetime