CAST Statement - Month Conversion

Question

Post reply

CAST Statement - Month Conversion

Roger Abram

Hall of Fame

Points: 3683
More actions
March 11, 2009 at 5:16 am

#132004

I'm using the following to return the current month in a comparison statement:
cast(month(getdate())as varchar(2))
However, for the month of March, it just returns the value of 3.
I need it to return the value as 03
How can I accomplish that?
Thanks for any and all help.
Rog

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

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hello Roger

You can do it like:

DECLARE @d DATETIME

SET @d = GETDATE()

SELECT @d, CASE WHEN DATEPART(MONTH, @d) < 10 THEN '0' ELSE '' END + CONVERT(VARCHAR(2), DATEPART(MONTH, @d))

But as currently discussed in this thread http://www.sqlservercentral.com/Forums/Topic672641-149-1.aspx you should consider to handle the formatting within the client application.

Greets

Flo

Aram Koukia SSCommitted Points: 1521 More actions · Answer 2

hi

you may need something like this:

select REPLICATE('0',2 - LEN(cast(month(getdate())as varchar(2)))) + cast(month(getdate())as varchar(2))

[font="Courier New"]Aram Koukia: http://www.koukia.ca[/font]

Roger Abram Hall of Fame Points: 3683 More actions · Answer 3

Thank you... this one was the easiest for me to incorporate and it worked just fine.

Roger

Martin_Burton SSCommitted Points: 1931 More actions · Answer 4

select right('00' + cast(month(getdate()) as varchar), 2)

is a little shorter

MRB

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Shorter.

select [Month] = right(100+month(getdate()),2)

Results:

Month

-----

03

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Florian Reischl

SSC-Dedicated

Points: 37301

March 11, 2009 at 7:56 am

#957487

Game, set and match to Michael!

🙂

Greets

Flo

Roger Abram Hall of Fame Points: 3683 More actions · Answer 7

Roger Abram

Hall of Fame

Points: 3683

March 11, 2009 at 8:00 am

#957488

Thanks!

Rog

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Michael Valentine Jones (3/11/2009)
Shorter.
select [Month] = right(100+month(getdate()),2)
Results:
Month
-----
03

Interesting...

Noticed this while messing with possible solutions...

SELECT

GETDATE(), -- Current Datetime

LEFT(GETDATE(), 19), -- StringDate 'Mar 11 2009 2:10PM'

CAST(LEFT(GETDATE(), 11) AS DATETIME) -- TimestrippedDateTime

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

The code below is the standard way to get a date without the time in SQL Server.

Extensive testing (by me and many others) has shown that casting a date to a string and back to a datetime in much slower.

select TimestrippedDateTime = dateadd(dd,datediff(dd,0,getdate()),0)

TimestrippedDateTime

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

2009-03-11 00:00:00.000

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Michael Valentine Jones (3/11/2009)
The code below is the standard way to get a date without the time in SQL Server.
Extensive testing (by me and many others) has shown that casting a date to a string and back to a datetime in much slower.
select TimestrippedDateTime = dateadd(dd,datediff(dd,0,getdate()),0)
TimestrippedDateTime
------------------------------------------------------
2009-03-11 00:00:00.000

Yep, I remember that thread, quite a time ago now. There's more...

SELECT

DATEADD(yy,DATEDIFF(yy, 0, GETDATE()),0) AS FirstDayOfYear,

DATEADD(mm,DATEDIFF(mm, 0, GETDATE()),0) AS FirstDayOfMonth,

DATEADD(dy,DATEDIFF(dy, 0, GETDATE()),0) AS TodayNoTime,

DATEADD(hh,DATEDIFF(hh, 0, GETDATE()),0) AS TodayNoMinutes,

DATEADD(mi,DATEDIFF(mi, 0, GETDATE()),0) AS TodayNoSeconds

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

The last one (For rounding off to the nearest minute) does not work for the entire range of datetime. I have posted a more general method below.

print 'Limited start of minute method'

select

dateadd(mi,datediff(mi,0,DT),0) AS TodayNoSeconds

from

(select DT = convert(datetime,'80001231') ) a

go

print 'MVJ Method'

select

dateadd(ms,-(datepart(ss,DT)*1000)-datepart(ms,DT),DT) AS TodayNoSeconds

from

(select DT = convert(datetime,'80001231') ) a

Results:

Limited start of minute method

Server: Msg 535, Level 16, State 1, Line 2

Difference of two datetime columns caused overflow at runtime.

MVJ Method

TodayNoSeconds

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

8000-12-31 00:00:00.000

The solutions below work for the entire range of datetime values, except where noted.

Start of Time Period Functions

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

Start of Week Function

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 12

[font="Verdana"]Can I put my hand in the air and recommend a Calendar table? It's a specialised version of a Tally/Numbers table, and you can do all the necessary date calculations ahead of time and just look them up. :w00t:

Saves a lot of hassle.

[/font]

Steven Danowitz Grasshopper Points: 16 More actions · Answer 13

Steven Danowitz

Grasshopper

Points: 16

March 11, 2009 at 2:51 pm

#957908

select convert(varchar(2), getdate(), 1)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 14

Bruce W Cassidy (3/11/2009)
[font="Verdana"]Can I put my hand in the air and recommend a Calendar table? It's a specialised version of a Tally/Numbers table, and you can do all the necessary date calculations ahead of time and just look them up. :w00t:
Saves a lot of hassle.
[/font]

This is also a way to go. Here is a link to a function I wrote that you can use to load a date table.

Date Table Function F_TABLE_DATE

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519