| Author |
Topic |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2009-01-07 : 08:02:51
|
| Hi am ina bit of a hurry and can't figure out how to convert the date which is a varchar datatime to a datetime datatype.Example of data 01/01/08 I tried to use convert(datetime,col,103) as Date_dte but it did not work.. |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-07 : 08:04:37
|
| convert(datetime,col) should be enough |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-07 : 08:05:21
|
What is the error you got?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-01-07 : 08:06:30
|
| It depends on the input format but something like this would work:DECLARE @date varchar(30)SET @date = '01/01/08 'SELECT CAST(@date AS DATETIME)- Lumbago |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-07 : 08:10:16
|
Unless data '17/12/08' is stored. SQL Server may not be able to determine which dateformat is used.CONVERT(DATETIME, Col1, <fmt> ) should be used to ensure that same conversion is made for all records.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-07 : 08:12:50
|
quote:
Originally posted by Peso
Unless data '17/12/08' is stored. SQL Server may not be able to determine which dateformat is used.CONVERT(DATETIME, Col1, <fmt> ) should be used to ensure that same conversion is made for all records.
E 12°55'05.63"N 56°04'39.26"
Don't we use <fmt> only when converting to varchar ?select convert(datetime,'01/01/08') works fine.select convert(datetime,'01/01/08',103) gives error.Syntax error converting datetime from character string. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-07 : 08:17:47
|
Of course!You are implying that century is used too, by writing 103, but there is no century in the data.select convert(datetime,'01/01/08', 3)select convert(datetime,'01/01/2008', 103)works both ok.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-07 : 08:21:08
|
| followed, thanks. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-07 : 08:31:05
|
Based on that, rookie_sql should use the value 3 for format in his original post.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-01-07 : 09:22:12
|
| Thanks everyone select convert(datetime,'01/01/08',103) work for me now. I also though it might be a space issues so i used the replace function to clean up any spaces. |
|
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2009-01-07 : 09:27:37
|
quote:
Originally posted by rookie_sql
Thanks everyone select convert(datetime,'01/01/08',103) work for me now. I also though it might be a space issues so i used the replace function to clean up any spaces.
how convert(datetime,'01/01/08',103) this works for u ??u can use only select convert(datetime,'01/01/08', 3)select convert(datetime,'01/01/2008', 103)as peso suggested |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-07 : 09:37:07
|
Maybe a typo? Or not using Microsoft SQL Server?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
Convert string to date