Results 1 to 3 of 3

Thread: Cannot search for a date in a DateTime column having TimeStamp

  1. #1
    Join Date
    Oct 2002
    Posts
    6

    Cannot search for a date in a DateTime column having TimeStamp

    I have a DateTime datatype column, with records showing as:

    2002-10-05 10:29:29.001
    2002-10-05 10:29:30.245
    2002-10-10 10:29:31.324
    2002-10-07 10:29:32.454
    2002-10-07 10:29:33.343
    2002-10-07 10:29:34.565
    2002-10-05 10:29:35.353
    2002-10-05 10:29:36.578
    2002-10-05 10:29:37.943
    2002-10-01 10:29:38.115
    2002-10-01 10:29:39.564

    and I want to search for records with date, say 2002-10-07, and it not work if I search with '10/07/2002' but if I give, say '10/07/200 10:29:34.565' it will only pull up that one record. I want to pull all 3 records having date of 10/07/2002.

    EXAMPLE 1:
    SELECT email_address AS Email, Sent
    FROM email_address
    WHERE Send = '#10/07/2002#'

    shows (0 row(s) affected)

  2. #2
    Join Date
    Sep 2002
    Posts
    169
    Unless you specify the time portion of the date, SQL Server defaults it to midnight (00:00:00) "this morning". Hence your query is being interpreted as give me all records with a date of midnight on the 7th of October.

    What you will need to do is to select records between midnight and 23:59:59 on the 7th of October.

    And a word of warning - be careful with the format of your date. SQL will interpret it based on the regional settings. If at all possible, using unambiguous dates e.g. '7 Oct 2002' instead of 07102002 or 10072002 or 20021007. I have seen an application working quite happily for many months suddenly fail because one of the sys admins decided that a change in the regional settings would be a good thing !

  3. #3
    Join Date
    Oct 2002
    Posts
    6
    I was able to find the solution, based on one of the postings by CS_TROYK on 9/27/02.

    SELECT email_address_ID as ID,
    email_address AS Email,
    Sent
    FROM email_address
    WHERE CAST(CAST(CAST(Sent AS real)As int)As datetime) = '10/10/2002'

    It works great.

    Thanks Stephen for posting your reply to my message.
    Thanks to CS_TROYK

Posting Permissions

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