Converting varchar to datetime in select statement

Question

Converting varchar to datetime in select statement

Viewing 5 posts - 16 through 19 (of 19 total)

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

stax68 SSChampion Points: 11711 More actions · Answer 1

I wouldn't combine data cleansing with ordinary DML. Check the values using a SELECT with ISDATE() first, then fix any corrupt values, then move the data.

If you have mixed date formats, in particular mixed dmy, mdy formats, then in some cases this will be caught by the ISDATE() which I think only allows US (mdy) format. But there could be other cases in European format which are also valid (though incorrect) US dates. In that case you are in trouble, and will have to do some more detailed checking and possibly manual cleaning.

The immensely unuseful moral of the story is don't ever store non-string values as strings.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Mark Green SSCrazy Points: 2085 More actions · Answer 2

In relation to:

CASE WHEN ISDATE

(DischargeDate) = 1 THEN DischargeDate END,

What does the select statement return if ISDATE(DischargeDate) <> 1. Does it just return nothing for that row?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

It returns <NULL> value for every ISDATE() = 0. So for all values that can't be interpreted as date [ISDATE() = 1], the CASE statement substitutes the string with <NULL> instead.

N 56°04'39.16"
E 12°55'05.25"

RyanRandall SSChampion Points: 13623 More actions · Answer 4

> ISDATE() which I think only allows US (mdy) format

I don't think this is correct. I think it works from the dateformat setting (see example below).

I agree that data cleansing shouldn't be combined with ordinary DML - that is unless the data cleansing is trivial, which might be the case here.

DECLARE @datestring varchar(8)

SET @datestring = '12/21/98'

set dateformat dmy

SELECT ISDATE(@datestring)

set dateformat mdy

SELECT ISDATE(@datestring)

/*results

-----------

0

-----------

1

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

stax68 SSChampion Points: 11711 More actions · Answer 5

Yes, wasn't sure and didn't look it up. Largely irrelevant to the issue though.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant