SET DATEFORMAT DMY;

select CONVERT(DATETIME,'13/2/2022',103). it returns 2022-02-13 00:00:00.000.

In a table table_3_2_2022_date_string_format, a column holds string date values as below.

1. nonleadingZeroStringDate
   2/3/2022
   23/12/2022
   13/3/2022

   select CONVERT(DATETIME,nonleadingZeroStringDate,103) from table_3_2_2022_date_string_format.

The above script caught an error 'Conversion failed when converting date and/or time from character string.'

The Microsoft doc says 'The setting of SET DATEFORMAT is set at execute or run time and not at parse time.'. It could explain why it failed?

CodePudding user response：

You're sort of using two different things here. I'm not super familiar with the idiosyncrasies when it comes to dealing with both DATEFORMAT and CONVERT(datetime, [value], ###) at the same time, but I believe DATEFORMAT is really only going to change how date string literals are treated.

By "literals" I mean a date string and you're not giving SQL Server any hints on how to convert it to a date...see below...

SET DATEFORMAT mdy
SELECT ConversionLiteral = CONVERT(datetime, '2/3/2022')        -- 2022-02-03 00:00:00.000
     , ConversionHint    = CONVERT(datetime, '2/3/2022', 103)   -- 2022-03-02 00:00:00.000

SET DATEFORMAT dmy
SELECT ConversionLiteral = CONVERT(datetime, '2/3/2022')        -- 2022-03-02 00:00:00.000
     , ConversionHint    = CONVERT(datetime, '2/3/2022', 103)   -- 2022-03-02 00:00:00.000

When changing the DATEFORMAT and using CONVERT() without specifying a style, it changes when DATEFORMAT changes.

But when you specify a style in the CONVERT(), I believe you're telling SQL Server to ignore what DATEFORMAT is set to, and instead use style 103, which is dd/mm/yyyy.

And this is why no matter what DATEFORMAT is set to in the above sample query, the ConversionHint value never changes.

So, if you are trying to import strings that are all in the format of dd/mm/yyyy, then using CONVERT(datetime, [value], 103) should work great. But it will throw an error if it tries to convert something like 12/25/2022.

My suggestion is this...if you are able to use the TRY_CONVERT() function. Then run something like:

SELECT *
FROM dbo.MyTable
WHERE TRY_CONVERT(datetime, [value], 103) IS NULL
    AND [value] IS NOT NULL

And that will show you which values are failing to convert.

CodePudding user response：

I tried almost every possible way except for adding 0 to make two decimal for day and month. Finally, I had to use the parse() which is not a native SQL function.