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
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
Microsoft SQL ServerSQL
Last Comment
johnnyg123
What DBMS are you using (SQL Server, Oracle, MySQL, ...)?
ASKER
ms sql server 2012
Give this a try.
»bp
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
»bp
Try:
SELECT convert(datetime, [ordered date], 101)
SELECT convert(datetime, [ordered date], 101)
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
ASKER
Thanks too all that responded
needed is date check as part of it
needed is date check as part of it