sql - date comparison using separate date part fields

Question

i have a statictics table which stores stats using separate fields for the parts of the date:

dayNumber
monthNumber
yearNumber

this works great when i need for a specific date.

I am wondering how to work with this, when i need a date range, say between this date, to that date.

many thanks!

See http://stackoverflow.com/questions/266924/create-a-date-with-t-sql for different ways to combine day, month and year integers to create date. Check Charles Bretana's answer — zedfoxus, Sep 12 '13 at 18:16

score 3 · Accepted Answer · edited Sep 12 '13 at 18:14

3

   CAST(
    CAST(yearNumber as char(4)) 
    + '-'
    + CAST(monthNumber as char(2)) 
    + '-'
    + CAST(dayNumber as char(2)) 
  as smalldatetime) 
  BETWEEN @StartDate and @EndDate

edited Sep 12 '13 at 18:14

PM 77-1

12,933
21
68
111

answered Sep 12 '13 at 18:10

outis nihil

736
6
16

Thanks Outis. I am using `SELECT TOP 40 tblCompanies.companyID, companyName, SUM(clicksLink) as mytotal FROm tblCompanies INNER JOIN tblCompanyClicks ON tblCompanies.companyID =tblCompanyClicks.companyID WHERE CAST( CAST( yearNumber as char(4)) + '-' + CAST(monthNumber as char(2)) + '-' + CAST( dayNumber as char(2)) as datetime) > GETDATE()-12 GROUP BY tblCompanies.companyID, companyName order by mytotal desc` but its coming up as empty, even though there are definitely stats there for the last 12 days – kneidels Sep 12 '13 at 18:31
GETDATE() - 12 doesn't mean anything, because GETDATE() returns a datetime data type and 12 is an int data type. You need something like `< DATEADD(dd,-12,GETDATE())` if you want the date to be more than 12 days ago, and `< DATEADD(hh,-12,GETDATE())` if you want the date to be more than 12 hours ago. – outis nihil Sep 12 '13 at 20:02

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

Reinforcing zfus comment to look at Charles Bretana's answer, you can use DATEADD to build the date for comparison in the WHERE clause:

...
WHERE DATEADD(yy, yearnumber - 1900,  
              DATEADD(m, monthnumber - 1, daynumber - 1))
      BETWEEN @StartDate AND @EndDate;

SQL fiddle

edited May 23 '17 at 12:20

Community

1
1

answered Sep 12 '13 at 18:35

Bryan

17,112
7
57
80

Thanks beargle. same as above - i am using the following revised query, but it comes up as empty, even there is data for the last 12 days - `SELECT TOP 40 companyID, SUM(clicksLink) as mytotal FROm tblCompanyClicks WHERE DATEADD(yy, yearnumber - 1900, DATEADD(m, monthnumber - 1, daynumber - 1)) > GETDATE()-12 GROUP BY companyID order by mytotal desc` – kneidels Sep 12 '13 at 19:06
1

This is technically a different (but related) topic from your original question. The problem is likely related to date subtraction (look at [DATEADD](http://technet.microsoft.com/en-us/library/ms186819.aspx)), but my recommendation is to create a new question (with sample table structure _AND_ data). Please also keep in mind that it's difficult to read multi-line code in the comments section. – Bryan Sep 12 '13 at 19:46

sql - date comparison using separate date part fields

2 Answers2