DATE QUERY - 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 2012 Forums

Transact-SQL (2012)

DATE QUERY

Author

Topic

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-08-07 : 10:22:56

I need a query that returns dates between the first date of each month and the last date in that month.

so august 1 - august 31, or sept 1 - sept 30. so basicalliy the start date should be the 1st of each month and end date should be the last date in that month. no matter what day in the month the query is ran it should always return start date between the 1st and 30 or 31st or in feb case 28 or 29

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-07 : 11:08:24

Declare @date table(d datetime)
Declare @d datetime

set @d='20140801'

While @d<='20140831'
Begin
Insert into @date values (@d)
set @d=@d+1
End
Select d from @date

Javeed Ahmed

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-07 : 11:13:34

Here are some things to help:


declare @d datetime = Current_Timestamp
declare @firstOfMonth datetime = dateadd(month, datediff(month, 0, @d), 0)
declare @lastOfMonth datetime = dateadd(day, -1, dateadd(month, 1, @firstOfMonth))
declare @firstOfNextMonth datetime = dateadd(month, 1, @FirstOfMonth)
select @d, @firstOfMonth, @lastOfMonth, @firstOfNextMonth

For your query, I'd probably do something like this:


select ...
where myDateColumn >= @firstOfMonth and myDateColumn < @firstOfNextMonth

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources