Results 1 to 8 of 8

Thread: DateDiff

  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: DateDiff

    I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.

    I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The concept of what a month is, is funny....you'd be better of if you could pick a fixed number of days

    It's like month is nondetermenistic, where days are determenistic..

    Never read that anywhere I don't think (damn I hate when that happens)


    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(myMonth99 int, myDay99 int, myYear99 int)
    GO
    
    INSERT INTO myTable99(myMonth99, myDay99, myYear99)
    SELECT 1,1,2003 UNION ALL
    SELECT 2,1,2003 UNION ALL
    SELECT 3,1,2003 UNION ALL
    SELECT 4,1,2003 UNION ALL
    SELECT 4,15,2003 UNION ALL
    SELECT 4,30,2003 UNION ALL
    SELECT 5,1,2003 UNION ALL
    SELECT 6,1,2003 UNION ALL
    SELECT 7,1,2003 UNION ALL
    SELECT 8,1,2003 UNION ALL
    SELECT 9,1,2003 UNION ALL
    SELECT 10,1,2003 UNION ALL
    SELECT 11,1,2003 UNION ALL
    SELECT 12,1,2003
    GO
    
    
    SELECT * FROM myTable99
     WHERE	DATEDIFF(mm,	
    	     CONVERT(datetime,
    	     CONVERT(varchar(4),myYear99)
    	+'/'+CONVERT(varchar(4),myMonth99)
    	+'/'+CONVERT(varchar(4),myDay99))
    ,GetDate()) >= 12
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    164
    Yeah, I see what you mean with the moth thing being funny. I figured out another way where I can do it. If I leave the date fixed where it displayes the year, month and day, I can accomplish what i have set out to do. This is what worked for me.

    select * from podinrh
    where date_rcvd between getdate() - 365 and getdate()
    order by date_rcvd

    I will also try out the script that you have provided me with Brett. Thanks for all the help you all provide.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah thats days...

    Works much better

    Code:
    SELECT * FROM myTable99
     WHERE	DATEDIFF(dd,	
    	     CONVERT(datetime,
    	     CONVERT(varchar(4),myYear99)
    	+'/'+CONVERT(varchar(4),myMonth99)
    	+'/'+CONVERT(varchar(4),myDay99))
    ,GetDate()) >= 365
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by estefex
    I will also try out the script that you have provided me with Brett.
    You know to just cut and paste it in to QA right?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Posts
    164
    Brett, It works fine too. I will have to see what format they are going to want to stick to. I am thinking they are going to want it with the date broken bown into columns like you did. I appreciate you help.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The Devil is in the details. To handle leap years, you might want to use this instead:

    select * from podinrh
    where date_rcvd between dateadd(year, -1, getdate()) and getdate()
    order by date_rcvd

    Also remember that the BETWEEN operator is inclusive. If your data is stored as whole dates (without the time of day), then you may end up excluding data from the first day or including an extra 24 hours at the end.

    For date comparisons, I use this instead of BETWEEN:

    select * from podinrh
    where date_rcvd > dateadd(year, -1, getdate()) and date_rcvd <= getdate()
    order by date_rcvd
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2004
    Posts
    164
    Thanks for the tipp because i was useing the time with the date as well. The number of records returned is a but greater than when I used the between statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •