Thread: Get Date Query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0

    Get Date Query


    Hi

    I have a table which holds the information of licences and when they are due to expire (date).

    I would like to create a DTS package to run a query every month to show which dates are due to be renewed 3 months ahead, so this month, I want to see all of the dates due to expire is October. At the moment I am opening up the query every month and showing where the dates are between '2012-10-01' and '2012-10-30'. Is it possible to use the GETDATE code to show this information.

    I have been doing lots of research, I have found this code (r.rendate = dateadd(m,+3,getdate())) which shows me all results in 3 months and anything after, I have also had one that shows me everything prior to 3 months and the 3 month date.

    Can anyone suggest a better way of doing this?

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    what you want to end up with is sql that resolves to this --
    Code:
    WHERE r.rendate >= '2012-10-01'
      AND r.rendate  < '2012-11-01'
    here, these date values will be calculated using formulae based on GETDATE()

    notice that the date range has an open-ended upper bound -- greater or equal to 1st of october, and strictly less than 1st of november

    you do ~not~ want to get involved in also calculating the last day of the month

    the open-ended upper range also works properly when your data involves datetime values instead of date values

    anyhow, now you need a formula for the 1st day of the month that's 3 months away

    start by calculating the 1st day of the current month

    if today is july 4th, you should subtract 3 days, i.e. 1 less than the current day of the month
    Code:
    DATEADD(DAY,-DAY(GETDATE())+1,CONVERT(DATE,GETDATE()))
    we need the CONVERT function to ensure the result is 00:00:00

    make sense so far?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    what you want to end up with is sql that resolves to this --
    Code:
    WHERE r.rendate >= '2012-10-01'
      AND r.rendate  < '2012-11-01'
    here, these date values will be calculated using formulae based on GETDATE()

    notice that the date range has an open-ended upper bound -- greater or equal to 1st of october, and strictly less than 1st of november

    you do ~not~ want to get involved in also calculating the last day of the month

    the open-ended upper range also works properly when your data involves datetime values instead of date values

    anyhow, now you need a formula for the 1st day of the month that's 3 months away

    start by calculating the 1st day of the current month

    if today is july 4th, you should subtract 3 days, i.e. 1 less than the current day of the month
    Code:
    DATEADD(DAY,-DAY(GETDATE())+1,CONVERT(DATE,GETDATE()))
    we need the CONVERT function to ensure the result is 00:00:00

    make sense so far?
    Thanks for your assistance, Yes, I think I am understanding this although I only ever write very basic queries so sometimes it takes a while to get me head around the different codes.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    so what do you get when you run this --
    Code:
    SELECT DATEADD(DAY,-DAY(GETDATE())+1,CONVERT(DATE,GETDATE())) AS d1
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    so what do you get when you run this --
    Code:
    SELECT DATEADD(DAY,-DAY(GETDATE())+1,CONVERT(DATE,GETDATE())) AS d1
    I'm not sure how to add it in to the select statement, this is what I have so far:-

    select u.pclname, c.clno as [REFERENCE],c.clname as [PREMISE], a.addline1, a.addline4 as [TOWN],a.addPostcode, cod.cddesc, r.rendate as [RENEWAL DATE], r.renReminderDate, r.rencomments
    from dbclient c
    INNER JOIN udparentclient u on u.pclid = c.clextid
    INNER join dbcontact co on co.contid = c.cldefaultcontact
    INNER JOIN dbaddress a on a.addid = coalesce (c.cldefaultaddress, co.contdefaultaddress)
    INNER join udrenewals r on c.clid = r.clid and (r.renCode = '36')
    LEFT join dbcodelookup cod on cod.cdcode = r.rencode and cod.cdtype = 'lictype'
    where (c.cltypecode = 'reformprem') and (u.adminclient = '1')

    The table with the dates is udRenewals and the date column I refer to is rendate

    Sorry, this probably doesn't help..
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    Originally Posted by popall1994
    I'm not sure how to add it in to the select statement
    see the first part of post #2

    you add it in to the WHERE clause

    but we weren't finished building the formulae

    so what do you get when you run this --
    Code:
    SELECT DATEADD(DAY,-DAY(GETDATE())+1,CONVERT(DATE,GETDATE())) AS d1
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    see the first part of post #2

    you add it in to the WHERE clause

    but we weren't finished building the formulae

    so what do you get when you run this --
    Code:
    SELECT DATEADD(DAY,-DAY(GETDATE())+1,CONVERT(DATE,GETDATE())) AS d1
    It errors on:- Msg 102, Level 15, State 1, Line 8
    Incorrect syntax near '('.

    I can't see what is causing it.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    Originally Posted by popall1994
    It errors on:- Msg 102, Level 15, State 1, Line 8
    line 8 ???

    what i asked you to run is only 1 line
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    line 8 ???

    what i asked you to run is only 1 line
    I get:- 2012-07-01 in the results....
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    Originally Posted by popall1994
    I get:- 2012-07-01 in the results....
    excellent

    so you now have a formula which gives the first day of the current month

    you are now almost ready to substitute this formula into the first line of the date range check --
    Code:
    WHERE r.rendate >= '2012-10-01'
      AND r.rendate  < '2012-11-01'
    however, you will first have to add 3 months to this formula

    and for the second date, you will have to add 4 months

    make sense?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    excellent

    so you now have a formula which gives the first day of the current month

    you are now almost ready to substitute this formula into the first line of the date range check --
    Code:
    WHERE r.rendate >= '2012-10-01'
      AND r.rendate  < '2012-11-01'
    however, you will first have to add 3 months to this formula

    and for the second date, you will have to add 4 months

    make sense?
    Yes it does....

IMN logo majestic logo threadwatch logo seochat tools logo