Converting varchar to datetime

Question

Post reply

Converting varchar to datetime

EIAnd

Right there with Babe

Points: 743
More actions
October 3, 2008 at 1:46 pm

#193960

Hi
i have a table whit a column of dates but the type of column is varchar, for example '30/08/2004' (dd/mm/yyyy) so i need to convert those varchar dates into a real datetime type, i have create this cursor code:
declare @Fecha varchar(10)
declare Fechas cursor for
select FechaRecepcion
from Ejemplares
where ISDATE(FechaRecepcion) = 1
open Fechas
fetch next from Fechas into @Fecha
if @@fetch_status = 0
select CONVERT(datetime, @Fecha, 103) from Ejemplares
else
print 'No se pudo convertir'
close Fechas
deallocate Fechas
but the problem is that it converts me all the data whit the first register of the table, for example:
i have this values:
30/08/2004
02/05/2005
06/02/2002
when i execute the cursor:
2004-08-30 00:00:00.000
2004-08-30 00:00:00.000
2004-08-30 00:00:00.000
any idea or help doing this?
thank you so much for any help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why the cursor? That whole thing could be replaced by a single statement.

select CONVERT(datetime, FechaRecepcion, 103)

from Ejemplares

where ISDATE(FechaRecepcion) = 1

The reason for what you're getting though is that the cursor fetch gets 1 date from the table. You then execute a select against the entire table, but with the variable in the select clause, rather than the column. Hence you get a single value repeated for each row of the table

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EIAnd Right there with Babe Points: 743 More actions · Answer 2

now i remember why i use a cursor:

I execute the select CONVERT(datetime, FechaRecepcion, 103)

from Ejemplares

where ISDATE(FechaRecepcion) = 1

but then apear and error:

Mens. 241, Nivel 16, Estado 1, Línea 1

Error de conversión al convertir una cadena de caracteres a datetime.

error converting varchar to datetime.

thanks any help

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

There are a lot of things that will qualify as a date that will fail the conversion using date format 103. For example, 0 is a valid date. So is 2008 and so is FEB 2008.

You need to just set the date format to YMD and then just CAST the date column to DATETIME using your current WHERE clause.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

Also see why isdate is not fully reliable

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail