Convert a Date

Question

Post reply

Convert a Date

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
January 25, 2016 at 5:47 am

#310981

Hi Need to export a Date in this format - I've tried a number of things and can't get it into the specific format I require - please advise. Many Thanks
14-SEP-2015 or 31-MAR-2014

Viewing 15 posts - 1 through 15 (of 29 total)

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

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 1

The following -

CONCAT('"',DATEPART(d,ra.StartDate),'-',DATEPART(m,ra.StartDate),'-',DATEPART(year,ra.startdate),'"' ) as test,

Just goes - "11-9-2015" I need it to be "11-SEP-2015"

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Have you tried CONVERT? Use REPLACE as well if you need to change spaces into hypens, and UPPER if the whole month portion needs to be in capitals.

John

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

Yeah tried to convert to varchar and it didn't do anything different.

Dave Morrison SSCrazy Points: 2017 More actions · Answer 4

TSQL Tryer (1/25/2016)
Hi Need to export a Date in this format - I've tried a number of things and can't get it into the specific format I require - please advise. Many Thanks
14-SEP-2015 or 31-MAR-2014

Try something like this

select replace(convert(varchar(50),getdate(),106), ' ', '-')

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

Quick suggestion

😎

DECLARE @TD DATETIME = GETDATE();

SELECT CONCAT(DAY(@TD),CHAR(45),SUBSTRING(UPPER(DATENAME(MONTH,@TD)),1,3),CHAR(45), YEAR(@TD));

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

Another option:

SELECT UPPER( REPLACE( CONVERT(char(11), GETDATE(), 106), ' ', '-'))

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

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 7

Thanks everyone - between you all you got me to where I want to be.

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

SQL Server 2012 supports the FORMAT function:

select format(GETDATE(), 'dd-MMM-YYYY')

-- Gianluca Sartori

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 9

spaghettidba (1/25/2016)
SQL Server 2012 supports the FORMAT function:
select format(GETDATE(), 'dd-MMM-YYYY')

Gianluca, don't use the format function, it has terrible performance.

😎

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
SQL Server 2012 supports the FORMAT function:
select format(GETDATE(), 'dd-MMM-YYYY')
Gianluca, don't use the format function, it has terrible performance.
😎

It is a CLR function, so there's a startup cost and it performs slower than native functions.

It has the advantage of being simple and easy to understand in your code.

If I'm worrying about the performance of a function I use to format my output, it must be something that really runs like hell on the rest of the execution (reading data from tables) or returns a huge amount of rows. Most of the time it is not so.

-- Gianluca Sartori

ben.brugman SSChampion Points: 13369 More actions · Answer 11

Luis Cazares (1/25/2016)
Another option:
SELECT UPPER( REPLACE( CONVERT(char(11), GETDATE(), 106), ' ', '-'))

Be carefull with dates, very often the language or the regional settings are important.

For the solution of Luis (our Italian Plumber) :

set language italian

SELECT UPPER( REPLACE( CONVERT(char(11), GETDATE(), 106), ' ', '-')) , 'Which Month ?'

This would result in :

L'impostazione della lingua è stata sostituita con Italiano.

----------------------------------- -------------

25-GEN-2016 Which Month ?

(1 row(s) affected)

Assuming the the language and regional settings are always the same can lead to problems.

Ben

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 12

spaghettidba (1/25/2016)
Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
SQL Server 2012 supports the FORMAT function:
select format(GETDATE(), 'dd-MMM-YYYY')
Gianluca, don't use the format function, it has terrible performance.
😎
It is a CLR function, so there's a startup cost and it performs slower than native functions.
It has the advantage of being simple and easy to understand in your code.
If I'm worrying about the performance of a function I use to format my output, it must be something that really runs like hell on the rest of the execution (reading data from tables) or returns a huge amount of rows. Most of the time it is not so.

Both I and Jeff Moden have posted examples showing the problem with the format function, 40-50 times slower than alternatives in most cases.

😎

spaghettidba SSC Guru Points: 105732 More actions · Answer 13

Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
SQL Server 2012 supports the FORMAT function:
select format(GETDATE(), 'dd-MMM-YYYY')
Gianluca, don't use the format function, it has terrible performance.
😎
It is a CLR function, so there's a startup cost and it performs slower than native functions.
It has the advantage of being simple and easy to understand in your code.
If I'm worrying about the performance of a function I use to format my output, it must be something that really runs like hell on the rest of the execution (reading data from tables) or returns a huge amount of rows. Most of the time it is not so.
Both I and Jeff Moden have posted examples showing the problem with the format function, 40-50 times slower than alternatives in most cases.
😎

No doubt about that.

My point is: does it really matter when I'm formatting 30 or even 100 dates?

-- Gianluca Sartori

Luis Cazares SSC Guru Points: 183698 More actions · Answer 14

Certainly Ben, language settings can be a problem. However, the problem often relies on keeping the default (us_english) rather than setting a foreign and unexpected language.

The code might need changes with languages that don't use 3 letter short month names like French, Bulgarian, Estonian, Finnish, etc.

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