0

I have several columns of varchar(max) type that have either date or datetime data. I want to convert them to date type, which I know that I should use CONVERT function. Up to here is no problem!

ISSUE at my hand is that the dates stored in each column are not from similar date format type; not across columns and not even within one column. In other words, date format is not consistent within the column. Some of dates in one column may look like:

DDMMMYYYY

DD/MM/YYYY

MM/DD/YYYY

....

Using convert (without specifying the date style) will end up creating crazy outputs.

I need a script that converts these varchar to dates, preserving the original format.

I know the SET DATEFORMAT function; is there a reverse function that GET DATEFORMAT? Is there a way to detect the date format style?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
MeysaM
  • 21
  • 10
  • 1
    how can you tell if 01/02/2017 means January 2, 2017 or February 1, 2017 given the input date might be DD/MM/YYYY or MM/DD/YYYY? – Alexander Higgins Jun 26 '17 at 16:35
  • 1
    There is no solution with ambiguous date formats. – Dan Guzman Jun 26 '17 at 16:35
  • 1
    Stop storing dates at varchar and you won't have this problem. You're going to continue to have headaches otherwise and will constantly run into unsolvable situations as the one you have here. – S3S Jun 26 '17 at 16:42
  • I understand the ambiguous cases, and I have a work around for that (to compare with other date fields in a row). I do not have control on the data creation process, and this what it is. – MeysaM Jun 26 '17 at 17:12

2 Answers2

0

A SQL Server date data type has no "format". Date is a 4-byte binary structure with the display formatting controlled entirely by the client application that renders the data.

Parsing the unseparated DDMMYYYY value reliably into date is possible because the date components on the string are known. However, the DD/MM/YYYY and MM/DD/YYYY are ambiguous in cases where the day component is less than 13.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • The input is a varchar, hence it is formatted. I understand the ambiguous cases; but I may have it parsed based on the last know (unambiguous) case. – MeysaM Jun 26 '17 at 17:08
  • I don't understand when you mean about parsing ambiguous formats based on the last known unambiguous case. – Dan Guzman Jun 26 '17 at 17:29
  • I have another date column that stores a date which is identical value, but in different format. i can compare the 2 values and make sure they agree – MeysaM Jun 26 '17 at 18:08
  • @MeysaM, that approach should work if you have 2 dates that should have identical values. If you need help with that, add those details to your question and add the tag for the specific SQL Server version you are using. – Dan Guzman Jun 26 '17 at 18:15
0

You can use the TRY_CONVERT() function if you're using SQL Server 2012 or later. You can use the styles defined in the CONVERT docs.

For example, this will convert every value in the table that has a date that is in MM/DD/YYYY format (101) that is not ambiguous that it might be in DD/MM/YYYY format (105):

SELECT TRY_CONVERT(date, VarcharColumn, 101)
FROM UnnamedTable
WHERE TRY_CONVERT(date, VarcharColumn, 101) IS NOT NULL
    AND TRY_CONVERT(date, REPLACE(VarcharColumn,'/','-'), 105) IS NULL

This will convert every value that is in DDMMMYYYY format (106 is closest):

SELECT TRY_CONVERT(date, SUBSTRING(VarcharColumn,1,2) + ' ' + SUBSTRING(VarcharColumn,3,3) + ' ' + SUBSTRING(VarcharColumn,5,4), 106)
FROM UnnamedTable
WHERE TRY_CONVERT(date, SUBSTRING(VarcharColumn,1,2) + ' ' + SUBSTRING(VarcharColumn,3,3) + ' ' + SUBSTRING(VarcharColumn,5,4), 106) IS NOT NULL

However, this:

I need a script that converts these varchar to dates, preserving the original format.

is a nonsensical request. Date and time datatypes are not saved with any display formatting information. They're saved as a date, time, datetime, et al, data type in a binary format. If you leave the value as a datetime, then it's the client that determines how the date is displayed. When you run SELECT getdate() from SSMS, it's SSMS that's determining the display format. Formatting can be done by SQL Server by converting the field to a varchar with the CONVERT() function or the FORMAT() function. Generally, however, it's preferable to format dates in your application like you would with currencies.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66