Extracting Only Time from GETUTCDATE() Function

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)

Extracting Only Time from GETUTCDATE() Function

Author

Topic

cant2ny
Starting Member

20 Posts

Posted - 2009-07-09 : 16:13:28

Hey All,

I'm new to SQL so I apoligize in advance if this is a basic question. I'm trying to extract just the time from the GetDate() and GetUTCdate() to run a convert present UTC dates to EST.

My query for this sections looks like:

Select
@UtcTime = convert(varchar(10), getUTCdate(), 108),
@CurrentTime = convert(varchar(10), getdate(), 108)

However, it's outputting as:
1900-01-01 02:40:00.000

How do I get rid of the 1900-01-01 since it's not needed?

Thanks in advance for any help.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-09 : 16:20:43

Have you defined @UtcTime and @CurrentTime as datetime fields?

If yes, change them to varchar fields

cant2ny
Starting Member

20 Posts

Posted - 2009-07-09 : 16:29:07

Thank you vijayisonly

Unfortuently, changing it to varchar or nvarchar will not work. SQL throws this error message:

Operand data type varchar is invalid for subtract operator.

Currently, the inital dates are being pulled off a .CSV file that is imported into a SQL table. The inital field also has the date and UTC time, which is seperated via string searchs. The UTC time is imported as a nvarchar variable. Since it's only present dates that need converted, adding the current time and subtracting the UTC time gives the correct date while taking into account Daylight Savings. My query looks like this and the subtration is throwing an error message:

CONVERT (nvarchar(5),
CAST(Substring(CreatedDate,charindex('T', CreatedDate)+1, 5) as char(5)), 114)
+ @CurrentTime - @UtcTime,

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-09 : 16:33:36

This script converts a SQL Server datetime value to a time only datetime value with the time as an offset from 1900-01-01 00:00:00.000.

By convention, time only is usually stored this way in SQL Server. The reason for this is that the time can be added to a datetime value containing the date only, and produce the original date and time.


select
	DT,
	TimeOnly = a.DT-dateadd(dd,datediff(dd,0,a.DT),0)
from
	( -- Test Data
	select DT = getdate()	union all
	select DT = getutcdate()
	) a

select UTC_Diff = getutcdate()-getdate()



Results:
DT                        TimeOnly
------------------------- ------------------------
2009-07-09 16:27:42.643   1900-01-01 16:27:42.643
2009-07-09 20:27:42.640   1900-01-01 20:27:42.640

(2 row(s) affected)

UTC_Diff
-------------------------
1900-01-01 03:59:59.997

(1 row(s) affected)

More info here:
Time Only Function: F_TIME_FROM_DATETIME
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358

CODO ERGO SUM

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-09 : 23:45:01

SELECT SUBSTRING(CONVERT(varchar,(getUTCdate())), 12, 13)
SELECT SUBSTRING(CONVERT(varchar,(getdate())), 12, 13)

Hope can help...but advise to wait pros with confirmation...

cant2ny
Starting Member

20 Posts

Posted - 2009-07-10 : 13:59:25

Thanks for the responses - using both waterducks and michael's it was able to be extracted. Simple query that ended up being more string trimming then anything else. Final results was this:

Right(Cast(Cast(Substring(CreatedDate,charindex('T', CreatedDate)+1, 5) as datetime) - Cast(@CurrentDateDiff as datetime) as varchar(20)),7)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources