I need to display the datetime for each of the variables as a header on my report, but it's not showing correctly. Can anyone explain?
declare
@StartDate datetime
,@StartDate1 datetime
,@StartDate2 datetime
,@StartDate3 datetime
,@EndDate datetime
,@EndDate1 datetime
,@EndDate2 datetime
,@EndDate3 datetime
--Edit the dates depending on the months required in the report
set @startdate = '20150101 00:00:00.000'
set @enddate = '20150331 23:59:59.999'
--These are auto calculated Dates
set @StartDate1=@StartDate;
set @StartDate2=dateadd(mm,1,@StartDate1);
set @StartDate3=dateadd(mm,2,@StartDate1);
set @EndDate1=dateadd(mm,-2,@EndDate);
set @EndDate2=dateadd(mm,-1,@EndDate);
set @EndDate3=@EndDate;
--Bluegum
select
@StartDate1 as [Bluegum M1 Start]
,@EndDate1 as [Bluegum M1 End]
,@StartDate2 as [Bluegum M2 Start]
,@EndDate2 as [Bluegum M2 End]
,@StartDate3 as [Bluegum M3 Start]
,@EndDate3 as [Bluegum M3 End]
;
The results show different dates, not sure why. Are these dates as displayed being used in my report, or the ones I have SET?
That's just one reason datetime[2] ranges should always be >= and <, not between. So, for Jan thru all of Mar, you would do:
set @startdate = '20150101'
set @enddate = '20150401'
...
WHERE datetime_column >= @startdate AND datetime_column < @enddate
You'll end up pulling more of your hair out that way because you're missing nearly a full 10 seconds of the day. See Scott's post above for the correct way to do end dates and the criteria to select from a range of dates that have the time.
Understood but there are two problems with that... you posted that you used 23:59:50 instead of 23:59:59. Second, it's a real bad habit to get into because it won't always be that way. Scott's method is method that works correctly for all temporal resolutions and it's a good habit to get into because temporal resolutions in the database can easily change.
