return month and day from a date

Posted on 2016-10-07
Medium Priority
Last Modified: 2016-10-07
I want to return month and date to look like this


What would the syntax be?

So how would i modify

SELECT GetDate() to return what i need?
Question by:vbnetcoder
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
LVL 43

Expert Comment

by:Eugene Z
ID: 41833787
SELECT GetDate() ,convert(varchar(5),getdate(),101)
LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 41833790
This works on my 2012 box
Declare @dt date = '2016-02-17'

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

LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41833791
Try this:

Open in new window

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!


Author Comment

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]
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)

Author Closing Comment

ID: 41833807
LVL 43

Expert Comment

by:Eugene Z
ID: 41833811
you can try if you'r on sql 2012

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

762 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