Declare datetime stamp - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Declare datetime stamp

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 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 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
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 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...!

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 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
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 be

WHERE c.Occured_DT < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)+.25

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 again

SZ1
Learning and development is the driving force in the universe...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 05:12:19

quote:
Originally posted by sz1

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...!

welcome

make sure you read this to understand logic behind sugestions

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-01-17 : 05:19:57

OK will do.
Thanks

SZ1
Learning and development is the driving force in the universe...!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources