Date & Time Field

Question

Date & Time Field

Dhaval-190627

Old Hand

Points: 323
More actions
November 30, 2004 at 8:28 am

#163935

I am transfering one of my Access 2000 application into sql 2000. I have one table in access which contain two field one store one date (11/27/2004) formate and other store only Time (10:00 AM) formate. I am transfering all table from access to SQL and i am having proble to create field with datatype which can only store Time in above formate.
Can any body help me out to suggest me to use right datatype so it can store only Time insted of full date and time.

Viewing 5 posts - 1 through 4 (of 4 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: 76035 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.