Day Before the first of the month, 1 year later

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

Transact-SQL (2000)

Day Before the first of the month, 1 year later

Author

Topic

homerjay80
Starting Member

26 Posts

Posted - 2007-05-09 : 12:32:21

Hello,

I have a select that formats a date column to get the first day of the month after the 1 year anniversary.

Select CONVERT(CHAR(10),CONVERT(DATETIME,RTRIM(CONVERT(CHAR(2),MONTH(DATEADD(MM,1,DATEADD(YY,1,EecDateOfOriginalHire))))) + '/1/' +  RTRIM(CONVERT(CHAR(4),YEAR(DATEADD(MM,1,DATEADD(YY,1,HireDate)))))),101) from ....

Now I want this to also give me day before this date.
Example: Given date 1/1/2007, return 1/31/2008
Any suggestions.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-09 : 12:50:29

SELECT DATEADD(month, 12, DATEADD(month, DATEDIFF(month, -1, '20070101'), -1))

Peter Larsson
Helsingborg, Sweden

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-09 : 12:55:26

Try something like:

SELECT DATEADD(YEAR, 1, (DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), 0) - 1))
-- slightly more simple, but less readable perhaps:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 11, GETDATE()), 0) - 1

EDIT: Bah Petere beat me and I mis-read the requirement.. :(

homerjay80
Starting Member

26 Posts

Posted - 2007-05-09 : 13:01:06

Thanks Peter
Both your efforts are appreciated. :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-09 : 14:04:26

For readability and easy maintenance, I recommend using the Date() function here:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

then it is simply:

select dbo.Date( year(getdate())+1 , month(getdate())+1 , 1)-1

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-09 : 15:26:49

This seems nice and short:


select dateadd(month,datediff(month,0,HireDate)+13,-1)

CODO ERGO SUM

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-10 : 01:48:48

Declare @d datetime
Select @d = '1/1/2007'

Select dateadd(month, 1, dateadd(year, 1, @d)) - 1, dateadd(month, 1, dateadd(year, 1, @d))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources