Link to home
Create AccountLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

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
Avatar of Bill Prew
Bill Prew

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

ASKER

ms sql server 2012
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
Try:

SELECT convert(datetime, [ordered  date], 101)
SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

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