[Resolved] Get current date and set time

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)

[Resolved] Get current date and set time

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-07-05 : 16:35:11

I need to create time buckets for current day, there will be 12 buckets each with 2 hours interval and the format should be in 120, like

2012-07-05 08:00:00', 2012-07-05 09:59:59, 2012-07-05 10:00:00, 2012-07-05 11:59:59

I know how to get current date but not sure how to force specific time for each bucket. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 16:54:10

[code]
;With NumberTable(N)
AS
(
SELECT 0
UNION ALL
SELECT N+2
FROM NumberTable
WHERE N+2 < = 12
)

SELECT DATEADD(hh,N,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)) AS Start,DATEADD(ss,-1,DATEADD(hh,N+2,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))) AS End
FROM NumberTable
[/code]

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-05 : 16:57:46

WITH n(n) AS (SELECT 0 UNION ALL SELECT n+2 FROM n WHERE n<22)
, d(d) AS (SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0))
SELECT DATEADD(HOUR,n,d) start, DATEADD(second, -1, DATEADD(HOUR,n+2,d)) finish
FROM n CROSS JOIN d

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-07-06 : 11:15:05

Great, they both work and creates start / end for the day.

Now, my question is: How can I get the start / end into variables? Reading the data base I need to test if a record falls within a specific start / end and update the a bucket with qty for this timespan.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-06 : 11:21:43

you cant get them to variables as there are set of them. You need to populate a table variable with them using INSERT...SELECT

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

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-07-06 : 13:04:09

Not sure if I understand correctly. I now have 12 start times and 12 end times so I need 24 variables like

2012-07-06 00:00:00.000 2012-07-06 01:59:59.000 which will be variable @Start_1 and variable @End_1

2012-07-06 02:00:00.000 2012-07-06 03:59:59.000 which will be
variable @Start_2 and variable @End_2

and so on....

now I read a transaction table and I will test transaction date and see in which bracket (start/end)it falls under and update my bucket with qty.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-06 : 13:24:56

Why do you need variables?

;WITH n(n) AS (SELECT 0 UNION ALL SELECT n+2 FROM n WHERE n<22)
	,d(d) AS (SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0))
	,d2(start,finish) AS (SELECT DATEADD(HOUR,n,d), DATEADD(SECOND, -1, DATEADD(HOUR,n+2,d))
	FROM n CROSS JOIN d)
SELECT d2.start,d2.finish,SUM(a.qty) 
FROM d2 
INNER JOIN OtherTable A
ON A.TransDate BETWEEN d2.start AND d2.finish
GROUP BY d2.start,d2.finish

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-07-06 : 14:21:10

The result will be passed back to a vb application where I will fill a grid view that has 12 cells horizontally, I will also use the data to create bar chart where each bar represents a time span.

Grid View Cells:
Product qty 00:00-1:59 qty 2:00-3:59 qty 4:00-5:59 etc

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-06 : 14:42:11

[code];WITH n(n) AS (SELECT 0 UNION ALL SELECT n+2 FROM n WHERE n<22)
,d(d) AS (SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0))
,d2(start,finish) AS (SELECT DATEADD(HOUR,n,d), DATEADD(SECOND, -1, DATEADD(HOUR,n+2,d))
FROM n CROSS JOIN d)
SELECT Product, [Qty 00:00-01:59], [Qty 02:00-03:59], [Qty 04:00-05:59], [Qty 06:00-07:59],
[Qty 08:00-09:59], [Qty 10:00-11:59], [Qty 12:00-13:59], [Qty 14:00-15:59], [Qty 16:00-17:59],
[Qty 18:00-19:59], [Qty 20:00-21:59], [Qty 22:00-23:59]
FROM (SELECT Product, Qty, 'Qty ' + CONVERT(CHAR(5),d2.start,8)+'-'+CONVERT(CHAR(5),d2.finish,8) Caption
FROM d2 INNER JOIN Product P ON P.TransDate BETWEEN d2.start AND d2.finish) a
PIVOT (SUM(Qty) FOR Caption IN([Qty 00:00-01:59],[Qty 02:00-03:59],[Qty 04:00-05:59],
[Qty 06:00-07:59],[Qty 08:00-09:59],[Qty 10:00-11:59],[Qty 12:00-13:59],[Qty 14:00-15:59],
[Qty 16:00-17:59],[Qty 18:00-19:59],[Qty 20:00-21:59],[Qty 22:00-23:59])) b[/code]

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-07-06 : 14:57:45

Impressive, that worked, thank you so much.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources