Setting default value when creating table

I am trying to create a table and set a default value for one of the field to 'yyyymm'
the code I am using is

qry_mmyy varchar(6) default (CONVERT(VARCHAR(4),YEAR(getdate()) + CONVERT(VARCHAR(2),MONTH(getdate())))),

Open in new window


I am getting 2017 still though instead of 201502

any ideas would be welcome

thanks
damixaAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Also, if this value will always derive from something else and never be edited, perhaps a calculated column would be better than a default, or always calculate it in whatever query uses it.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
-- This is the varchar
SELECT CONVERT(VARCHAR(4),YEAR(getdate())) + RIGHT('0' + CONVERT(VARCHAR(2),MONTH(getdate())),2)

The use of RIGHT('0'.. is required because February will evaluate to 2, and not 02, so to correct this add a zero to the left side and take the right-most two characters.

-- This is an int, assuming you'll want to do math on this value then any numeric is better
SELECT (CONVERT(VARCHAR(4),YEAR(getdate())) * 100) + CONVERT(VARCHAR(2),MONTH(getdate()))
0
 
Scott PletcherSenior DBACommented:
qry_mmyy varchar(6) default CONVERT(varchar(6), getdate(), 112)
0
 
damixaAuthor Commented:
Thanks
0
 
Scott PletcherSenior DBACommented:
Just to clarify for sure:
I assume you mean "computed column" rather than "calculated column"?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.