Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Dealing w/ Minutes in DateTime Field

    If I subtract 14 days from a datetime field, will the time of day that I run this query affect the resultset? I am running the query during "normal business hours", 8 am - 5 pm, and the records are entered during this time frame as well.

    ddave

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, if the datetime values in your table are not rounded off to midnight.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Mar 2004
    Posts
    45
    No.
    Hans.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. (I assume you are comparing these datetime values to the current datetime?....)
    Last edited by blindman; 04-16-04 at 16:17.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2004
    Posts
    193
    I am comparing fields that have datetime data to a value contained in (GETDATE() - 15). The "-15" part is for the past 15 days. Therefore if I run a query at 11:00 am will this exclude records that were entered at 9:00 am 15 days ago? That is the real issue. I want to have the records entered from 8:00 am thru 10:59 am the fifteenth day prior.

    If the records are excluded then I am aware of how to use CONVERT to change datetime into varchar and then back to datetime to drop the minutes by force. I just have to fiddle with it when I get back to work Monday. Thanks.

    ddave

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should not "subtract" 15 from a datetime value. Use the DATEDIFF function instead:

    DATEDIFF(Day, -15, getdate())

    ...and yes the result will be the same time as current, just 15 days earlier.

    By the way, you do not have to explicitly convert a character string representing a datetime value to a datetime datatype. The conversion is implicit. This is sufficient:

    DATEDIFF(Day, -15, convert(char(10), getdate(), 120))
    Last edited by blindman; 04-17-04 at 20:30.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Posts
    193
    Actually, I needed to use the DATEADD function and input a negative number, in this case -15, to obtain the correct datetime field. These are my results:

    --test the use of the DATEDIFF function
    SELECT DATEDIFF(day, -15, GETDATE())
    /*
    returns an incorrect answer:
    */
    38109

    --test the use of the DATEADD function
    SELECT DATEADD(day, -15, GETDATE())
    /*
    returns the correct answer:
    */
    2004-04-04 12:01:26.063

    Now to get rid of those pesky minutes. It is complicated because what I need to do is return ALL records that were input on April 4th, independent of the time of day.

    I have fiddled with this and this is what I have so far:

    --use DATEADD to obtain the date that is 15 days prior:
    SELECT DATEADD(day, -15, GETDATE())
    /*
    gets a date as a datetime value, subtracts 15 "days" from the date:
    */
    2004-04-04 12:23:42.190

    SELECT CONVERT(varchar, DATEADD(day, -15, GETDATE()), 112)
    /*
    gets a date as a datetime value, subtracts 15 "days" from the date
    and converts the datetime value into a varchar:
    */
    20040404

    SELECT CONVERT(datetime, (CONVERT (varchar, DATEADD(day, -15, GETDATE()), 112)) )
    /*
    gets a date as a datetime value, subtracts 15 "days" from the date
    and converts the datetime value into a varchar
    then coverts the varchar back into a datatime value:
    */
    2004-04-04 00:00:00.000


    ddave

Posting Permissions

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