Author |
Topic |
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-01-16 : 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 timeCan 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 -1SZ1Learning and development is the driving force in the universe...! |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-16 : 06:58:59
|
-- To get yesterday's date with 6 hrsSELECT DATEADD(hh, 6, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-1), 0))--Chandu |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-01-16 : 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 dateFrom DIM_CALL cWhere 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_CATEGORYORDER BY TotalCat DESC;SZ1Learning and development is the driving force in the universe...! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-16 : 23:47:07
|
quote: Originally posted by bandi -- To get yesterday's date with 6 hrsSELECT DATEADD(hh, 6, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()-1), 0))--Chandu
can be further simplified asSELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)-.75------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-17 : 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
52326 Posts |
Posted - 2013-01-17 : 03:48:33
|
to get records prior to 6 am this morning it should beWHERE c.Occured_DT < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)+.25------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-01-17 : 05:10:58
|
Thanks chaps I will have a look at these and get back.Thanks againSZ1Learning and development is the driving force in the universe...! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2013-01-17 : 05:19:57
|
OK will do.ThanksSZ1Learning and development is the driving force in the universe...! |
|
|
|