Convert custom format string to Date

Question

I'm receiving date values(as string) from one of the source system. It is always a 8 char long as shown below. I'm really not sure why they have kept in this format.

15/06-10
07/03-03
28/10-04
10/07-90
05/07-55

But for my application, I need to convert this into a proper date format(i.e. DD-MON-YYYY).

First 2 characters represent Date
Next 2 characters(after /) represent Month
last 2 represent year

In oracle, I can use to_date to achieve this. Something like

select to_date('15/06-10','yy/mm-dd') from dual;

But in SQL Server, I couldn't find such function. Is there a way to achieve this? The closest I have got is

DECLARE @d VARCHAR(100) = '15/06-10';
SELECT DATEFROMPARTS('20'+SUBSTRING(@d,7,2),SUBSTRING(@d,4,2),SUBSTRING(@d,1,2))

I'm not sure if this is the right way. Also It is giving the results only when I hardcode 20 for the year. But i'm getting data from 1950. So I cannot hardcode 19 or 20 Expected output for above sample is

+----------+-------------+
| 15/06-10 | 15-JUN-2010 |
+----------+-------------+
| 07/03-03 | 07-MAR-2003 |
+----------+-------------+
| 28/10-04 | 28-OCT-2004 |
+----------+-------------+  
| 10/07-90 | 10-JUL-1990 |
+----------+-------------+
| 05/07-55 | 05-JUL-1955 |
+----------+-------------+

Y2K strikes again! No one should be using 2 digit years. Fixing your data during extract is the best solution - and using a proper format for dates is far better than this bizarre one. But surely your response is that such is not possible. So the short answer is - anything that works is "right way". Adapt Gordon's logic to use 2 digit years with the appropriate style string - which you can find in the documentation. — SMor
– SMor, Commented Dec 10, 2020 at 13:50
Looks very strange format. How will you know whether it is 19 or 20? Suppose if the data is 10/07-19, then do you expect 10-JUL-2019 or 10-JUL-1919? That is the reason you should always use 4 digit format. — Arun Palanisamy
– Arun Palanisamy, Commented Dec 11, 2020 at 6:28
I agree that this format is a big problem. We are taking action to correct it. But for now, I need to fix the current data and i'm getting only dates from 1950. so from 50 to 99 i want to 19 and 01 to 20 i want 20. For this 10/07-19 , i need 10-JUL-2019 @ArunPalanisamy — Avinash
– Avinash, Commented Dec 14, 2020 at 10:43

Gordon Linoff · Accepted Answer · 2020-12-10 11:55:04Z

1

SQL Server does not have a to_date() function. But here is a simpler way to do the conversion you want:

select convert(date, replace('20' + str, '/', '-'))
from (values ('15/06-10'), ('07/03-03'), ('28/10-04')) v(str)

The expression in the select actually changes the format to YYYY-MM-DD, which is trivially converted to a date. However, SQL Server convert()/cast() is relatively smart about recognizing dates in strings.

answered Dec 10, 2020 at 11:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Avinash Over a year ago

Thanks Gordon. But this is giving me wrong output. For eg., 28/10-04 it is giving 2028-10-04 but i want 28-OCT-2004. Also I cannot hardcode 20 in the code. Becoz it may be 19 also as i'm getting date from 1950 onwards. I have added sample for that. Sorry that I didn't convey this clearly before

Gordon Linoff Over a year ago

@Avinash . . . This returns a date. If you want a specific format, then you need to convert the date to a string. For the record, the code in your question also returns a date. You can use format() or convert() to create a string with a date in a particular format.

GMB · Accepted Answer · 2020-12-10 11:57:00Z

0

One option uses datefromparts() and string functions:

select datefromparts(
    '20' +  left(@d, 2), 
    substring(@d, 4, 2),
    right(@d, 2)
) as dt

answered Dec 10, 2020 at 11:57

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

Avinash Over a year ago

Thanks @GMB. But I cannot hardcode 20. Becoz it may be 19 also as i'm getting date from 1950 onwards. Sorry that I didn't convey this clearly before

Collectives™ on Stack Overflow

Convert custom format string to Date

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related