| Author |
 Topic  |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
 Posted - 01/16/2013 : 06:19:44
|
Hi,
I want to be able to set a date and time and use it to calculate previous stats other than todays date and from 6am backwards each day.
DECLARE @MyDate AS DATETIME
SET @MyDate = '2011-02-15 06:00:00' ---always want this to be previous day not todays date time
Can I use something like the above to build into a DateAdd function like:
=DateAdd(day, -1, @MyDate)
or if I have a startdate field can I:
where StartDate = @MyDate -1
SZ1
Learning and development is the driving force in the universe...! |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1416 Posts |
Posted - 01/16/2013 : 06:58:59
|
-- To get yesterday's date with 6 hrs
SELECT DATEADD(hh, 6, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-1), 0))
--
Chandu |
 |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/16/2013 : 08:31:56
|
Chandu,
Will that give me all records prior to 6am this morning? thats what I'm after, do I need the table logged date in place of the GETDATE to remove all calls after 6am this morning?
Something like this?
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat,
DATEADD(hh, 6, DATEADD(dd, DATEDIFF(dd, 0, c.OCCURED_DT -1), 0)) --occured_dt is the actual system logged date
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
--And c.Occured_DT < DATEADD(hh,10,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
Group by c.INTI_CATEGORY
ORDER BY TotalCat DESC;
SZ1
Learning and development is the driving force in the universe...! |
Edited by - sz1 on 01/16/2013 08:33:41 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47051 Posts |
Posted - 01/16/2013 : 23:47:07
|
quote:
Originally posted by bandi
-- To get yesterday's date with 6 hrs
SELECT DATEADD(hh, 6, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-1), 0))
--
Chandu
can be further simplified as
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)-.75
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1416 Posts |
Posted - 01/17/2013 : 03:32:35
|
quote:
Originally posted by sz1
Chandu,
Will that give me all records prior to 6am this morning? thats what I'm after, do I need the table logged date in place of the GETDATE to remove all calls after 6am this morning?
WHERE c.Occured_DT < DATEADD(hh, 6, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-1), 0)) --> gives the yesterday's records
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47051 Posts |
Posted - 01/17/2013 : 03:48:33
|
to get records prior to 6 am this morning it should be
WHERE c.Occured_DT < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)+.25
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/17/2013 : 05:10:58
|
Thanks chaps I will have a look at these and get back.
Thanks again
SZ1
Learning and development is the driving force in the universe...! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47051 Posts |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 01/17/2013 : 05:19:57
|
OK will do.
Thanks
SZ1
Learning and development is the driving force in the universe...! |
|
|
| |
Topic |
|
Declare datetime stamp
