date and time assignment - 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

General SQL Server Forums

New to SQL Server Programming

date and time assignment

Author

Topic

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-10-21 : 10:00:41

hello all,
i have data in the database that contains data about date and time of certain events in nvarchar data type in a deferent format. lets say i am trying to represent 1/10/2009 12:30:33AM the date field will be 011009(october first 2009) and the time field will be 123033(24 hour time format) i want to copy the data from 1 table to the other assigning each value to its correct format and putting in a single datetime column..i cant seem to find a way to do that in sql ..i can do it in a vb function but it would extremely slow the process

thanks in advance for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 10:06:35

[code]
select convert(datetime,
stuff(stuff('011009', 3, 0, '/'), 6, 0, '/')
+ ' '
+ stuff(stuff('123033', 3, 0, ':'), 6, 0, ':'), 3)
[/code]

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

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-10-21 : 10:35:27

ok thanks khtan it worked like a charm ..i have one more problem the time here is in utc time ..i want it to get the local time by getting the local time offset thanks in advance man

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 11:56:36

quote:
Originally posted by bobz_0585

ok thanks khtan it worked like a charm ..i have one more problem the time here is in utc time ..i want it to get the local time by getting the local time offset thanks in advance man

just use like

DATEADD(ms,DATEDIFF(ms,GETUTCDATE(),GETDATE()),GETUTCDATE())

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-21 : 12:00:03

You can use date math or you can get the offset from SysDateTimeOffset:

SELECT DATEDIFF(HOUR, GETUTCDATE(), GETDATE())
SELECT DATEDIFF(HOUR, SYSUTCDATETIME(), SYSDATETIME())
SELECT SYSDATETIMEOFFSET()

EDIT: Added the SYS date times

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-10-22 : 02:15:43

thanks man it worked perfectly ..:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources