Default Value in a table ? - please help

[access97to2000]

Hi,
i am creating a table in SQL 2000 where i have 2 fields
login_date, login_time of types smalldatetime. How can i specify default values to both the fields??
when i am trying to give getdate() in the property pages down, its throwing an error.
please help

 

[SQLBill]

Usually defaults are created when the table is created (see CREATE TABLE in Books Online). I think your main problem might be with the data type. SMALLDATETIME is the date AND the time together. Your post looks like you are trying to break SMALLDATETIME apart and store the date separate from the time. I'm not sure you can do that and keep the SMALLDATETIME data type.

-SQLBill

 

[SQLBill]

Here's more from 'The Guru's Guide to Transact-SQL' by Ken Henderson :

Quote:

SQL Server dates come in two varieties: datetime types and smalldatetime types. There is no separate time data type--dates and times are always stored together in SQL Server data.
End Quote

Quote:
If you wish to store a date without a time, simply omit the time portion of the column or variable--it will default to 00:00:00.000 (midnight). If you need a time without a date, omit the date portion--it will default to January 1, 1900.
End quote

So, if you are using DATETIME or SMALLDATETIME types, the data will ALWAYS contain both a date and a time.

If you want to STORE only the date or only the time, you have to use a data type like VARCHAR or CHAR. Or you can retrieve the portion you want in your script.

-SQLBill

 

[access97to2000]

if i change datatype to varchat and try to print date, how can i give the default ?

 

[AndyMoss]

Why not just have one field [Login Timestamp] and separate in code as required - I do it all the time, and works fine.