How to change date format from YYMMDD to DD-MM-YYYY

Other SQL Server Topics
1

Hi Team,

Can any one help to convert date format YYMMDD to DD-MM-YYYY in a sql script.
In data I have a date like 220504 which I want to convert into 04-05-2022.

I have tried below given way but i am getting error.
convert(varchar(10),cast(a.TRANSACTION_DATE as DATE),103) NEWDATE

Thanks in advance

2

What is the datatype of TRANSACTION_DATE?

How to post a T-SQL question on a public forum | spaghettidba

3

Hi,

Transaction_Date has Number(6) as data type.

Thanks

4

SQL Server does not have a number data type. Exact numbers are int, bigint, smallint, numeric and decimal.

I am going to assume you are using some sort of exact number in SQL Server. You should really have dates as a date type and not convert them to varchar. The date is displayed in the correct format for the current locale.

SELECT CAST(CAST(220504 AS char(6)) AS date)
5

Hi Thanks for your help.
But now for date 220504 I am getting date as 22-05-04 but actual date is 04-05-2022.
Can you pl help to get the correct date format.

6

The query Ifor show does convert the numbers into correct date. If you want to display it in specific format, you should handle it in your front end application where the date is being displayed. Note that there is no specific format for date data type in SQL Server.

1 Like
7

It looks like the problem here is an ambiguous format for the date string/number. Is the number supposed to represent YYDDMM or YYMMDD?

You state you are getting 22-05-04 which would be May 4th, 2022, but then you state it should be 04-05-2022 which can be either May 4th (DD-MM-YYYY) or April 5th (MM-DD-YYYY).

SQL Server is assuming the format for that date number/string is YYMMDD so it is converting it to the date based on that format. If the format is actually YYDDMM then you need to parse out the individual pieces to create the date:

DECLARE @testDate int = 220504;

SELECT DATEFROMPARTS(@testDate / 10000 + 2000, @testDate % 100, @testDate % 10000 / 100)

Just one more reason why storing dates as anything other than one of the defined date data types is a bad idea.

1 Like