Solved

Setting default value when creating table

Posted on 2015-02-04
5
73 Views
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

thanks
0
Comment
Question by:damixa
  • 2
  • 2
5 Comments
 
LVL 65

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()))
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 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.
0
 
LVL 69

Expert Comment

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

Author Closing Comment

by:damixa
ID: 40589591
Thanks
0
 
LVL 69

Expert Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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