Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with datetime column Expand / Collapse
Author
Message
Posted Monday, January 26, 2009 7:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 19, 2013 11:03 AM
Points: 374, Visits: 280
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()))))



Post #643439
Posted Monday, January 26, 2009 8:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 10:37 AM
Points: 31, Visits: 133
Does this give you what you want?
select cast(convert(varchar(10),getdate(),101)+' 02:00AM' as DateTime)
Post #643445
Posted Monday, January 26, 2009 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #643451
Posted Monday, January 26, 2009 8:39 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 29, 2014 11:09 PM
Points: 3,108, Visits: 11,502
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')




Post #643475
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse