2 replies to this topic

[Skylark320]

Am quite new to SQL Server and the like and coming across an issue.
Basically, i have created a table for which one of the columns i wish to be the date in dd/mm/yy format. As i understand, that can be achieved via CONVERT(Varchar(10),GetDate(),108). However, how do i script it so that this is entered automatically into each row every time a new row is added.

I am happy with
CurDate Date Not Null DEFAULT GETDATE()
however as you can imagine i get the long version with milliseconds and all, have tried

CurDate Date Not Null DEFAULT CONVERT(Varchar(8), GETDATE(),108) but to no avail.

Thanks in advance!

Ben (Skylark320)

[WingedPanther]

You would do better to NOT store your date as a varchar. Store it as a Date or DateTime field, and format it in the application that uses it.

The reason I recommend this is simply that I've seen what happens when you use a varchar. Somebody puts in data in mm/dd/yy format, or yy/mm/dd format, etc, and you end up with invalid data.

[LuthfiHakim]

Agree with WingedPanther, It's never hurt to only show the date part in the front-end. However if you insist to store only the date part, then what you have to remember is that date/datetime values actually are decimal/float values. The integer part defining the date and the fraction part defining the time. So you only have to remove the fraction part, using FLOOR() function, then use CAST() to get the wanted data type. Like this (not tested for field's default value though):

CurDate Date NOT NULL DEFAULT CAST(FLOOR(GETDATE()) as datetime)

Cheers!