Convert SQL Server Date to DD/MM/YY

Question

Post reply

Convert SQL Server Date to DD/MM/YY

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 9, 2014 at 8:06 am

#288109

I'm trying to convert SQL Server Dates to Char Field in the format of DD/MM/YY.
Sometimes the SQL Server Column had the Time (all zeros) and in other cases it may contain the time.
Any help would be appreciated.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I got it.

,RIGHT('0' + CAST(DATEPART(mm,PURCHASE_DATE) as varchar(2)),2)+ '/' +

RIGHT('0' + CAST(DATEPART(dd,PURCHASE_DATE) as varchar(2)),2)+ '/' +

CAST(DATEPART(yyyy,PURCHASE_DATE) as varchar(4)) AS PURCHASE_DATE

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 2

I came up with something like this:

declare @SSDate datetime = '01/22/2001'

declare @CharSSDate varchar(10)

set @CharSSDate = right('0' + cast(MONTH(@SSDate) as varchar(2)),2) + '/' + right('0' + cast(DAY(@SSDate) as varchar(2)),2) + '/' + RIGHT(CAST(year(@SSDate) as varchar(4)),2)

print @SSDate

print @CharSSDate

Looks much like yours however I used the MONTH, DAY, & YEAR functions.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 3

Your first post indicated you wanted a 2 digit year. Your solution provides you a 4 digit year. Just saying.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Oh my, so much code! My laziness wants to write less.

declare @SSDate datetime = '20140109'

declare @CharSSDate char(10)

SELECT CONVERT( char(10), @SSDate, 103), --4-digit year

STUFF( CONVERT( char(10), @SSDate, 103), 7, 2, '')--2-digit year

You shouldn't store dates as strings. It will just give you headaches and use more space than needed. You should leave format for presentation only.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Luis Cazares (1/9/2014)
Oh my, so much code! My laziness wants to write less.
declare @SSDate datetime = '20140109'
declare @CharSSDate char(10)
SELECT CONVERT( char(10), @SSDate, 103), --4-digit year
STUFF( CONVERT( char(10), @SSDate, 103), 7, 2, '')--2-digit year
You shouldn't store dates as strings. It will just give you headaches and use more space than needed. You should leave format for presentation only.

Actually, don't need the STUFF:

declare @SSDate datetime = '20140109'

declare @CharSSDate char(10)

SELECT CONVERT( char(10), @SSDate, 103), --4-digit year

CONVERT( char(10), @SSDate, 3) --2-digit year

Books Online is your friend:

http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Kurt W. Zimmerman (1/9/2014)
Your first post indicated you wanted a 2 digit year. Your solution provides you a 4 digit year. Just saying.
Kurt

Sorry about the Year.

It first was supposed to be a 2 digit year but evolved to be a 4 digit year.

I forgot to mention this..

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Luis Cazares (1/9/2014)
Oh my, so much code! My laziness wants to write less.
declare @SSDate datetime = '20140109'
declare @CharSSDate char(10)
SELECT CONVERT( char(10), @SSDate, 103), --4-digit year
STUFF( CONVERT( char(10), @SSDate, 103), 7, 2, '')--2-digit year
You shouldn't store dates as strings. It will just give you headaches and use more space than needed. You should leave format for presentation only.

I agree with you that the dates should not be stored as strings.

The Data is coming from one vendor (AS400) with mixed dates and strings.

The destination Vendor (Oracle wants the data in a string format.

Nice code.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Thanks Lynn. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 9

Lynn Pettis (1/9/2014)
Luis Cazares (1/9/2014)
Oh my, so much code! My laziness wants to write less.
declare @SSDate datetime = '20140109'
declare @CharSSDate char(10)
SELECT CONVERT( char(10), @SSDate, 103), --4-digit year
STUFF( CONVERT( char(10), @SSDate, 103), 7, 2, '')--2-digit year
You shouldn't store dates as strings. It will just give you headaches and use more space than needed. You should leave format for presentation only.
Actually, don't need the STUFF:
declare @SSDate datetime = '20140109'
declare @CharSSDate char(10)
SELECT CONVERT( char(10), @SSDate, 103), --4-digit year
CONVERT( char(10), @SSDate, 3) --2-digit year
Books Online is your friend:
http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx

+1000

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cees Cappelle-442904 SSC Eights! Points: 967 More actions · Answer 10

Maybe I'm thinking too simple for this question, but why not use:

select convert(VarChar(32), PURCHASE_DATE, 3)