convert varchar to datetime - 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

SQL Server 2000 Forums

Transact-SQL (2000)

convert varchar to datetime

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-31 : 05:44:15

I did found something like this here in this forum...
declare @t nvarchar(20)
set @t='20070423142807'
select month( cast(stuff(stuff(stuff(@t,9,0,' '),12,0,':'), 15,0,':') as datetime))

which is working perfectly...
Now if i want to convert a varchar type to datetime for which i did this..
declare @n varchar
set @n='12121985'
select cast(stuff(stuff(@n,3,0,' '),6,0,':') as datetime)

which is giving null...
What wrong am i doing ??
do help...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 05:47:28

You are declaring the @n variable with 1 character only.

declare @n varchar(20)
set @n='12121985'
select cast(stuff(stuff(@n,3,0,'-'),6,0,'-') as datetime)

Also, using " " and ":" does not work with dates.
Use "-".

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 05:50:18

Or explicit cast your date, since SQL Server does not know if the date should be treated as DMY or MDY formatted date text.

declare @n varchar(20)
set @n='10121985'

select	@n AS original,
	cast(RIGHT(@n ,4) + substring(@n, 3, 2) + substring(@n, 1, 2) as datetime) AS dmy,
	cast(RIGHT(@n ,4) + substring(@n, 1, 2) + substring(@n, 3, 2) as datetime) AS mdy

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-31 : 06:05:33

1 http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx
2 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-31 : 06:06:57

thnks peso for ur helpful reply...
now i am changinh my query a lttle bit...
same query just difference of @n
declare @n varchar(20)
set @n='12/12/1985'
select cast(stuff(stuff(@n,3,0,'-'),6,0,'-') as datetime)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 06:07:57

select cast(stuff(stuff(@n,3,1,'-'),6,1,'-') as datetime)

See Books Online what STUFF function does.

E 12°55'05.63"
N 56°04'39.26"

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-31 : 06:10:34

thnks madhivnan i went through the links posted by you...
really very helpful......

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-31 : 06:10:46