Solved

return month and day from a date

Posted on 2016-10-07
7
41 Views
Last Modified: 2016-10-07
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
Comment
Question by:vbnetcoder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 41833787
SELECT GetDate() ,convert(varchar(5),getdate(),101)
more
BOL
https://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41833790
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41833791
Try this:
SELECT RIGHT(CONVERT(VARCHAR, GETDATE(), 10),5)

Open in new window

0
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.

 

Author Comment

by:vbnetcoder
ID: 41833794
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 41833804
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
 

Author Closing Comment

by:vbnetcoder
ID: 41833807
ty
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 41833811
you can try if you'r on sql 2012
SELECT DATEADD(DAY,1,EOMONTH(GETDATE(),-1))
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

707 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