there are a few ways to compare days, or return the first day of the month in sql server.
say you need to compare if two dates fall on the same day. one way to manage the comparison is to use the date functions and compare each.
Day(@date)
Month(@date)
Year(@date)
which actually resolves to:
datepart(dd, @date)
datepart(mm, @date)
datepart(yy, @date)
another way, is to convert the date into a conical, formatted string - and use that for comparison.
using this method, gives you a YYYY-MM-DD representation of the date -- and converts that back into a date in a simple concise statement. (the "120" is the format specifier).
CAST(convert (char(10), GetDate(), 120), as datetime)
it's an easy way to get the date, set to 12:00 am. and using that, you can make a comparison in one line (rather than having year=year and month=month and day=day. but both work).
example :
declare @date datetime
-- comparison using datetime --
CAST(convert, char(10), @date, 120), as datetime) > CAST(convert, char(10), GetDate(), 120), as datetime)
-- comparison using string --
convert (char(10), @date, 120) = convert (char(10), GetDate(), 120)
you can also convert a date, to get the first of the month using the these methods. make sure you have two digit month formatting, if you're building a date string.
declare @iyear int
declare @imonth int
declare @syear varchar(10)
declare @smonth varchar(10)
set @iyear = year(@date)
set @imonth = month(@date)
set @syear = convert (varchar, @iyear)
set @smonth = replace( str(@imonth, 2, 0), ' ', '0' )
-- dashes are optional --
set @date = @syear + @smonth + '01'
set @date = @syear + '-' @smonth + '-01'
or, finally you can cast in a similar manner as before, giving you "YYYY-MM", and then add "-01" for the first:
set @date = convert(char(7), @date, 120) + '-01'
-- specific month, current year, example september 1st--
set @date = convert(char(4), GetDate(), 120) + '-09-01'
even better... especially if you're just comparing dates, or need a datetime version of the first of the month, you can do this little trick:
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
it works by using the "zero" time, a short-cut for "1900-01-01 00:00:00.000". It's a little goofy when you think about it, using this zero-point to evaluate another date, adding back the difference to compose the actual date you want. But it works, offers a reasonable way to center right on midnight, uses native datetime functions --and other than being slightly cryptic, the code is nice and clean.
Using this trick :
Monday of the current week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
if Monday isn't day weekday 0...
select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())
first day of the year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
first day of the quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
midnight, today
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
last day, prior month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
last day, current month
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))
first day of september
select DATEADD(mm,8,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
last day of the year
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)