Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

burakcan

Programmer
Oct 10, 2011
39
CA
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.
 
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
 
Thanks Greg but My date year is 1949 not the 2049 (This is birth date field)
 
Code:
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
 
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 :-(
 
So you’re saying with certainty that you have absoluely no birthdates in your table from 1900 through 1917?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Yes there is no birthdates in your table from 1900 through 1917.
 
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:
Code:
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.
 
'Tis the return of the Y2K bug. Still biting, 17 years later. :D



Just my $.02

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

--Greg
 
Thanks Olaf/Greg/Borislav/Skip.
It works now. :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top