Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Changing DateTime to Date

    I have a table which picks up the datetime a record is entered. I want to run a query that picks up all records for that day, regardless of the time. How can I change the DateTime to just showing Date.

    From 2008-09-17 11:29:34
    To 2008-09-17

    This what I was looking at but doesnt work due to the error shown, not sure where I'm going wrong...

    ([Generated Date] = CONVERT(VARCHAR(10), '2008-09-17', 111))

    Error:
    Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is probably because you are using British settings (Gawd bless the queen!) rather than the heinous US settings. Check out this super article:
    http://www.karaszi.com/SQLServer/info_datetime.asp

    The short is use something like:
    Code:
    '20081917'
    or
    Code:
    '2008-19-17T00:00:00'
    Also, most (with one prominent exception) would recommend this to strip out the time portion (or, more accurately, set the time to midnight):
    Code:
    DATEADD(d, 0, DAEDIFF(d, YourDate, 0))
    You can substitue the "0" with a date constant (say '20080101') if you like, so as not to incur the wrath of the Cumudgeon
    Last edited by pootle flump; 09-17-08 at 08:44.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cumudgeon sounds like a pr0n star

    the correct way to return datetime values for a day is with an open-ended range test
    Code:
    WHERE datefld >= '2008-09-17'
      AND datefld  < '2008-09-18'
    as for displaying the value, that's when you would use CONVERT, not before
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah good points - bofadem.

    I missed the where predicate part - Rudy is, of course and boringly as ever, correct.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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