SQLTeam.com | Weblogs | Forums

DATETIME not showing correctly


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?

@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;

@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?


Rounding. The end dates are being rounded up. Turns out datetime cannot accurately represent 999 milliseconds so it rounds it up


Excellent, now I know I have changed the @enddate to be '20150331 23:59:50.000' and it works perfectly.

Thanks for saving me pulling my hair out :slight_smile:


And you have such nice hair! Be a real pity to pull it out!!


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.


Except... the database only has the ability to insert a time as HH:MM:SS

There are no .000 etc


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.


OK point taken, I will work through my queries and change to midnight of the day after.