Time Interval - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Time Interval

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-04-15 : 05:38:56

Dear all

I need to reterview all data from day before yesterday 9pm to yesterday 9pm (exactly 24hrs - data) using datecreated(datatype-datetime)field from cst table

How to do this.Any help would be highly appreciated.

Regards,
SG

I tried this,but not working.
(cst.DateCreated > dateadd(hh,-48,getdate()) and cst.DateCreated > dateadd(hh,-24,getdate()))

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 05:55:23

Hey your data is stored 12 H format or 24 H

Raghu' S

satheesh
Posting Yak Master

152 Posts

Posted - 2011-04-15 : 06:11:01

Thanks for you reply raghuveer125

Its in 24hs format raghu.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-15 : 06:23:55

declare @EndTime datetime, @StartTime datetime;
set @EndTime = '2011/04/14 21:00:00' -- Set this to the end of the range you'r interested in, the 9pm yesterday
Set @StartTime = dateadd(dd,-1,@EndTime )

SELECT ...
WHERE DateCreated between @StartTime and @EndTime

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-15 : 06:24:42

quote:
Originally posted by raghuveer125

Hey your data is stored 12 H format or 24 H

Datetime columns don't store a format. The column stores the date in a numerical format. It's only when converted to a string (for display) that there's a format.

--
Gail Shaw
SQL Server MVP

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-04-15 : 06:25:25

check it once

select datecreated
from #test
where datecreated between DATEADD(day,-2,getDate()) and DATEADD(day,-1,getDate())

--Ranjit

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-04-15 : 06:27:36

check this also

select datecreated
from #test
where datecreated between DATEADD(hour,-48,getDate()) and DATEADD(hour,-24,getDate())

--Ranjit

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 06:36:46

declare @EndTime datetime, @StartTime datetime;
set @EndTime = CONVERT(VARCHAR(14), GETDATE()-1, 111)+' 21:00' -- Change your system date and check if you want current day to --previous day then just remove -1 from getdate()-1
Set @StartTime = dateadd(dd,-1,@EndTime )

select @EndTime
select @StartTime

SELECT ...
WHERE DateCreated between @StartTime and @EndTime

Raghu' S

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-15 : 07:53:13

quote:
Originally posted by raghuveer125

declare @EndTime datetime, @StartTime datetime;
set @EndTime = CONVERT(VARCHAR(14), GETDATE()-1, 111)+' 21:00' --

Just for your info, that is just about the most inefficient way to remove the time from a date.

It would also be appreciated if you wrote your own posts, didn't just copy someone else's and change a few lines without attribution.

--
Gail Shaw
SQL Server MVP

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 08:07:55

Yep that is true if system date is wrong then it wont give proper output! Thanks. And sorry I did not copy your post.

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources