[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3990
  • Last Modified:

Generating a leading zero in DatePart(mm, a_date)

Hi all

SELECT CAST(DATEPART(yyyy, '03/04/2004') as char(4)) + CAST(DATEPART(mm, '03/04/2004') as char(2))
returns 20043

I am trying to edit this to return 200403, and it's not abundantly clear how to pull this off.  

Thanks.
-Jim
0
Jim Horn
Asked:
Jim Horn
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT CAST(DATEPART(yyyy, '03/04/2004') as char(4)) + RIGHT('00'+CAST(DATEPART(mm, '03/04/2004') as char(2)),2)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
aneeshattingal - No love.  Still showed 20043 when run in SQL2K Query Analyzer.  -Jim
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT CAST(DATEPART(yyyy, '03/04/2004') as char(4)) + RIGHT('00'+CAST(DATEPART(mm, '03/04/2004') as varchar(2)),2)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
need to use varchar(2)  instead of char(2)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
That worked.  Thank you.  -Jim
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now