CODEWITHSUNDEEP
sqlsql-server-2008
SaNa3819
SaNa3819

Reputation: 347

how to separate date?

In a database

  Date

 5/29/2013
 12/4/2013
 12/1/2014

The output will be like this

 Year   Month  date
 2013    5      29
 2013    12      4
 2014    12      1

I tried this code: 

select [Date],
right([Date],4)  as year ,
left([Date],CHARINDEX('/',[Date])-1) as month ,
substring([Date],3,2) as date  
FROM Table1

but it not showed the result properly.

Upvotes: 0

Views: 67

Answers (7)

Mukund
Mukund

Reputation: 1689

DECLARE @datevar varchar(50) = '10/10/2014'
declare @date datetime = convert(date,@datevar,101)
SELECT datepart(year, @date) AS 'year'
    ,DATEPART(month, @date) AS 'Month'
    ,DATEPART(day, @date) AS 'Day'

you can use this also.

Upvotes: 1

Mohit
Mohit

Reputation: 11314

Why are you doing so Just do as written below 

SELECT CONVERT(DATE,[date]) ,
YEAR = DATEPART(YEAR , CONVERT(DATETIME, [date])),
MONTH = DATEPART(MONTH , CONVERT(DATETIME, [date])),
DAY = DATEPART(DAY, CONVERT(DATETIME, [date]))
FROM DateTable

You can also use other formats if your data is not in this format

Upvotes: 0

Narayan Prasad Twayana
Narayan Prasad Twayana

Reputation: 1

Please try this too :

select datepart(year,[Date]) [year], 
       datepart(month,[Date]) [month],
       datepart(day,[Date]) [day]
  FROM table1

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10264

As Date is a varchar column, first convert it to DateTime and then do the manipulations as :

SET DATEFORMAT MDY;

select [Date],
       cast ([Date] as Datetime),
       Year(cast ([Date] as Datetime)) [Year],
       Month(cast ([Date] as Datetime)) [Month],
       Day(cast ([Date] as Datetime)) [Date]
FROM Table1

DEMO

Upvotes: 0

user262503
user262503

Reputation: 103

create table #t(id varchar(10))
insert into #t values('5/29/2013'),
 ('12/4/2013'),
 ('12/1/2014')

 select year(id) as [year],month(id) as [month],day(id) as [day] from #t

Upvotes: 0

user4094161
user4094161

Reputation:

Try this:

SELECT DATEPART(yyyy,date) AS Year,
DATEPART(mm,date) AS Month,
DATEPART(dd,date) AS Day,
FROM Table1

http://www.w3schools.com/sql/func_datepart.asp

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try:

select 
    Year(CONVERT(Datetime, [Date])) [Year],
    Month(CONVERT(Datetime, [Date])) [Month],
    Day(CONVERT(Datetime, [Date])) [Date]
FROM Table1

Upvotes: 0

Related Questions