1

I receive data from a client in a text format and then I have to convert it into date for MS SQL Server 2016 and Postgresql 12.

The dates can be in these formats:

30.12.2019 or 12.30.2019 or

30/12/2019 or 12/30/2019

Luckily, the same formatting applies for the whole field(column), however my query needs to determine which one of these it is.

I would like to apply a conditional clause (as CASE WHEN/IF) both in TSQL and POSTGRESQL. This is what I have so far:

TSQL:

SET DATEFORMAT mdy;   UPDATE mytable SET
finaldate =CAST(TRY_CONVERT(varchar(255),RIGHT(mydate,4)+LEFT(mydate,2)+SUBSTRING(mydate,4,2))
AS DATE) WHERE mydate <> '00/00/0000' AND LEFT(mydate,2) < 13 AND
SUBSTRING(mydate,3,1) = '/';

SET DATEFORMAT dmy;  
UPDATE mytable SET finaldate=CAST(TRY_CONVERT(varchar(255),RIGHT(mydate,4)+SUBSTRING(mydate,4,2)+LEFT(mydate,2)) AS DATE)
WHERE mydate <> '00.00.0000' AND SUBSTRING(mydate,4,2) < 13 AND SUBSTRING(mydate,3,1) = '.';

POSTGRESQL:

UPDATE my_table SET finaldate = TO_DATE(mydate, 'DD/MM/YYYY')
WHERE mydate <> '00/00/0000' AND CAST(LEFT(mydate,2) AS INTEGER) < 13 AND SUBSTRING(mydate,3,1) = '/';


UPDATE my_table SET finaldate = TO_DATE(mydate, 'DD.MM.YYYY')
WHERE mydate <> '00.00.0000' AND CAST(SUBSTRING(mydate,4,2) AS INTEGER) < 13 AND SUBSTRING(mydate,3,1) = '.';

However this cover only the scenarios for

30.12.2019 and 12/30/2019.

How can I use the condition if one of the numbers in the particular position is more than 13 then all the dates will be formatted.

Thank you!

Kokokoko
  • 452
  • 1
  • 8
  • 19
  • 5
    If the same column contains 30.12.2019 and 12.30.2019 then you will need a crystal ball to determine if 11.10.2019 is October 11 or November 10. – Salman A Nov 13 '19 at 10:17
  • 1
    So are you saying that you want to scan the whole table to see where one of the values that is either a date or month is > 12, and if found, then you know which is the day and month not only for that field but for the whole table? And then you just need to update the `finaldate` field with a date based on the `mydate` field, where you now know the dd and mm position, but could be delimited by `.` or `/`? – 404 Nov 13 '19 at 10:25
  • that is the thing - it cannot. it can be either 30.12.2019 or 12.30.2019.... therefore if the condition of <13 is met at least in one field then I know the whole column is 30.12.2019 or 12.30.2019. – Kokokoko Nov 13 '19 at 10:26
  • Sorry I don't get the < 13 thing. Both day and month can be < 13, so what information do you gain by knowing that one of them is less than 13? Surely you want to be checking if one is greater than 12, because only one can be. – 404 Nov 13 '19 at 10:30
  • 1
    Something like `SELECT MAX(SUBSTRING(x, 1, 2)::int) AS max1, MAX(SUBSTRING(x, 4, 2)::int) AS max2` followed by `CASE WHEN max1 > 12 THEN /* parse as day month year */ WHEN ...`? – Salman A Nov 13 '19 at 10:30
  • At least in SQL Server, date does not have a display format. I'm pretty sure that's also true with PostgreSql, – Zohar Peled Nov 13 '19 at 12:01
  • The problem of formats month-day-year and day-month-year is that it is ambiguous every month then the day is less than 13. That's about 43%, so not much of an unusual exception. As @Eric points out it's the client you need to talk with. IMHO you need to be willing to change as well, I would push for the ISO_8601 standard. Alternately, create a client_options table of which date format is an entry, Then just identify the client, which I'm sure must already done. – Belayer Nov 13 '19 at 21:44

1 Answers1

1

Too long for a comment.

Anything you put in place here is likely to fail on you eventually. Say the client has a slow month, and all the transactions they send are from the first through the eleventh. You'll have no way of discerning the intended date format.

But that aside, there is a solution to this. The client knows what the export specifications are for each file that they send you. I've been the importer or the exporter of probably hundreds of flat files over the years, and the first thing to be done is to get in touch with the technical resource on the other end of the transaction and establish the ground rules, including date formats.

Chances are that there is some other distinguishing characteristic of each one of these files that will tell you which export specification it came from so that you can accept it accordingly. On the off chance that the client is building these things ad hoc, and changing things up between runs, then it's in their best interest as well as yours to formalize the protocol so that they don't have to think so hard about it every time.

But the TL;DR on this is that you won't find your best answer on Stack Overflow, you'll find it in your client's IT department.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35