Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Red face Unanswered: SQL table data, smalldatetime and Calendar control

    Inside my SQL 2005 database I have a SmallDateTime with the layout "mm/dd/yyyy hh:mm:ss AM/PM" and then on a selected date of a calendar control it returns the date with 12:00:00 AM which obviously doesn't match with my database's time.

    So, I'm looking for a way to drop the time off completely (for the query purpose) the database date when running a select statement. I don't want to touch SQL table data by modifying it, I just want to be able to query sql table using calendar control. Does anyone now how to accomplish this? Thank you in advance. - Nietzky

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are many ways to strip the time off a datetime value, but this is the most efficient --
    Code:
    dateadd(d,datediff(d,0,datecol),0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    2

    conversion/ smalldatetime

    Thank you!

    I also cam eacross this solution: CAST(FLOOR(CAST(dtPostDate AS float)) AS datetime) .

    Is your more efficient than this one?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    until your tests prove otherwise, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Given the inexact nature of float it might not even be accurate in all cases.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For many reasons, I would suggest using code more like:
    Code:
    SELECT meta_list
       FROM dbo.myTable
       WHERE targetDate BETWEEN @argDate AND @argDate + ' 23:59:59.997'
    This does not require any changes to the table data, it finds any rows for the given date, and it allows the SQL engine to use indicies if they exist.

    -PatP

Posting Permissions

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