Date - Always Sept 1 of last year

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)

Date - Always Sept 1 of last year

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-11-23 : 11:05:03

Newbie here.

I have to write a query where the date is always between Sept 1 of last year and yesterday. I got the yesterday, but I have no idea how to get the Sept 1 and this would be the end of Sept 1 at 11:59PM.

Thanks for your help.

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

bobmcclellan
Starting Member

46 Posts

Posted - 2010-11-23 : 11:21:13

Select convert(datetime,'9/1/' + rtrim(convert(char,year(getdate()) ,4)) + ' 11:59 pm')
returns
2010-09-01 23:59:00.000

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-11-23 : 12:17:04

Thanks! This is what I used taken from what you gave me and it worked.

CONVERT (datetime, '9/1/' + RTRIM(CONVERT (char, YEAR(GETDATE()) - 1, 0)))

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-26 : 09:47:48

Work on dates rather tan characters

select DATEADD(month,8,DATEADD(YEAR,DATEDIFF(year,0,getdate())-1,0))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources