convert string to date DD.MM.YYYY

[Baron]

Is it possible to convert a string with an 'invalid' date format into a date?

I tried the following without success:

select convert(date, '15.06.2021');

select date('15.06.2021');

select cast ('15.06.2021' as date);

[VolkerBarth · Accepted solution]

You can (temporarily) adapt the date_order setting. Note, you might as well need to adjust the date_format/timestamp_format options.

For temporary import of say, German-based date values, our procedures often contain code like:

set temporary option date_order = DMY;
select cast ('15.06.2021' as date);
set temporary option date_order = ;

[Baron · Accepted solution]

Thanks

The problem is that I need to convert the string with format DD.MM.YYYY and then write it in a column with a type of DATE.

The only way I could do it in this way:

BEGIN

declare @birthday date;

declare @birthdaychar varchar(40);

set @birthdaychar = '15.06.2021';

set temporary option date_order = DMY;

set @birthday = (select dateformat(@birthdaychar, 'YYYY-MM-DD'));

update employee set birthday = @birthday ;

set temporary option date_order = ;

end;

Do you think that I could do it more elegant? Does this temporary option affect only the current connection or the whole database?

[VolkerBarth · Accepted solution]

Does this temporary option affect only the current connection or the whole database?

Yes, note the docs:

When the SET TEMPORARY OPTION statement is not qualified with a user ID, the new option value is in effect only for the current connection.

As stated in the answer, you might as well adapt the date_format and timestamp_format options temporarily, so you can convert according strings into date data types without the need do use the dateformat function. That might be "more elegant" particularly when you have to treat several different date values.

And of course you do not need a separate variable @birthday within the UPDATE statement, you can simply code "update employee set birthday = dateformat(...)"

[Baron · Accepted solution]

Interesting is that after changing the option to DMY then all formats will be accepted.

set temporary option date_order = DMY;

select dateformat('2020-12-31', 'DD.MM.YYYY'), dateformat('2020.12.31', 'DD.MM.YYYY'), dateformat('31.12.2020', 'DD.MM.YYYY'), dateformat('31-12-2020', 'DD.MM.YYYY');

set temporary option date_order = ;

[VolkerBarth · Accepted solution]

That's possibly a misconception, AFAIK you can ALWAYS use any format with DATEFORMAT because that deals with converting date values to strings, and apparently you tell the function what format to use, so there's no ambiguity here.

In contrast, the mentioned options (and your original question) decide how strings are converted to date values, and therefore date order and expected format are relevant.

Note: The "date_format" option is different from the DATEFORMAT function. 🙂

[Baron · Accepted solution]

But without line 1) below I will get an error, if I execute only line 2), which means that I can't use any format with the function dateformat. It expects a datetime-expression as the first parameter, and '31.12.2020' can't be converted to datetime (unless we change the date order)

1- set temporary option date_order = DMY;

2- select dateformat('2020-12-31', 'DD.MM.YYYY'), dateformat('2020.12.31', 'DD.MM.YYYY'), dateformat('31.12.2020', 'DD.MM.YYYY'), dateformat('31-12-2020', 'DD.MM.YYYY');

3- set temporary option date_order = ;

[VolkerBarth · Accepted solution]

Ah, sorry, I misread your sample. Your usage of the DATEFORMAT function is somewhat unusual: It expects a date/datetime as first parameter, whereas you use it with a string that has to be automatically converted to a date beforehand.

So your sample function calls basically

1. convert from string to date values (which requires fitting date order, otherwise it will raise a conversion error, as mentioned in my answer)
2. and then back to the desired string format, which is independent of date order. My previous comment was simply dealing with that step.

In other words:

select dateformat(current date, 'yyyy-mm-dd'), dateformat(current date, 'dd.mm.yyyy'), dateformat(current date, 'mm/dd/yyyy')

should work independen to the mentioned options, as they deal with converting string to date values whereas the DATEFORMAT() function does the opposite.

[Baron · Accepted solution]

My problem is as follows: I want to import into EMPLOYEE table from a CSV file, the table has a field called BIRTHDAY of type DATE. Birthday exists in the CSV sometimes as DD.MM.YYYY and sometimes as YYYY.MM.DD.

For this reason, I create a temporary table which has the same structure as EMPLOYEE except that the field BIRTHDAY is of type VARCHAR(40), and I use the UNLOAD statement to import from the CSV into my temp. table (here unload will always succeed regardless of what format is the BIRTHDAY).

Later I need to move the contents of the temp table into my EMPLOYEE table, and so that I need to convert the BIRTHDAY from string into DATE (so I need to convert it first to type DATE and then fix the format), and for that I need the block code above.

Actually your hint with option date_order works fine and did exactly what I want.

Just wanted to explain the reason for the unusual usage of DATEFORMAT

[VolkerBarth · Accepted solution]

The approach to import external data with varying or unfitting format into a local temporary table with a VARCHAR data type instead of a number or date data type and then to insert data from the temporary table into the permantent table with according data conversion is certainly very common. We do use that as well, say when importing numerical values formatted with comma as decimal separator or with currency symbols. For such cases, we would then typically use both a "source-value-as-is" VARCHAR column and a "target-datatype" column in the temporary table.

Assuming you would have both a "BirthdayString" VARCHAR column and a "BirthdayDate" DATE column within your TempEmployee table.

If there are just those two date formats (dd.mm.yyyy and yyyy.mm.dd), you might as well use one single update statement with the YMD function and without the need to adapt the date_order, such as

update TempEmployee
set BirthdayDate =
   if charindex('.', BirthdayString) = 3
   then -- 'dd.mm.yyyy'
      ymd(substring(BirthdayString, 7, 4),
          substring(BirthdayString, 4, 2),
          substring(BirthdayString, 1, 2))
   else -- 'yyyy.mm.dd'
      ymd(substring(BirthdayString, 1, 4),
          substring(BirthdayString, 6, 2),
          substring(BirthdayString, 9, 2))
   end if
where BirthdayDate is null;

Not necessarily better, just another approach.

[Baron · Accepted solution]

Thanks @Volker Barth, Actually I am expecting the BIRTHDAY in the CSV in many other formats like dd\\mm\\yyyy, dd/mm/yyyy, dd_mm_yyyy, yyyy\\mm\\dd, yyyy/mm/dd, yyyy_mm_dd...

With the block above I can read all possible formats, but good to know about the function YMD!

[Baron · Accepted solution]

But why you prefer to have an extra column in the temp table and an extra step for correcting the format before transferring from temp to EMPLOYEE?

What is the drawback with my approach? (to make the conversion/correction during transferring from temp to EMPLOYEE)?

[VolkerBarth · Accepted solution]

Well, we usually have to insert or update rows from external data into permanent tables, so it feels easier to correct/normalize the external data within the temp tables before updating permanent tables - so in case the conversion fails those errors happen before permanent tables are touched at all. Just a preference, there's nothing wrong with your approach AFAICT...

[VolkerBarth · Accepted solution]

Well, if delimiters are varying, instead of CHARINDEX() you could also search for any delimiter using "if PATINDEX('[^0-9]', BirthdayString)..." or regexes. (Of course this would fail if month and day parts may be re-arranged, too.)