defaut

Question

defaut

weston_086

SSCommitted

Points: 1645
More actions
March 15, 2012 at 2:17 pm

#253066

i have one column with int data type .and i want default values for that 12/31/2099.how i can do that with int datatype of that column

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

well, you should store dates in datetime columns. you end up wasting a lot of time converting int values to dates and back again when it is not necessary.

anyway, of you convert that date to int, i think you get '73048

--4098273048

select

convert(int,getdate()),

convert(int,convert(datetime,'12/31/2099'))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

weston_086 SSCommitted Points: 1645 More actions · Answer 2

Is this possible that i can have in this format 12/31/2099 .if we will do like

ALTER TABLE [dbo].[test] ADD DEFAULT ((convert(int,convert(datetime,'12/31/2099')))) FOR [id]. it will set default 73048 after converting that into integer.but i want default value like 12/31/2099 in integer column

Lowell SSC Guru Points: 323482 More actions · Answer 3

weston_086 (3/15/2012)
Is this possible that i can have in this format 12/31/2099 .if we will do like
ALTER TABLE [dbo].[test] ADD DEFAULT ((convert(int,convert(datetime,'12/31/2099')))) FOR [id]. it will set default 73048 after converting that into integer.but i want default value like 12/31/2099 in integer column

well obviously an int column cannot have slashes, so that's going to stop you with that initial desire.

you could store the date as YYYYMMDD, 20991231 for example, but yuck. you go right back to having to convert the value based on division of the parts.

so the right thing to do is to convert that column to datetime, as i alluded to before. that way, the date you want really IS the date you want, no conversions necessary.

I'd stronly suggest avodiing converting to varchar, int, or any other datatype.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!