Manually setting date and 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 2005 Forums

Transact-SQL (2005)

Manually setting date and time

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-03-26 : 10:37:46

In our place we have this ridiculous set-up where we have date and time separate. So we'll have DateCreated (e.g. 2009-03-26 00:00:00.000) and TimeCreated (e.g. 1900-01-01 14:34:26.000).

Why? I do not know but I need to manually reset a couple of fields, one DateCreated with a time set to zero and one TimeCreated with a date set to 1900-01-01.

The date one is easy enough but how do I set a time where the date must be 1900-01-01 and the timne is, say 14:34?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 10:47:36

[code]UPDATE Table1
SET DateOnly = DATEADD(DAY, DATEDIFF(DAY, 0, DateOnly), 0),
TimeOnly = DATEADD(DAY, DATEDIFF(DAY, TimeOnly, 0), TimeOnly)[/code]

E 12°55'05.63"
N 56°04'39.26"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 10:50:45

When a field is defined as datetime and you update it with just the time say '14:34' , it will get updated with the default 1900-01-01 date ...as below.

declare @t table(timeonly datetime)
insert @t
select '2009-01-01'

update @t set timeonly = '14:34'

select * from @t
will give 1900-01-01 14:34:00.000

Is this what you were looking for?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 10:51:54

With the update above written in a trigger, you can store full datetime in both columns and the trigger would truncate the necessary data for you.

CREATE TRIGGER	dbo.trgTable1DateAndTime
ON		Table1
AFTER		UPDATE,
		INSERT
AS

SET NOCOUNT ON

UPDATE		t1
SET		t1.DateOnly = DATEADD(DAY, DATEDIFF(DAY, 0, t1.DateOnly), 0),
		t1.TimeOnly = DATEADD(DAY, DATEDIFF(DAY, t1.TimeOnly, 0), t1.TimeOnly)
FROM		Table1 AS t1
INNER JOIN	inserted AS i ON i.PkKey = t1.PkKey

E 12°55'05.63"
N 56°04'39.26"

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-03-26 : 11:05:56

I actually worked it out for myself in the end and i'm pretty chuffed as to how I did it so i'll share.

I used getdate to give me the currentdate and time
then i used DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) which gives me zero hours, minutes etc for the supplied date and then i did a DATEDIFF on seconds between the two.
Then having worked out the seconds between midnight last night and now, I DATEADDed that in seconds to {d '1900-01-01'} to give me what I wanted.

Thanks for the help anyway but it feels much better to have ploughed through it myself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources