asked on

format different sql dates into mm/dd/yyyy format

I have an sql server 2012 order table that has dates in various formats stored in a varchar field

here is a sample

order id
1 Null
2 12-01-2017
3 01/01/93
4 01/03/16
5 12/01/1999
6 2-2-07

trying to write a query that would "force" dates into mm/dd/yyyy format

(no year is before1990)

if the date is null would like it formatted as 01/01/1900

given the sample I would like to see

ordered date
1 01/01/1900
2 12/01/2017
3 01/01/1993
4 01/03/2016
5 12/01/1999
6 02/02/2007

Bill Prew

What DBMS are you using (SQL Server, Oracle, MySQL, ...)?

johnnyg123

ASKER

ms sql server 2012

Bill Prew

Give this a try.

SELECT [Line], CONVERT(date, IsNull([order id], '1-1-1900')) AS [order id] FROM
(SELECT 1 AS [Line], NULL AS [order id]
UNION
SELECT 2 AS [Line], '12-01-2017' AS [order id]
UNION
SELECT 3 AS [Line], '01/01/93' AS [order id]
UNION
SELECT 4 AS [Line], '01/03/16' AS [order id]
UNION
SELECT 5 AS [Line], '12/01/1999' AS [order id]
UNION
SELECT 6 AS [Line], '2-2-07' AS [order id]) As t1

Open in new window

»bp

Garfield Samuels

Try:

SELECT convert(datetime, [ordered date], 101)

SOLUTION

Mark Wills

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

ASKER CERTIFIED SOLUTION

Dean Chafee

membership

This solution is only available to members.

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

Start Free Trial

NerdsOfTech

TRY_CONVERT is a safe approach to the date conversion as on error (from say a malformed or invalid date) it returns null; this null and a natural null can then default to 01/01/1900.

 SELECT 
 `order_id`,
 IsNull(TRY_CONVERT(DATE, `order_date`, 101), CONVERT(DATE,'01/01/1900')) AS `ordered_date`
 FROM orders

Open in new window

johnnyg123

ASKER

Thanks too all that responded
needed is date check as part of it