data types default values

Question

Post reply

data types default values

Malka W

SSC Enthusiast

Points: 123
More actions
September 12, 2006 at 1:51 am

#170342

I'm trying to set a default value of date to a datetime / smalldatetime data type in an existing sql table. I've searched for valid default values for this but those I've tried always give me the following message:
Error validating the default for colomn 'date'.
Do you want to edit the default?
I've tried using values like: now, now(), CURRENT, CURRENT() but all give me the same message. I thought changing the colomn name will help, but it doesn't.
Where can I find a list of default values for the data types, and what default values can I use specifically for DATETIME / SMALLDATETIME
Thank you,
M

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76000 More actions · Answer 1

CREATE DEFAULT df_now AS GETDATE()

CREATE DEFAULT df_today AS CONVERT(SMALLDATETIME,CONVERT(CHAR(10),GETDATE(),102),102)

CREATE TYPE dbo.ty_now FROM DATETIME NOT NULL

CREATE TYPE dbo.ty_today FROM SMALLDATETIME NOT NULL

exec sp_bindefault 'df_now','ty_now'

exec sp_bindefault 'df_today','ty_today'

LinkedIn Profile

Malka W SSC Enthusiast Points: 123 More actions · Answer 2

Thank you!

May I trouble you again and ask what default values I can use for TIME?

I have two columns, one for the current date and the other for the current time.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

CONVERT with option 108.

N 56°04'39.16"
E 12°55'05.25"

Malka W SSC Enthusiast Points: 123 More actions · Answer 4

Thank you

Is there a list of available default values for every data type, which I can look up? And also the convert function options

M

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 5

SQL BOL(Books Online) has everything u have asked for.

Thanks

Sreejith

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Why two columns? Tis a much better design to have a single column that stores the current datetime.

Getdate retuns the current date and time to an accuracy of 3 ms. Unliks MS Access, there's no built-in function that returns just the date.

If you want to do range queries, or date arithmetic, it's much easier if you have the date and time in a single column. You can always seperate tehm at the presentation layer, if the user wants to see them seperatly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

filthy Grasshopper Points: 11 More actions · Answer 7

thats a great resource... is there some sort of url that would actually be of some use? or is it just any sql books online?

thanks-

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

You want just the date?

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

N 56°04'39.16"
E 12°55'05.25"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Books Online is the title of the help files that come with SQL. They are also available on msdn, but I don't remember the url

Look in the SQL Server group in your start menu.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass