DateTime format to mmmdd format

Question

Post reply

DateTime format to mmmdd format

Cory Slaney

SSCrazy

Points: 2459
More actions
April 21, 2005 at 5:00 pm

#66198

I would like to be able to convert SQL server DateTime format to the format mmmdd.
eg. Feb25 What would be the best way to do this?

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Try this:

SELECT SUBSTRING( DATENAME(month, GETDATE()), 1, 3) + CONVERT( varchar(4), DATEPART( day, GETDATE()))

I wasn't born stupid - I had to study.

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

If you need a leading zero, try this:

SELECT CASE

WHEN LEN( DATEPART( day, '01/01/2005')) = 1

THEN SUBSTRING( DATENAME(month, '01/01/2005'), 1, 3) + '0' + DATENAME( day, '01/01/2005')

ELSE SUBSTRING( DATENAME(month, '01/01/2005'), 1, 3) + DATENAME( day, '01/01/2005')

END

SELECT CASE

WHEN LEN( DATEPART( day, GETDATE())) = 1

THEN SUBSTRING( DATENAME(month, GETDATE()), 1, 3) + '0' + DATENAME( day, GETDATE())

ELSE SUBSTRING( DATENAME(month, GETDATE()), 1, 3) + DATENAME( day, GETDATE())

END

I wasn't born stupid - I had to study.

Rob Farley SSCrazy Points: 2738 More actions · Answer 3

Wow... when I need to pad in SQL, I just use the 'right' function.

SUBSTRING( DATENAME(month, '01/01/2005'), 1, 3) + right('0' + DATENAME( day, '01/01/2005'),2)

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Cory Slaney SSCrazy Points: 2459 More actions · Answer 4

Thanks Farrell. You have been a great resource for a T-SQL newbie.

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 5

The first 6 characters of date format 100 (mon dd yyyy hh:miAM (or PM)) almost have what you need except for the single blank space. Here's another way to do it including the leading zero, if present. Will always return 5 characters in the mmmdd format...

Select Replace(Convert(Char(6),GetDate(),100),' ','')

--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)

ron k SSCertifiable Points: 6103 More actions · Answer 6

Everything you need to know about SQL Dates:

http://www.sql-server-performance.com/fk_datetime.asp

with credit to Frank Kalis

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Glad to help Cory. I would highly recommend reading the rest of the post and especially Frank's article. I plan to read over the weekend. Alright, at work Monday...

btw. Thanks Rob. I'm a knucklehead for not thinking about RIGHT...

I wasn't born stupid - I had to study.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Farrell, here's another method:

LEFT(DATENAME(month, '20050101'), 3) + REPLACE(STR(day('20050101'),2),' ','0')

But I think Jeff's method is much more elegant and efficient.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

High praise from the resident MVP. Thanks, Frank.

--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)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

It's simply "Honour to whom honour is due"

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Farrell Keough SSCoach Points: 17414 More actions · Answer 11

Thanks Frank!

I did not know that the STR function would handle dates. Cool!

I cannot get Jeff's solution to work without using GETDATE().

SELECT REPLACE( CONVERT( char(6), '20050101', 100), ' ', '')

(like I said, keep the Lawyer icon, your Honour )

I wasn't born stupid - I had to study.

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 12

From previous post consider the following SQL statements and return values

Select Replace(Convert(Char(6),GetDate(),100),' ','') as 'Date1'

--Returns

--Apr25

Select Replace(Convert(Char(6),'4/25/05',100),' ','')as 'Date2'

--Returns

--4/25/0

Select Replace(Convert(Char(6),'04/25/05',100),' ','')as 'Date3'

--Returns

--04/25/

-- The next one works with GetDate(),

-- ignores leading trailing spaces in input data

-- Does not add leading 0 for day

Select Left(Datename(month,Getdate()),3) + Rtrim(DatePart(dd,Getdate()))AS 'DATE4'

--Returns

--Apr25

Select Left(Datename(month,' 4/25/05'),3) + Rtrim(DatePart(dd,'04/25/2005 '))AS 'DATE5'

--Returns

--Apr25

Select Left(Datename(month,'04/5/05 '),3) + Rtrim(DatePart(dd,' 04/5/2005 '))AS 'DATE6'

--Returns

--Apr5

-- This one does not work with GetDate() in the REPLACE function

-- ignors leading and trailing spaces in input data

-- Adds leading 0 in day

Select LEFT(DATENAME(month, '20050101'), 3) + REPLACE(STR(day('20050101'),2),' ','0')

--Returns

--Jan01

Is there an SQL statement or function that will allow the use of getdate(), ignor leading or trailing spaces for dates as strings AND add a leading 0 for days

Thanks Mike

Rob Farley SSCrazy Points: 2738 More actions · Answer 13

Try this:

declare @thedate datetime

set @thedate = '4/25/05'

--set @thedate = getdate()

--set @thedate = '20050425'

--set @thedate = '20050401'

--set @thedate = '1-Apr-2005'

Select replace(Replace(Convert(Char(6),@thedate,109),' ','0'),' ','')

This is using a similar scenario, but first looking for the double-space, and converting that to a '0'.

By forcing the date into a datetime variable first, it won't treat '4/25/05' as a string.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 14

This is adding a leading 0 when one is not needed

declare @thedate datetime

set @thedate = '4/25/05'

Select replace(Replace(Convert(Char(6),@thedate,109),' ','0'),' ','')

Returns

Apr025 which has a leading 0 when it is not needed

Thanks

Mike