Setting default value when creating table

Posted on 2015-02-04
Medium Priority
Last Modified: 2015-02-04
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

Question by:damixa
  • 2
  • 2
LVL 66

Expert Comment

by:Jim Horn
ID: 40589563
-- 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()))
LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 40589566
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.
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40589588
qry_mmyy varchar(6) default CONVERT(varchar(6), getdate(), 112)

Author Closing Comment

ID: 40589591
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40589597
Just to clarify for sure:
I assume you mean "computed column" rather than "calculated column"?

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question