Storing Time in SQL Server 2000

[m3ckon]

HI there,

I currently store the date using the getdate() function

but how can I store just the time or seperate the time off from a
datetime datatype?

M3ckon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

SQLServer doesn't have separate DATE and TIME datatypes so store the date
and time together. If the date isn't relevant then just store some dummy
date with the time - if you don't specify the date when you insert a time as
a formatted string then it will default to 1900-01-01.

Separating out the time for display purposes may be best done in your
application but you can also do it in SQL using the CONVERT function. For
example:

CONVERT(CHAR(8),CURRENT_TIMESTAMP,8)

--
David Portas
SQL Server MVP
--

[--CELKO--]

>>>I currently store the date using the getdate() function <<

1) Now that you can, start to use the Standard SQL CURRENT_TIMESTAMP
instead of that old Sybase proprietary function.

2) I think that you conceptual model of time is wrong. You are not
storing just the date, an instant in time so small it does not exist,
but the duration of 24 hours that began at midnight. Time is a
continuum and continuums do not have discrete points.

Look Zeno's Paradoxes (The Arrow is the best one for this) and
Einstein's physics.

but how can I store just the time or seperate the time off from a

datetime datatype? <<

Look at the temporal library routines for help. Force everything to
one particular date and extract the time part. This is a kludge, of
course. The real question is what does that point in time mean in
your data model without reference to a date which is a duration in the
continuum? Read some of Rick Snodgrass's books and paper on the
subject.

[m3ckon]

I just wanted to split them up for display purposes ... but Isuppose
that can be done on the display tier of the app
good point though

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

m3ckon (an*******@devdex.com) writes:

I just wanted to split them up for display purposes ... but Isuppose
that can be done on the display tier of the app

Yes, that is the better place to do it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp