SQL Server 2000 database GETDATE() default for field

[ssailer]

I have a SQL Server 2000 database where the createDate field has a default value of GETDATE() and the data type is datetime. It is inserting the date just fine, but I do not want to capture the time along with the date. I only want the date to be entered. How can I accomplish this? Thanks!

[-__cfSearching__-]

Use the convert function to truncate the time from getdate()

[paross1]

Something along the lines of this.....

CAST(CONVERT(VARCHAR, GETDATE(), 101) AS datetime)

Phil

[ssailer]

CAST(CONVERT(VARCHAR, GETDATE(), 101) AS datetime)

This would be done in an underlying view or query, right?

[-__cfSearching__-]

It could be done in a query or in the column "default" statement. The choice is yours.

[ssailer]

never mind - I put it directly into the default value of the field and it's working. This has been a problem for days - thank you!

[paross1]

Don't forget, since your column is still type datetime, a "date" stripped of its time component is essentially midnight (time 00:00:00, etc.)

Phil

[-__cfSearching__-]

Added to paross1's comments, I would suggest using a less ambiguous date format and/or a double convert. To guarantee the date string is converted properly.

convert(datetime, convert(varchar, getDate(), 102), 102)

[ssailer]

would you enter this directly into the default value for the field?

convert(datetime, convert(varchar, getDate(), 102), 102)

[-__cfSearching__-]

IF you only wanted to store the date, then yes you would use that as the column default value.

...
createDate datetime default convert(datetime, convert(varchar, getDate(), 102), 102)

However, as paross1 cautioned be sure you understand you will only be capturing the date from now on. If you change your mind later, and decide you need both the created date and time, you will be seriously out of luck. A safer choice might be to keep the date and time, and use convert in your queries instead.

[Dan_Bracuk]

If you make it the default, you don't have to enter anything.