Date & Time Field

Question

Date & Time Field

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

Not possible in SQL. You could combine the date and time into one field in SQL. You could dummy the date portion of the field and have the correct time. Lastly, you can wait for SQL2005 and it is supposed to have these separated.

Michelle

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

When you use a DATETIME datatype in SQL Server, it will ALWAYS store a DATE and a TIME portion. Consider this:

SELECT

CAST('10:00:00' AS DATETIME)

, CAST('20041030' AS DATETIME)

------------------------------------------------------ ------------------------

1900-01-01 10:00:00.000 2004-10-30 00:00:00.000

(1 row(s) affected)

You can wait for SQL Server 2005, or have again two columns. but I think, if it is properly handled, the above works pretty well.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 76262 More actions · Answer 3

If you don't want to go down to the second you could try SMALLDATETIME which has half the storage requirements.

The strange thing is that both types store two numbers, in both cases the first number stores the number of days since Jan 1 1900.

The 2nd number is

For DATETIME the number of milliseconds since midnight
For SMALLDATETIME the number of minutes since midnight

The data structure is already there for separate date/time fields just the API to reference them separately.

LinkedIn Profile

lakusha SSCommitted Points: 1857 More actions · Answer 4

> Lastly, you can wait for SQL2005 and

> it is supposed to have these separated.

AFAIK, the Date & Time datatypes won't be supported in 2005. They were dropped from the beta2 feature list because of problems with existing datetime functions.