RE: complicated sql syntax - looking for explanation for working query

SSC Guru

Points: 323504

May 5, 2014 at 10:56 am

the starting at zero vs starting at one is because the function is adding time elements to generate your matrix of data

SELECT DATEADD( minute, n, DATEADD( YEAR, -2, GETDATE()))

if it started at 1, your starting time would be 12:01 instead of 12:00.

you could still use a 1-based tally table to generate the desired data, but then you have to make the calculation take that extra, initial minute into consideration;

SELECT DATEADD( minute, n -1, DATEADD( YEAR, -2, GETDATE()))

so in situations where you are fiddling with dates, it's prettier to manipulate the Tally/numbers you generate, instead of the functions using the generated values.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!