Learn how to a build a cloud-first strategyRegister Now


Month(GetDate()) to return '04' instead of 4

Posted on 2006-04-20
Medium Priority
Last Modified: 2008-03-06
Hi all

I have the following SP line in progress...

declare @time_period_id_current int
set @time_period_id_current = CAST(CAST(YEAR(GETDATE()) as char(4))+ '' + CAST(MONTH(GETDATE()) as char(4))as int)

Where I'm trying to convert the current year and month into and integer with YYYYMM format, i.e. 200604.  The above line returns 20064.

How to change the above line to retain the leading '0' in the month?

Thanks in advance.
Question by:Jim Horn
  • 3
LVL 75

Accepted Solution

Aneesh Retnakaran earned 920 total points
ID: 16499588
select RIGHT('00'+CAST(MONTH(GETDATE()) as varchar(2)),2)
LVL 38

Expert Comment

by:Jim P.
ID: 16499592
Right('00' + CAST(MONTH(GETDATE()) as char(4))as int) ,2)
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 80 total points
ID: 16499713
anees is correct. But yyou can get away without the sizing requirement.

select RIGHT('00'+CAST(MONTH(GETDATE()) as varchar),2)
LVL 66

Author Comment

by:Jim Horn
ID: 16499968
Thanks guys.  -Jim
LVL 38

Expert Comment

by:Jim P.
ID: 16499982
Glad to be of assistance. May all your days get brighter and brighter.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

810 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