String to date conversion in TSql with ISO-CodePudding

I have dates as NVarChar as MM/DD/YYYY format, I want to change it to Date type with 'YYYY-MM-DD' format in TSQl. I am using CONVERT (datetime, [date], 103) but its giving me an error 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value'.

Help is much appreciated

Thank you!

CodePudding user response：

Format 103 is for day/month/year. Use 101 for month/day/year.

See CAST and CONVERT (Transact-SQL). The SET DATEFORMAT command may also be of interest.

SELECT CONVERT(DATETIME, '12/31/2022', 101) -- Succeed
SELECT CONVERT(DATETIME, '31/12/2022', 101) -- Fail

SELECT CONVERT(DATETIME, '12/31/2022', 103) -- Fail
SELECT CONVERT(DATETIME, '31/12/2022', 103) -- Succeed

SET DATEFORMAT mdy
SELECT CONVERT(DATETIME, '12/31/2022') -- Succeed
SELECT CONVERT(DATETIME, '31/12/2022') -- Fail

SET DATEFORMAT dmy
SELECT CONVERT(DATETIME, '12/31/2022') -- Fail
SELECT CONVERT(DATETIME, '31/12/2022') -- Succeed

CodePudding user response：

DECLARE @DT datetime    SET @DT = GETDATE()

SELECT  'Date   - (101) US              mm/dd/yyyy                  '   AS Name, CONVERT(varchar(10), @DT,101)  AS Result   UNION
SELECT  'Date   - (102) ANSI            yyyy.mm.dd                  '   AS Name, CONVERT(varchar(10), @DT,102)  AS Result   UNION
SELECT  'Date   - (103) French          dd/mm/yyyy                  '   AS Name, CONVERT(varchar(10), @DT,103)  AS Result   UNION
SELECT  'Date   - (104) German          dd.mm.yyyy                  '   AS Name, CONVERT(varchar(10), @DT,104)  AS Result   UNION
SELECT  'Date   - (105) Italian         dd-mm-yyyy                  '   AS Name, CONVERT(varchar(10), @DT,105)  AS Result   UNION
SELECT  'Date   - (110) US -            mm-dd-yyyy                  '   AS Name, CONVERT(varchar(10), @DT,110)  AS Result   UNION
SELECT  'Date   - (111) Japan           yyyy/mm/dd                  '   AS Name, CONVERT(varchar(10), @DT,111)  AS Result   UNION
SELECT  'Date   - (112) ISO             yyyymmdd                    '   AS Name, CONVERT(varchar(10), @DT,112)  AS Result   UNION
SELECT  'Date   - (120) ODBC Short      yyyy-mm-dd                  '   AS Name, CONVERT(varchar(10), @DT,120)  AS Result   UNION    
SELECT  'Date   - (106) FR Short MMM    dd MMM yyyy                 '   AS Name, CONVERT(varchar(30), @DT,106)  AS Result   UNION
SELECT  'Date   - (107) US Short MMM    MMM dd, yyyy                '   AS Name, CONVERT(varchar(30), @DT,107)  AS Result   UNION
SELECT  'DandT  - (109) US Long  MMM    MMM dd yyyy hh:mm:ss        '   AS Name, CONVERT(varchar(30), @DT,109)  AS Result   UNION
SELECT  'DandT  - (113) FR Long  MMM    dd MMM yyyy hh:mm:ss        '   AS Name, CONVERT(varchar(30), @DT,113)  AS Result   UNION
SELECT  'DandT  - (121) ODBC No ccc     yyyy-mm-dd hh:mm:ss         '   AS Name, CONVERT(varchar(19), @DT,121)  AS Result   UNION    
SELECT  'DandT  - (121) ODBC Long       yyyy-mm-dd hh:mm:ss.ccc     '   AS Name, CONVERT(varchar(25), @DT,121)  AS Result   UNION    
SELECT  'Time   - (108)                 hhmmss                      '   AS Name, CONVERT(varchar(09), @DT,108)  AS Result   UNION
SELECT  'Time   - (114)                 hh:mm:ss:ccc                '   AS Name, CONVERT(varchar(12), @DT,114)  AS Result