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);

asked 15 Jun, 05:18

Baron's gravatar image

Baron
1.4k78102126
accept rate: 47%


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 = ;

permanent link

answered 15 Jun, 05:26

Volker%20Barth's gravatar image

Volker Barth
37.6k345513778
accept rate: 34%

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?

(15 Jun, 05:53) Baron
Replies hidden
1

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(...)"

(15 Jun, 06:33) Volker Barth

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 = ;

(15 Jun, 08:21) Baron
Replies hidden

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. :)

(15 Jun, 09:46) Volker Barth

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 = ;

(15 Jun, 10:07) Baron
Replies hidden
1

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.

(15 Jun, 10:17) Volker Barth
1

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

(17 Jun, 07:20) Baron
Replies hidden
1

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.

(18 Jun, 02:54) Volker Barth

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!

(18 Jun, 03:39) Baron
Replies hidden

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)?

(18 Jun, 03:44) Baron
1

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...

(18 Jun, 03:51) Volker Barth

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.)

(18 Jun, 03:56) Volker Barth
More comments hidden
showing 5 of 12 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×20
×7
×4

question asked: 15 Jun, 05:18

question was seen: 127 times

last updated: 18 Jun, 03:56