Reputation: 1702

SQL Convert dd/mm/yy to yymmdd

I have been trying to achieve this all day, I have followed numerous tutorials and can't seem to crack it, I have been trying things like:

select CONVERT(VARCHAR(10), DATE, 131) from Table

yet it does not seem to change anything.

Any advice or help would be appreciated. Thankyou in advance.

Upvotes: 4

Answers (6)

Dhaval Asodariya

Reputation: 558

Try this

declare @TDATE Date = '2015-11-10';
select Convert(varchar,Datepart(Year, @TDATE))+Convert(varchar,Datepart(Month, @TDATE))+Convert(varchar,Datepart(Day, @TDATE))

Output:

20151110

Upvotes: 2

user1017882

Reputation:

SELECT CONVERT(VARCHAR(10), DATE, 12)

12 is the right code for the format you want. See: https://msdn.microsoft.com/en-GB/library/ms187928.aspx

Upvotes: 11

Muhammad Sohail Arif

Reputation: 158

select date_format(column_name, '%Y%m%d') from table_name;

Upvotes: 0

Gordon Linoff

Reputation: 1269623

One method is to construct the value from date parts. Here is a numeric conversion:

select (year(date) % 100) * 10000) + month(date) * 100 + day(date)

It is easy to convert this to a number:

select cast( (year(date) % 100) * 10000) + month(date) * 100 + day(date) as varchar(10))

The slight advantage to this approach over using convert is that a human being can understand the logic without perusing arcane documentation, specific to SQL Server. I don't know why SQL Server doesn't support a simple format-type function similar to most other databases (and programming languages).

Upvotes: 0