Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: where clause for datetime field

    Hi,

    I need to delete some rows that have a timestamp of 11/17/03 3:15:19 PM in them. What should my where clause look like. Will this clause work

    where datetime = '11/17/03' OR where datetime = '11/17/03 3:15:19 PM'


    I do not have any rows that have a different timestamp for the same date 11/17/03. Which one of the above 2 where clauses would work.

    Please advise.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your first example will not work, as you need to specify both the date and time exactly and SQL Server assumes midnight as a default.

    Use your second example, or specify a range such as
    where datetime >= '11/17/03' and <'11/18/03'

    blindman

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your first example will not work, as you need to specify both the date and time exactly and SQL Server assumes midnight as a default.

    Use your second example, or specify a range such as
    where datetime >= '11/17/03' and <'11/18/03'

    blindman

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    You could use that format for a smalldatetime (only with 00 for seconds). But for datetime you would have to be exact
    '11/17/03 3:15:19.123 PM'. You can also use:
    where datetimecreated > '11/16/2003' and datetimecreated < '11/18/2003'.

    Stay away from keywords like datetime - if you have them put brackets around the word when not actually using the keyword.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use >= to make sure midnight values are included:
    where datetimecreated >= '11/16/2003' and datetimecreated < '11/18/2003'

    ...not:
    where datetimecreated > '11/16/2003' and datetimecreated < '11/18/2003'

    blindman

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Oops - I pasted the wrong code segment - "haste makes waste".

    Anyway, the final correct statement:

    where datetimecreated >= '11/17/2003' and datetimecreated < '11/18/2003'.

Posting Permissions

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