CODEWITHSUNDEEP
sql-serverdate
Umesh D
Umesh D

Reputation: 263

Convert DDMMYYYY to YYYYMMDD date format in sql server?

I have a date in table as "26052016" in format DDMMYYYY I want to convert this date to "YYYYMMDD" format.

Any idea
I have tried this method

select CONVERT(varchar(8),[doc-date],112) FROM C034_PDK_ParallelBillingSourceExtract

But this is gives me the same date as a result.

Please help me

Upvotes: 2

Views: 12427

Answers (6)

Geoff Griswald
Geoff Griswald

Reputation: 1093

Since SQL Server 2016 we have a couple of handy tools for this:

Use DATEFROMPARTS and SUBSTRING to convert odd date formats from any arrangement within a Varchar to an actual date:

SELECT DATEFROMPARTS(SUBSTRING('31122015',5,4), SUBSTRING('31122015',3,2), SUBSTRING('31122015',1,2))

Use FORMAT to Convert an actual date to YYYYMMDD:

SELECT FORMAT(MyDate, 'yyyyMMdd')

watch out for the yyyyMMdd, that's the only part of MS SQL that is case-sensitive. Lower case mm is "minutes" and upper case MM is "Month", upper case YYYY or DD is nothing, and will just add letters to your output!

Upvotes: 0

Pradyuman Singh
Pradyuman Singh

Reputation: 34

There are differet ways to do it.

The best way is to use substring method as you know the character positions are going to remain same.

For Example

Suppose your date is - 31122015

Pick the portions of date using substring method and concatenate them 

select  SUBSTRING('31122015',5,4) + SUBSTRING('31122015',3,2) + SUBSTRING('31122015',1,2)

The result would be - 20153112

Upvotes: 1

StackUser
StackUser

Reputation: 5398

Try like this,

SELECT substring([doc-date], 5, 4) + substring([doc-date], 3, 2) + substring([doc-date], 1, 2) AS [YYYYMMDD]
FROM C034_PDK_ParallelBillingSourceExtract

Upvotes: 1

A_Sk
A_Sk

Reputation: 4630

I can find this way, i don't know if any other way exist or not..

declare @date nvarchar(max)='01052016'

select convert(varchar(8),cast(CONCAT(SUBSTRING(@date,3,2),'/',SUBSTRING(@date,1,2),'/',SUBSTRING(@date,5,4)) as date),112)as [YYYYMMDD]

Clear Code:

declare @date nvarchar(max)='01052016'
declare @date1 date
set @date1 =cast(CONCAT(SUBSTRING(@date,3,2),'/',SUBSTRING(@date,1,2),'/',SUBSTRING(@date,5,4)) as date)

select convert(varchar(8),@date1,112)as [YYYYMMDD]

If you are using Sql version< 2012 then you need to skip CONCAT and use + for string concatination.

Upvotes: 1

Ashu
Ashu

Reputation: 482

SELECT CONVERT(VARCHAR(8), '26052016', 112) AS [YYYYMMDD] from
 C034_PDK_ParallelBillingSourceExtract

Upvotes: 1

dhS
dhS

Reputation: 3694

 SELECT CONVERT(VARCHAR(8), doc-date, 112) AS [YYYYMMDD] from
 C034_PDK_ParallelBillingSourceExtract

Check ... this should work correctly.

Thanks

Upvotes: 1

Related Questions