date part - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

date part

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-12-29 : 07:53:59

hi,

i have a following code that i need to convert into datetime format:


DECLARE @temp table (Ddate varchar(20))
insert into @temp 
select '3.11.2009 14:55:53' union  
select '12.11.2009 22:39:49' union 
select '12.9.2009 22:39:49' union 
select '2.3.2009 22:39:49'

select 
Ddate
,cast(SUBSTRING(Ddate, 1,CHARINDEX('.', Ddate) - 1) as int) as [day]
,replace(substring(Ddate, charindex('.',Ddate)+1, len(charindex('.',Ddate,charindex('.',Ddate)))+1),'.','') as [month]

from @temp

I need to break down also year.
is there a faster way to do this?

thank you in advance.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-12-29 : 08:00:26

fastest solution probably:


DECLARE @temp table (Ddate varchar(20))
insert into @temp 
select '3.11.2009 14:55:53' union  
select '12.11.2009 22:39:49' union 
select '12.9.2009 22:39:49' union 
select '2.3.2009 22:39:49'

select 
Ddate
,parsename(Ddate,3) as D
,parsename(Ddate,2) as M
,left(parsename(Ddate,1),4) as Y
from @temp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-29 : 08:47:46

or this

set dateformat dmy
DECLARE @temp table (Ddate varchar(20))
insert into @temp
select '3.11.2009 14:55:53' union
select '12.11.2009 22:39:49' union
select '12.9.2009 22:39:49' union
select '2.3.2009 22:39:49'

select actual_date,day(actual_date) as D,month(actual_date) as M,year(actual_date) as Y
from
(
select cast(Ddate as datetime) as actual_date from @temp
) as t

set dateformat mdy

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources