Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Using BETWEEN with SQL Server Dates

    I'm converting my Access 2003 DB to SQL Server 2005 Express and I use date range comparisons all the time. I learned that the dates also include a time in SQL Server which to me right now seems is going to be a hassle. I don't need the time at all. There will be many times when the current date will be input into a record. All I want is the date part. My first thought was to extract the date part each time I need to use it, but it would be better if I could come up with a test condition that will take any date/time and give the right results. I read about one that uses the following:

    get the any dates between 1/1/2006 and 12/31/2006.

    The test conditon would be:

    SELECT * WHERE(date >= 1/1/2006 AND date < 1/1/2007)

    You add a day to your upper range and use the less than operator. This wouldn't be so bad for static dates, but each time I used a dynamic date I'd have to add one day to the upper range before testing. I also read using any kind of function or the "Like" operator on a date renders the indexes useless.

    I guess what I'm looking for is method to get the results similar to using the "BETWEEN" statement where I don't have to manipulate any of the dates prior or during testing.

    My thought right now is from a different perspective. Any time I need the current date I'd truncate the time part leaving me with a date and a time part of "00:00:00". My only problem with this is what if in code I accidentally forget to truncate the time. I could see this happen giving how much I need to use the current date and being used to not having to worry about the time part.

    Any help with this will be greatly appreciated. I think this is my last major hurdle before I'm convinced everything will convert over nicely to SQL Server.

  2. #2
    Join Date
    Jul 2006
    Posts
    87
    Let me just clarify here. You are moving from access (no timestamp in your date, based on your message) to SQL Server. So then, why would you encounter a date that included a time in the conversion to SQL Server.

    Between exists in SQL Server, so you could still use it in your date comparison calls.

    Good Luck,
    Code Carpenter
    Code:
    If this.Helped
    Then Say("Thank You")
    Else Goto 'Help'

  3. #3
    Join Date
    Jan 2004
    Posts
    145
    The problem will occur after I have converted to SQL Server. Once I start using SQL Server and the GETDATE() function I'll be getting times with the dates.

    However I just thought of something. I could use VBA to get the current date and pass it as a parameter to a sproc instead of using GETDATE() in the sproc.

    It is almost time to go home so I'll save that thought for tomorrow. Now I will be looking forward to work tomorrow

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    Quote Originally Posted by gwgeller
    I'm converting my Access 2003 DB to SQL Server 2005 Express and I use date range comparisons all the time. I learned that the dates also include a time in SQL Server which to me right now seems is going to be a hassle.
    Uhm.....the datetime datatype in Access includes time as well....
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jan 2004
    Posts
    145
    I guess your right, but I've never had this problem with BETWEEN in Access. I'll have to look at how the data is stored later.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  6. #6
    Join Date
    Jan 2004
    Posts
    145
    Again, blindman is correct. The problem is you can't get just the date in SQL Server without doing some work (as far as I've read). I looked back into my Access/VBA code and in one example I use VBA to get the date and pass it to the query, which is probably what I'll end up doing. However Jet SQL has three different functions:

    Now() - Returns date and time
    Date() - Returns date only
    Time() - Returns time only

    All three are stored in the same data type. So I guess the question should be is there an equivalent to the Date() function in SQL Server? My guess in no, but I am a mere SQL Server newbie.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by gwgeller
    So I guess the question should be is there an equivalent to the Date() function in SQL Server?
    Code:
    SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
    Ta da!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2004
    Posts
    145
    Let me rephrase. Is there a built in function in SQL Server equivalent to Date() in Access? Such as Now() in Access and GETDATE() in SQL Server return the current date/time. Again, regrettably, I will say the answer is no.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    It is not built-in, but is easily calculated using the code Pootle posted.

    If you want, you can wrap that code in a user-defined function.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    you can create a user defined function with poodle's code

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rbackmann
    you can create a user defined function with poodle's code

    OPr you can just steal some code

    http://weblogs.sqlteam.com/jeffs/arc...2/02/2959.aspx

    But all date information is stored in SQL Server as datetime...and you want it that way

    Easrly on M$ was going to make 2 new datatypes, DATE and TIME

    But they gave up
    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.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by rbackmann
    you can create a user defined function with poodle's code
    How dare you?

    Pootle
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    Now, don't get your fur all up.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Jul 2006
    Posts
    87
    Quote Originally Posted by blindman
    Now, don't get your fur all up.
    Careful Blindman, you know those small doggies are the ones that bite the most...
    Code Carpenter
    Code:
    If this.Helped
    Then Say("Thank You")
    Else Goto 'Help'

Posting Permissions

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