Help with datetime column

• ishaan99

  SSCertifiable

  Points: 6261

  More actions

  January 26, 2009 at 7:53 am

  #155176

  I need to extract data from a table where created data >=yday data <=2 in the morning. This is what i have. Cutently it takes data only till 12 am for today how do i change it to accept tilll today 2 in the morning. any help will be greatly appreciated. Since it a dattime time i am nota bel to append it with '02:00:00' also. TIA

  SELECT DISTINCT * friom tableA

  where (cast(h.created as datetime)>= convert(datetime, floor(convert(float, getdate()-1)))

  and cast(h.created as datetime) < convert(datetime, floor(convert(float, getdate()))))

• andy.gear

  SSC-Addicted

  Points: 493

  More actions

  January 26, 2009 at 8:07 am

  #932606

  Does this give you what you want?

  select cast(convert(varchar(10),getdate(),101)+' 02:00AM' as DateTime)

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 26, 2009 at 8:14 am

  #932610

  Change:

  and cast(h.created as datetime) < convert(datetime, floor(convert(float, getdate()))))

  to:

  and cast(h.created as datetime) < dateadd(hour, 2, convert(datetime, floor(convert(float, getdate())))))

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 26, 2009 at 8:39 am

  #932630

  select distinct *

  from

  tableA

  where

  -- Greater than or equal to yesterday at 00:00:00.000 (midnight)

  h.created >= dateadd(dd,datediff(dd,0,getdate())-1,0)

  -- Less than today at 02:00:00.000 (2am)

  h.created < dateadd(dd,datediff(dd,0,getdate()),'02:00')