Solved

return month and day from a date

Posted on 2016-10-07
7
37 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
7 Comments
 
LVL 42

Expert Comment

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

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 48

Expert Comment

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

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 42

Expert Comment

by:EugeneZ
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 42

Expert Comment

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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Split data on commas and insert into another table in separate records 26 70
Connect to an SQL server ActiveX 10 24
SQL Server / Update DB? 22 36
Database Integrity 1 48
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

840 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