current date last month - 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 2005 Forums

Transact-SQL (2005)

current date last month

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-04-22 : 08:36:26

hi,
If today is the 22/4/08
how can I get the day last month,
eg. 22/3/08

?thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 08:52:03

select dateadd(month,-1,dateadd(day,datediff(day,0,getdate()),0))

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-04-22 : 10:31:55

thats great,
but what if I wanted to select a date , eg :

declare @Date1 datetime
SET @Date1 = '2008-02-01'
select dateadd(month,-1,dateadd(day,datediff(day,0,@Date1),0))

But I want the date to show '2008-01-22'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:35:06

You mean always the 22nd of previous month?
That is not what this topic header is about.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:36:30

[code]declare @Date1 datetime

SET @Date1 = '20080201'

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date1), 21)[/code]

E 12°55'05.25"
N 56°04'39.16"

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-04-22 : 10:37:36

no, not always the 22nd.
It must be what every the day is today,.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-22 : 11:39:43

You need to decide what you want to do when the same day last month does not exist.

For example, what do you do on 2008-03-31?

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-23 : 02:27:28

quote:
Originally posted by Peso

declare @Date1 datetime

SET @Date1 = '20080201'

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date1)-1, 21)

E 12°55'05.25"
N 56°04'39.16"

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-23 : 05:39:58

My interpretation of the requirements is: You want the date in the month before a given date which has the same day as today. Is that right?

If so (and with reference to MVJ), what do you want to do if today is the 31st and the date is in 10th March 2008, say? Return 29 Feb 2008, 31 Jan 2008, or something else?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-23 : 05:44:03

To adapt what we have so far to meet my interpretation...

DECLARE @Date1 DATETIME
SET @Date1 = '20080201'
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date1)-1, DATEPART(DAY, GETDATE())-1)

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources