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

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-03-09 : 05:38:14

Hi,

I have a field called reportdate that is varchar (50) 01022005. I need to convert it to a date field so that it will appear as follows:
01/02/2005.

Any help will be appreciated

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-09 : 06:20:53

[code]
declare @b varchar(50)
set @b = '01022005'
select @b,
convert(datetime, left(@b, 2) + '/' + substring(@b, 3,2) + '/' + right(@b, 4)),
convert(varchar(10), convert(datetime, left(@b, 2) + '/' + substring(@b, 3,2) + '/' + right(@b, 4)), 103)
[/code]

Go with the flow & have fun! Else fight the flow

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-03-09 : 07:49:57

Thanks. I forgot to add something to my previous post.
Reportdate which is a varchar field contains the date in one of the following ways:
8122004
29122004

How can I convet that to date? Also, since i have multiple rows in my table I tried to do something like this but it gave me an error:
declare @b varchar(50)
set @b = (select Reportdate from importfile)

RM
Yak Posting Veteran

65 Posts

Posted - 2005-03-09 : 08:15:30

set dateformat dmy

declare @b varchar(50), @len Int
set @b = '8122004'
Set @len = len(@b)
select @b,
convert(datetime, Left(@b,@len - 6) + '/' + substring(@b, case when @len = 8 Then 3 Else 2 end,2) + '/' + right(@b, 4))
,convert(varchar(10), convert(datetime, Left(@b,@len - 6) + '/' + substring(@b, case when @len = 8 Then 3 Else 2 end,2) + '/' + right(@b, 4)), 103)

RM
Yak Posting Veteran

65 Posts

Posted - 2005-03-09 : 08:19:32

set dateformat dmy

Declare @table Table (StrDate varchar(8))

Insert @table
Select '8122004'
Union All
Select '29122004'

Select strdate
, convert(datetime, Left(strdate,len(strdate) - 6) + '/' + substring(strdate, case when len(strdate) = 8 Then 3 Else 2 end,2) + '/' + right(strdate, 4))
, convert(varchar(10), convert(datetime, Left(strdate,len(strdate) - 6) + '/' + substring(strdate, case when len(strdate) = 8 Then 3 Else 2 end,2) + '/' + right(strdate, 4)), 103)
From @table

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-03-10 : 04:27:33

Thanks for the help :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources