Solved

Date format in sql

Posted on 2015-02-17
11
428 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
Comment Utility
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
Comment Utility
Hi Anthony,
My string is 2/01/2015  not 20150201'.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 75

Expert Comment

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

Author Comment

by:VBdotnet2005
Comment Utility
It is varchar(12).
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:VBdotnet2005
Comment Utility
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
Comment Utility
Can we return 01 for date and month 02?
0
 

Author Comment

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

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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:
ScottPletcher earned 500 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now