asked on

sql date conversion

I have a table that we get from a vendor that has 2 columns

Here is a sample of the data

SDNUID      BirthDate
2674      10 Dec 1948
2683      1938
2687      May 1937
2607      Null

The Birthdate field is defined as varchar(50)

The field contains 'date' in one of 3 formats (date could have a null value)

dd mmm yyyy

yyyy

mmm yyyy

What I would like to do is write a query that will return a valid date format by doing the following

if date value is in dd mmm yyyy format then return dd-mmm-yyyy

if date value is in yyyy format then return 01-01-yyyy

if date value is in mmm yyyy format then return 01-mmm-yyyy

if date value is null then return 01-Jan-1900

so using above data example

SDNUID      BirthDate
2674      10-Dec-1948
2683      01-Jan-1938
2687      01-May-1937
2607      01-Jan-1900

I thought of using case statement based on whether length of birthdate field is 11, 4, 8 or 0 but that didn't seem quite right

Russell Fox

The built-in CAST function will return the dates you want without mucking around with string parsing. You just need to work with the NULL value. That will leave you with a valid DATE field that you can format however you wish (see CONVERT(NVARCHAR...)):

SELECT SNUID, CAST(COALESCE(BDate, '1/1/1900') AS DATE) FROM YourTable

Open in new window

Kyle Abrahams, PMP

select SDNUID, replace(convert(varchar, cast(isnull(BirthDate, '1/1/1900) as datetime), 106), ' ', '-')
from yourTable

Michael Carrillo

First, are you wanting to correct the data itself (update statements) or just query the data (select statements)?

If you are correcting the data, you can run a series of updates to address each of the anomolies.

For example:

Update TableName
Set BirthDate= 01-Jan-1900
Where BirthDate=NULL

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnnyg123

ASKER

Thanks for all the posts!!!!

You wouldn't believe it (or maybe you would :-)) ....the requirement has changed slightly

Now I need to only capture valid dates and ignore any others

How would I query to return only those dates that are valid?

Using the sample data above, the query results would just be

2674 10 Dec 1948

Thanks!

Scott Pletcher

That's the other advantage of YYYYMMDD -- can be easily verified :-) .

Use ISDATE() on the value: if you get a 1 result, you can safely convert to a date/datetime.

johnnyg123

ASKER

Thanks Scott..you pointed me in right direction

I was trying the isdate() of 1 in the query and was getting all rows because sql server thought that all 'dates' were valid

what I really wanted was only dates that were in ddmmmyyyy format so I used following in where clause

replace((BirthDate),' ', '-') = replace(convert(varchar, cast(isnull(BirthDate, '1/1/1900') as datetime), 106), ' ', '-')

johnnyg123

ASKER

Thanks! Worked out great!

Scott Pletcher

If ISDATE(<string>) returns 1, then CAST(<string> AS date|datetime) should always work correctly. The only problem would be if you tried to do the conversion yourself, instead of letting SQL do it for you.