Solved

Date format in sql

Posted on 2015-02-17
11
444 Views
Last Modified: 2015-03-09
How can I return 2/01/2015 to below string format varchar (please) in sql?

2/01/2015 to 02/01/2015
2/01/2015 to get date only 01
2/01/2015 to get month only 02


SELECT DATEPART(d, '2/01/2015')   return 1
select DATEPART(m, '2/01/2015')    return 2
0
Comment
Question by:VBdotnet2005
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40615659
This is a lot easier if you are using SQL Server 2012...

However, since you cannot use the FORMAT function, your only recourse is CONVERT as in:
DECLARE @YourDate date = '20150201'
SELECT CONVERT(varchar(10), @YourDate, 101)
SELECT SUBSTRING(CONVERT(varchar(10), @YourDate, 101), 4, 2)
SELECT SUBSTRING(CONVERT(varchar(10), @YourDate, 101), 1, 2)
0
 

Author Comment

by:VBdotnet2005
ID: 40615664
Hi Anthony,
My string is 2/01/2015  not 20150201'.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40615669
This is how you could do it with SQL Server 2012:
DECLARE @YourDate date = '20150201'
SELECT FORMAT(@YourDate, 'MM/dd/yyyy')
SELECT FORMAT(@YourDate, 'dd')
SELECT FORMAT(@YourDate, 'MM')
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40615672
My string is 2/01/2015  not 20150201'.
What data type is it exactly in SQL Server?
0
 

Author Comment

by:VBdotnet2005
ID: 40615683
It is varchar(12).
0
 

Author Comment

by:VBdotnet2005
ID: 40615691
We have sql2008.

SELECT CONVERT(varchar(10), '2/01/2015'      , 101)
2/01/2015
SELECT SUBSTRING(CONVERT(varchar(10), '2/01/2015', 101), 4, 2)
1/
SELECT SUBSTRING(CONVERT(varchar(10), '2/01/2015', 101), 1, 2)
2/
0
 

Author Comment

by:VBdotnet2005
ID: 40615693
Can we return 01 for date and month 02?
0
 

Author Comment

by:VBdotnet2005
ID: 40615694
Also date should be 02/01/2015 format.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40615713
Try it this way:
DECLARE @YourVarcharDate varchar(12) = '2/01/2015'         
DECLARE @YourDate date = CONVERT(date, @YourVarcharDate, 101)
SELECT CONVERT(varchar(10), @YourDate, 101)
SELECT SUBSTRING(CONVERT(varchar(10), @YourDate, 101), 4, 2)
SELECT SUBSTRING(CONVERT(varchar(10), @YourDate, 101), 1, 2)

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40615731
I would like to point out that a string is always a string. Sounds simple enough until you see this:

'2/01/2015'

because it looks like a date, but it remains a string (note the single quotes!) and it is NOT a date unless it is converted.

To do what you want, it needs to become a date first and THEN you can use date functions such as DATEPART()

nb: In the database date/datetime/time information is NOT stored in a human readable manner but stored as sets of numbers.

so in addition to the comment above
DECLARE @YourVarcharDate varchar(12) = '2/01/2015'         
DECLARE @YourDate date = CONVERT(date, @YourVarcharDate, 101)
SELECT CONVERT(varchar(10), @YourDate, 101)
SELECT SUBSTRING(CONVERT(varchar(10), @YourDate, 101), 4, 2)
SELECT SUBSTRING(CONVERT(varchar(10), @YourDate, 101), 1, 2)

-- now date functions will work too

select datepart(month,@YourDate)
select datepart(year,@YourDate)
select datename(weekday,@YourDate)

Open in new window


no points please
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40616916
SELECT convert(varchar(2), cast('2/01/2015' as datetime), 101) as month,
    convert(varchar(2), cast('2/01/2015' as datetime), 103) as day

SELECT convert(varchar(2), input_date, 101) as month,
     convert(varchar(2), input_date, 103) as day
FROM (
    select cast('2/01/2015' as datetime) as input_date
) AS test_data
0

Featured Post

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.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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 date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

860 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