datetime = '12:00:00 AM'

[jcfowl]

All the programmers agreed to use a value of '12:00:00 AM' for sentinal values on DateTime columns for our database. The problem is that when we INSERT or UPDATE '12:00:00 AM' into the column it is stored as '1/1/1900'. Does anyone know a way to store '12:00:00 AM' in a DateTime column?

 

[SQLBill]

DATETIME datatype is DATE AND TIME. You can not use DATETIME datatype to store only the date or only the time. SQL Server will add the default part if you leave it out.

Refer to FAQ183-5834 and FAQ183-5842 for more information about using dates and times in SQL Server.

-SQLBill

Posting advice: FAQ481-4875

 

[Rhys666]

Nope, it's a DateTime datatype, not a time datatype so always has both the date and time elements associated with it. Have you experimented with user defined datatypes or accepting a default date value which can be eliminated when querying the database by utilising the DATEPART function just to get the time?

Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home

 

[Pattycake245]

You cannot just store the time in as datetime field. The reason why SQL adds on the 1/1/1900 is that this is the default when no date is given. If you only want to store the time into a table, you need to store it as a char or varchar field.

Tim