May 5, 2005 at 10:46 am
I am trying to convert a varchar(255) column to datetime. The varchar column has a date in the form of DD/MM/YYYY. I created a new datetime column to receive the results.
update Inventory
set DateColumn = convert(datetime, DateStringColumn, 101)
This is complicated by the fact that DataStringColumn contains some bad values.
Any suggestions would be appreciated.
David
May 5, 2005 at 10:49 am
Update Inventory
set DateColumn = CASE WHEN ISDATE(DateStringColumn) = 1 THEN convert(datetime, DateStringColumn, 101) ELSE NULL END
May 5, 2005 at 11:02 am
Remi,
Thank you. Worked perfectly.
David
May 6, 2005 at 4:05 pm
Not to piddle, but this will perform better.
update Inventory
set DateColumn = convert(datetime, DateStringColumn, 101)
where isdate(DateStringColumn) = 1
Signature is NULL
May 6, 2005 at 8:55 pm
While we're at it why not get rid of the convert statement it seems to work fine for me without it.
update Inventory
set DateColumn = DateStringColumn
where isdate(DateStringColumn) = 1
May 7, 2005 at 6:48 am
Thanks All...
But isn't the explicit cast more efficient than the implicit cast? I'm not sure how SQLServer does it, but somehow it must find out the datatype that is being cast to a date.
David
May 7, 2005 at 10:58 am
Can't say that I know for sure which is the most efficient but if I were to explicity convert to datetime in this type of situation, I would not bother to convert it to a style (i.e. 101) as that seems irrelevant since it will not be stored in database that format anyway.
I might use:
update Inventory
set DateColumn = cast(DateStringColumn as datetime)
where isdate(DateStringColumn) = 1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply