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 |
nathansi
Starting Member
4 Posts |
Posted - 2013-11-13 : 22:58:09
|
Hi all,I've got a field I'm trying to convert into a date format as it currently sits as a text field. The date setup is of the format DD/MM/YYYY. Some of the fields are NULL, however for the sake of filling in gaps I've just set the nulls to '01-01-1905'.I've tried to use CAST to change the data into a readable format for SQL Server however I tend to get one of two messages:Conversion failed when converting date and/or time from character string.-Or-error converting data type varchar to dateAny ideas on how I can fix this issue? |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-11-13 : 23:06:08
|
[code]convert ( datetime, datecolumn, 103)[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-14 : 02:17:16
|
quote: Originally posted by nathansi Hi all,I've got a field I'm trying to convert into a date format as it currently sits as a text field. The date setup is of the format DD/MM/YYYY. Some of the fields are NULL, however for the sake of filling in gaps I've just set the nulls to '01-01-1905'.I've tried to use CAST to change the data into a readable format for SQL Server however I tend to get one of two messages:Conversion failed when converting date and/or time from character string.-Or-error converting data type varchar to dateAny ideas on how I can fix this issue?
Reason is this. date formats are interpreted on basis of language nad regional settings. So you need to pass an explicit style factor based on your date format and use CONVERThttp://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|