• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

return month and day from a date

I want to return month and date to look like this

02/17

What would the syntax be?

So how would i modify

SELECT GetDate() to return what i need?
0
vbnetcoder
Asked:
vbnetcoder
1 Solution
 
Eugene ZCommented:
SELECT GetDate() ,convert(varchar(5),getdate(),101)
more
BOL
https://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
This works on my 2012 box
Declare @dt date = '2016-02-17'
SELECT FORMAT(@dt, 'MM/dd')

Open in new window

Keep in mind this returns a varchar and not a date data type.

If you're running 2008 and FORMAT is not available, you may have to do this (not tested as I don't have a 2008 box handy)
Declare @dt date = '2016-02-17'
SELECT RIGHT('0' + CAST(MONTH(@dt) as varchar(2)), 2)  + '/' + RIGHT('0' + CAST(DAY(@dt) as varchar(2)),2) 

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
SELECT RIGHT(CONVERT(VARCHAR, GETDATE(), 10),5)

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
vbnetcoderAuthor Commented:
On other option is to make the date the first day

So, I found this syntax that should work.  Do you think there is a better way of doing this?

   SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()), 0) AS [year_month_date_field]
0
 
Eugene ZCommented:
you Q was "I want to return month and date to look like this:
the above post will give what you need
if you have 2nd Q " get date the first day of this Month"
the one of options
you code is the one of the most populars   SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()), 0)
0
 
vbnetcoderAuthor Commented:
ty
0
 
Eugene ZCommented:
you can try if you'r on sql 2012
SELECT DATEADD(DAY,1,EOMONTH(GETDATE(),-1))
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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