Date time function - 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 2000 Forums

Transact-SQL (2000)

Date time function

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-05 : 14:00:46

Hi
i have a time i need to store in the db.but its in this format
"HH:MM:SS.NNNN"
Please help
Regards
Nitin

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-05 : 14:24:13

You can convert that time into datetime format, as long as the number of decimal places is only 3:

declare @time varchar(50)
set @time = '13:33:23.132'

SELECT CONVERT(datetime, @time)

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-05 : 14:28:31

i want to insert the date in the table in this format "HH:MM:SS.NNNN"
,which datatype should i use for this and how?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-05 : 15:49:09

It depends on what you want to use this data for.

For Datatime datatypes Sql only reports milliseconds to 3 places (even that is not true thousands) so if you need 4 place precision you'll need to get creative, possibly store the components in individual columns. If you don't mind the data being completely useless in terms of sorting, calculating etc, you can just keep it in a varchar column "as is".

Will you want to use it to calculate durations or total hours or anything besides "looking at" individual formatted values? Will you need to sort by these value?

Will these times be associated with dates?

EDIT:
in case anyone doesn't know what I mean by "(even that is not true thousands)" check this out:


select convert(datetime, dt)
from	(
	select '1/1/2006 11:59:59.996' dt union
	select '1/1/2006 11:59:59.997' union
	select '1/1/2006 11:59:59.998' union
	select '1/1/2006 11:59:59.999' union
	select '1/1/2006 12:00:00.000'
	) a

output:
2006-01-01 11:59:59.997
2006-01-01 11:59:59.997
2006-01-01 11:59:59.997
2006-01-01 12:00:00.000
2006-01-01 12:00:00.000

Be One with the Optimizer
TG

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-04-05 : 16:07:29

Hi TG
thanks a lot for the reply.thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources