How to convert string DD-MMM-YY date to Date format in SQL. 1

[burakcan]

Hello,
How to convert string DD-MMM-YY date to YYYY-MM-DD Date format in SQL?
Sample
'17-Jun-49' --> 1949-06-17
Thanks for the help.

 

[gbaughma]

Well, SELECT CONVERT(DATE,'17-Jun-49') returns
2049-06-17

... you would need to adjust accordingly.


Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[burakcan]

Thanks Greg but My date year is 1949 not the 2049 (This is birth date field)

 

[bborissov]

SELECT CONVERT(DATE,LEFT('17-Jun-49',7)+'19'+RIGHT('17-Jun-49',2))

but this will bring some more questions?
what is the border where you should start to add 2000 ->17 or 16 or...?


Borislav Borissov
VFP9 SP2, SQL Server

 

[burakcan]

Thanks Borislav
As I mentioned this is the birth date field. If we get someone birtdate in after 2000. Date will be something like 15-Jan-01.
then this approach wont work :-(

 

[SkipVought]

So you’re saying with certainty that you have absoluely no birthdates in your table from 1900 through 1917?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[burakcan]

Yes there is no birthdates in your table from 1900 through 1917.

 

[Olaf Doschke]

You'll have to decide for a turnover year, the simplest rule is to go down 100 years if the date is in the future. But whatever birthdays are in your data, if they are from employees, none of them should be below age 16, perhaps, so you can also be quite sure 12 would mean 1912 instead of 2012. Then also, anyone beyond age 65 would not fit in with such a birth date today. So you may also use that to find invalid data not fitting the expected age range.

Find some cut offs like that and then you have your conversion.

In the simplest case any converted date must be a past date:

SELECT field, CASE WHEN CONVERT(DATE,field)>getdate() THEN DateAdd(year,-100,CONVERT(DATE,field)) ELSE CONVERT(DATE,field) END as theDate FROM yourtable

Bye, Olaf.

 

[gbaughma]

'Tis the return of the Y2K bug. Still biting, 17 years later.



Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[burakcan]

Thanks Olaf/Greg/Borislav/Skip.
It works now.