Datetime need only time value - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Datetime need only time value

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2010-12-29 : 03:05:46

Hi,
the below query will give result 40539.4599427
I need only the values after decimal (4599427)

select CONVERT(NUMERIC(18,7),getdate())
result:40539.4599427

Thanks.

No paiN No gaiN

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 03:11:00

select CONVERT(NUMERIC(18,7),getdate()) - CONVERT(int,getdate())

multiply by 10000000 and convert if you want an integer.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

amodi
Yak Posting Veteran

83 Posts

Posted - 2010-12-29 : 03:19:04

Dear,
Thanks for u r reply.
i am looking for some alternate solution without multiplying by 10000000
is there any Sql funtion ?
Appriciate your help.

No paiN No gaiN

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 03:28:28

The datetime is held as 2 binary numbers
You can get the time part via
select convert(binary(4),getdate())

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-29 : 03:37:48

Maybe the value you want is
SELECT 1.0*convert(int,convert(binary(4),getdate()))/CONVERT(INT,0x00000000018B8200)

which is the same as
SELECT 1.0*convert(int,convert(binary(4),getdate()))/(24*60*60*300)
i.e. the binary number divides a day into portions of 24*60*60*300 i.e. about 3 ms.
The decimal value you want is the binary number divided by the number of portions in a day.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-29 : 06:07:33

select parsename(CONVERT(NUMERIC(18,7),getdate()),1)

Madhivanan

Failing to plan is Planning to fail

amodi
Yak Posting Veteran

83 Posts

Posted - 2011-01-02 : 06:55:59

Thank's Madhivanan

quote:
Originally posted by madhivanan

select parsename(CONVERT(NUMERIC(18,7),getdate()),1)

Madhivanan

Failing to plan is Planning to fail

No paiN No gaiN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 04:15:45

quote:
Originally posted by amodi

Thank's Madhivanan

quote:
Originally posted by madhivanan

select parsename(CONVERT(NUMERIC(18,7),getdate()),1)

Madhivanan

Failing to plan is Planning to fail

No paiN No gaiN

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources