DATETIME: just date and concate time

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  July 31, 2003 at 10:54 am

  #64371

  I've been following the discussion on using DATEADD instead of CONVERT when doing queries on just the Date portion of the datetime datatype.

  http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=14707&FORUM_ID=23&CAT_ID=2&Topic_Title=DATETIME+datatype&Forum_Title=General&whichpage=1

  However, the code being discussed just uses DATEADD to end up with the date followed by 00:00:00

  What is the best way to use datetime datatype in a WHERE statement when you need a specific time?

  Currently I use this:

  WHERE mydate >= CONVERT(VARCHAR(10), GETDATE(), 120) + ' 05:00:00'

  Which is the same as:

  WHERE mydate >= '2003-07-31 05:00:00'

  Is there a better way to do that instead of converting to a varchar?

  -SQLBill

• Antares686

  SSC Guru

  Points: 125444

  More actions

  July 31, 2003 at 11:16 am

  #468058

  Lots of ways to strip the time. Here is how I would do

  DATEADD(hh,5,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  July 31, 2003 at 11:30 am

  #468060

  Did exactly what I wanted.

  Thanks.

  -SQLBill