Changing the time in a datetime

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

Changing the time in a datetime

Author

Topic

hannadernbrant
Starting Member

4 Posts

Posted - 2010-04-14 : 05:46:59

Hello,
I've got a problem with how to change the time in an existing datetime. I'm doing a booking calendar and I've started the SQL code but am now stuck. What i've got so far is this:

INSERT INTO test
VALUES (getdate())

For starting i want it to store today's date, then i want it to add one day, so far i've done this like this:

SELECT DATEADD (dd, 1, test.time)
FROM test

WHERE tid = (SELECT MAX(time) FROM test)

I'm not sure how to save this into the database. When I've added a day to the bookingdate i want to change the time to 8.00 and then check if there's a time there allready in the database, then add 2 hours and search for that etc until it's 16.00. then again change day and start at 8, i want this to continue throughout the month. I've gotten stuck and am in bad need of some help

thanks,
Hanna

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 06:28:32

Here's a sample that may help you on your journey. It uses 2 methods. The first method is faster, however it does have a limit. The maximum date it will return is 4094 hour after 8:00am this morning. That's nearly 6 months. The second method (commented out) is slowerm, as it uses a loop, checking each value from 8:00am today, until 4:00pm, then 8:00am tomorrow...

Hope this helps.

CREATE TABLE #tmp (
	Date DATETIME
)

INSERT INTO #tmp
SELECT DATEADD(hh, 8, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
UNION ALL SELECT DATEADD(hh, 10, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
UNION ALL SELECT DATEADD(hh, 12, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
GO 

DECLARE @dt DATETIME
SET @dt = DATEADD(hh, 8, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))

-- Method 1
INSERT INTO #tmp
SELECT MIN(v.Date)
FROM (	SELECT DATEADD(hh, number * 2, @dt) AS Date
		FROM master.dbo.spt_values 
		WHERE [Type] = 'P' ) v
LEFT JOIN #tmp t
	ON t.Date = v.Date
WHERE t.Date IS NULL
	AND DATEPART(hh, v.Date) BETWEEN 8 AND 16


/*
-- Method 2
WHILE EXISTS (SELECT 1 FROM #tmp WHERE Date = @dt)
BEGIN
	IF DATEPART(hh, @dt) = 16
		SET @dt = DATEADD(hh, 16, @dt)
	ELSE
		SET @dt = DATEADD(hh, 2, @dt)
END

INSERT INTO #tmp
SELECT @dt
*/

SELECT * FROM #tmp
GO

DROP TABLE #tmp
GO

In your solution, you should also make sure the date column is indexed.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-14 : 07:39:22

you can use the first method with a tally table or function instead of master.dbo.spt_values if you needed more dates.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-14 : 07:52:04

Method 3
use F_TABLE_DATE

KH
[spoiler]Time is always against us[/spoiler]

hannadernbrant
Starting Member

4 Posts

Posted - 2010-04-14 : 11:27:56

Thank you for the replys, seems to be a lot of useful methods to do this :)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 11:41:29

quote:
Originally posted by khtan

Method 3
use F_TABLE_DATE

I'm not sure that would work. While that function does a hell of a lot (most of which isn't needed for this solution), it doesn't actually have a time component.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

hannadernbrant
Starting Member

4 Posts

Posted - 2010-04-16 : 07:27:37

quote:
Originally posted by DBA in the making

Here's a sample that may help you on your journey. It uses 2 methods. The first method is faster, however it does have a limit. The maximum date it will return is 4094 hour after 8:00am this morning. That's nearly 6 months. The second method (commented out) is slowerm, as it uses a loop, checking each value from 8:00am today, until 4:00pm, then 8:00am tomorrow...

Hope this helps.
CREATE TABLE #tmp (
Date DATETIME
)

INSERT INTO #tmp
SELECT DATEADD(hh, 8, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
UNION ALL SELECT DATEADD(hh, 10, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
UNION ALL SELECT DATEADD(hh, 12, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
GO

DECLARE @dt DATETIME
SET @dt = DATEADD(hh, 8, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))

-- Method 1
INSERT INTO #tmp
SELECT MIN(v.Date)
FROM ( SELECT DATEADD(hh, number * 2, @dt) AS Date
FROM master.dbo.spt_values
WHERE [Type] = 'P' ) v
LEFT JOIN #tmp t
ON t.Date = v.Date
WHERE t.Date IS NULL
AND DATEPART(hh, v.Date) BETWEEN 8 AND 16

/*
-- Method 2
WHILE EXISTS (SELECT 1 FROM #tmp WHERE Date = @dt)
BEGIN
IF DATEPART(hh, @dt) = 16
SET @dt = DATEADD(hh, 16, @dt)
ELSE
SET @dt = DATEADD(hh, 2, @dt)
END

INSERT INTO #tmp
SELECT @dt
*/

SELECT * FROM #tmp
GO

DROP TABLE #tmp
GO

In your solution, you should also make sure the date column is indexed.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

I've spent many hours looking through this code and trying to figure out how to use it. Since i need to check for every hour it generates against the database and I'm not sure how to make this happen. As mentioned earlier I am very novice on SQL code so it's a bit hard for me to understand some functions.
Thanks and sorry for not completly understanding.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-18 : 14:09:48

I'll go through the code piece by piece. (Sorry for the delay, been busy over the last few days).

This first part simply creates a temp table to store the results in, and populates it with 3 initial records.

CREATE TABLE #tmp (
	Date DATETIME
)

INSERT INTO #tmp
SELECT DATEADD(hh, 8, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
UNION ALL SELECT DATEADD(hh, 10, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
UNION ALL SELECT DATEADD(hh, 12, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))
GO

This creates a datetime variable, and populates it today's date, at 8:00am.

DECLARE @dt DATETIME
SET @dt = DATEADD(hh, 8, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE())))

This is the main query, it creates a list of times, using the [number] field of the master.dbo.spt_values table (where type = 'p'). This number field contains the numbers 0 though 2047. From this, it passes the number field to DATEADD(hh, number * 2, @dt), which adds 2 hours to the date variable created earlier, for each value of [number]. So 0 becomes 2010-04-18 08:00:00 and 1 becomes 2010-04-18 10:00:00, etc.

Next, it uses a left join onto the original table to filter out records that already exist in that table, by specifying a where clause of 'WHERE t.Date IS NULL'. The other part of the where clause, 'AND DATEPART(hh, v.Date) BETWEEN 8 AND 16' filters out any times that are not between 8:00am and 4:00PM. The MIN() in the select clause gets the first value in this list, and the INSERT inserts it into the temp table.

-- Method 1
INSERT INTO #tmp
SELECT MIN(v.Date)
FROM (	SELECT DATEADD(hh, number * 2, @dt) AS Date
		FROM master.dbo.spt_values 
		WHERE [Type] = 'P' ) v
LEFT JOIN #tmp t
	ON t.Date = v.Date
WHERE t.Date IS NULL
	AND DATEPART(hh, v.Date) BETWEEN 8 AND 16

Method 2 works by taking the same datetime starting point (@dt), running though a loop, adding 2 hours to it each time, until it finds a time not already in the database. When the loop hits 4:00pm, it starts from 8am the next morning.


/*
-- Method 2
WHILE EXISTS (SELECT 1 FROM #tmp WHERE Date = @dt)
BEGIN
	IF DATEPART(hh, @dt) = 16
		SET @dt = DATEADD(hh, 16, @dt)
	ELSE
		SET @dt = DATEADD(hh, 2, @dt)
END

INSERT INTO #tmp
SELECT @dt
*/

This part selects the results of the temp table

SELECT * FROM #tmp
GO

And this part removes the temp table

DROP TABLE #tmp
GO

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources