| Author |
Topic |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2012-01-10 : 10:11:46
|
| hi there,How can I turn a varchar field like 13.09.2011 (Sept. 13, 2011) into a date type like 2011-09-13? I tried convert(date, field, 101) but got error. Thanks in advance. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-01-10 : 10:16:16
|
Try 104 instead of 101.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-10 : 10:38:07
|
| why are you using varchar to store dates? it will simply cause lot of unwanted casts while doing date manipulations. why not make it datetime if you're sure it stores only dates?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-01-10 : 10:40:51
|
OP never said that he stores dates using varchar.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-10 : 10:46:22
|
quote:
Originally posted by webfred
OP never said that he stores dates using varchar.
No, you're never too old to Yak'n'Roll if you're too young to die.
orginal questionHow can I turn a varchar field like 13.09.2011 (Sept. 13, 2011) into a date type like 2011-09-13?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-01-10 : 10:50:59
|
thx but I've read the question.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-10 : 10:53:09
|
quote:
Originally posted by webfred
thx but I've read the question.
No, you're never too old to Yak'n'Roll if you're too young to die.
its clearly stated that op is storing date as varchar values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-01-10 : 10:58:17
|
Nope it's not sooooooooooooo clear.Maybe it is an import from a file where the date value is stored like this.anyway we should stop this ping pong now  my postcounter is good enough 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2012-01-10 : 11:38:00
|
| thanks gentlemen. the reason it uses varchar is it is imported from a SAP database. 104 works, i just wonder why 101 not working. Is it because that 104 is German style for dd.mm.yyyy which match the varchar type? Does this mean expression style of century should match the format before conversion not after? I tried 103 it works but 102 not. Thanks guys. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-10 : 11:45:40
|
quote:
Originally posted by allan8964
thanks gentlemen. the reason it uses varchar is it is imported from a SAP database. 104 works, i just wonder why 101 not working. Is it because that 104 is German style for dd.mm.yyyy which match the varchar type? Does this mean expression style of century should match the format before conversion not after? I tried 103 it works but 102 not. Thanks guys.
based on your regional and language settings as well as date format settings it will try to interpret your date values. thats its always recommended to use one of the unambigous formats while passing date values like one given belowhttp://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-10 : 11:48:45
|
quote:
Originally posted by webfred
Nope it's not sooooooooooooo clear.Maybe it is an import from a file where the date value is stored like this.anyway we should stop this ping pong now my postcounter is good enough
No, you're never too old to Yak'n'Roll if you're too young to die.
Actually I dont really have to increase my post count like this as I post quite a lot answering forum questions itself My point was just that whatever be source if at all possible better to use proper datatype to avoid unnecessary complications later during manipulations------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
convert string to date