HOW DO I DEFAULT DATETIME FIELD TO '12:00AM'

[SeanB]

Thanks I am stuck here I hate the 1/1/1900 problem. Also if you have any advice on how to handle datetime that would be helpful. Thanks

 

[foxdev]

SeanB, you're not giving us much to work with. You haven't told us in what context you are trying to do this (a stored procedure, ADO from VB or IIS, Query Analyzer, ODBC, etc).

Generally, this:

[tt]declare @MyDateVar datetime
set @MyDateVar = '07/26/2001' [/tt]

will set the time portion to 00:00, or 12:00 AM. Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause

 

[elmo29]

I have in the past used

declare @date datetime

set @date = convert(nvarchar,getdate(),102)

to get the current date excluding time.

 

[foxdev]

I hate the 1/1/1900 problem

SeanB, again I'm just guessing, but do you mean that you only want to store the time without the date? Is this what you mean by the "1/1/1900 problem"? If so, in SQL Server a datetime or smalldatetime type value always has a date component; there's no way around that. You could use another data type, or you could choose to ignore the date portion of the value. You can even use DatePart() to retrieve only portions of the value. Robert Bradley
Support operation moo!
Visit the OpCow page

http://www.agrainofhope.org/opcow.shtml

to help in this cause

 

[tlbroadbent]

You can also use the Convert function to return only the time portion of the field.

Select convert(char(8), datetimecolumn, 108) As TimeOnly
From tbl

Note 1: 108 represents a datetime time of hh:mm:ss.

Note 2: Don't get hung up on the way SQL Server stores dates and times. The storage format is fixed. Learn to use the datetime functions and Convert to display the data the way you want and you'll eliminate a lot of frustration and headaches associated with dates and times in SQL Server. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin