March 11, 2009 at 5:16 am
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
March 11, 2009 at 5:35 am
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
March 11, 2009 at 5:35 am
hi
you may need something like this:
select REPLICATE('0',2 - LEN(cast(month(getdate())as varchar(2)))) + cast(month(getdate())as varchar(2))
March 11, 2009 at 5:52 am
Thank you... this one was the easiest for me to incorporate and it worked just fine.
Roger
March 11, 2009 at 7:25 am
select right('00' + cast(month(getdate()) as varchar), 2)
is a little shorter
MRB
March 11, 2009 at 7:33 am
Shorter.
select [Month] = right(100+month(getdate()),2)
Results:
Month
-----
03
March 11, 2009 at 7:56 am
Game, set and match to Michael!
🙂
Greets
Flo
March 11, 2009 at 8:00 am
Thanks!
Rog
March 11, 2009 at 8:17 am
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
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
March 11, 2009 at 10:23 am
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
March 11, 2009 at 10:31 am
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
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
March 11, 2009 at 1:23 pm
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
March 11, 2009 at 1:34 pm
[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]
March 11, 2009 at 2:51 pm
select convert(varchar(2), getdate(), 1)
March 11, 2009 at 2:53 pm
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply