Need help on DatePart - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help on DatePart

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-10-26 : 03:45:47

Hi,

Scenario 1
Let say current date is 2009-10-26
How Im gonna using Datepart to display value as follow
Previous Month in YYYYMM | Current Month in YYYYMM
-------------------------------------------------
200909 | 200910

Scenario 2
Let say current date is 2010-01-26
How Im gonna using Datepart to display value as follow
Previous Month in YYYYMM | Current Month in YYYYMM
-------------------------------------------------
200912 | 201001

Select DatePart(???)

Please help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-26 : 04:12:02

declare @datecol datetime
select @datecol = '20091026'
select YEAR(@datecol) -- gives 2009
select MONTH(@datecol) -- gives 10
-- to display concatenated year and month you have to convert to varchar
select convert(varchar(4),YEAR(@datecol)) + convert(varchar(2),MONTH(@datecol))
-- if month is less than 10 then you need to add a leading zero
select convert(varchar(4),YEAR(@datecol)) + right('00'+convert(varchar(2),MONTH(@datecol)),2)
-- to get the previous month you can use dateadd() with negative value
select MONTH(dateadd(mm,-1,@datecol))

No, you're never too old to Yak'n'Roll if you're too young to die.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-10-26 : 04:38:37

quote:
Originally posted by webfred

declare @datecol datetime
select @datecol = '20091026'
select YEAR(@datecol) -- gives 2009
select MONTH(@datecol) -- gives 10
-- to display concatenated year and month you have to convert to varchar
select convert(varchar(4),YEAR(@datecol)) + convert(varchar(2),MONTH(@datecol))
-- if month is less than 10 then you need to add a leading zero
select convert(varchar(4),YEAR(@datecol)) + right('00'+convert(varchar(2),MONTH(@datecol)),2)
-- to get the previous month you can use dateadd() with negative value
select MONTH(dateadd(mm,-1,@datecol))

No, you're never too old to Yak'n'Roll if you're too young to die.

Let say today is 26 Jan 2010
select datepart(mm,getdate())

I got

1 not 01. Actually, I need to return as 01

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-26 : 04:46:46

Please read my post and you will see:

quote:
-- if month is less than 10 then you need to add a leading zero
select convert(varchar(4),YEAR(@datecol)) + right('00'+convert(varchar(2),MONTH(@datecol)),2)

No, you're never too old to Yak'n'Roll if you're too young to die.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-10-26 : 05:49:55

Yes. That's great.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-26 : 05:59:39

welcome