US Date and UK Date 1

[niall29]

I am trying to run a query sorted by date on a table where the date column was set up Varchar 8 but my problem is someone put some dates in the UK way (dd/mm/yyyy) while the rest of the dates are US (mm/dd/yyyy) so now when I try to sort by date it can't.
Does anyone know how I could built a case statment to tell it if Date is UK to convert to US else .....

I can not figure out how to do this and any help would be greatly appreciated.

Thanks in advance

 

[DotNetGnat]

instead of using CASE use either cast() or convert() functions

-DNG

 

[gmmastros]

Unfortunately, you have a HUGE problem. You can use the IsDate function to determine if a string represents a valid date. Unfortunately, some dates are valid for both formats. For example, 1/4/2006 is valid in britsh and us formats (Jan 4 or Apr 1).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

My best suggestion is to fix the data. Best case... Change the data so that it is consistent. Then Change the field's data type to DateTime so that you won't be allowed to insert invalid dates.

That being said, you can use the IsDate function to determine if you have a valid date. If it's not, you can swap the month and day. Something like this...

Declare @Temp Table(Data VarChar(10))

Insert Into @Temp Values('12/25/2006')
Insert Into @Temp Values('25/12/2006')

Select 	*,
		Case When IsDate(Data) = 1 Then Convert(DateTime, Data)
			 Else SubString(Data, 4, 2) + '/' + Left(Data, 2) + '/' + Right(Data, 4)
			 End
From 	@Temp 
Order By Case When IsDate(Data) = 1 Then Convert(DateTime, Data)
			 Else SubString(Data, 4, 2) + '/' + Left(Data, 2) + '/' + Right(Data, 4)
			 End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[harebrain]

George, I think you hit this nail on the head and then backed off by sugar-coating it with code. (How do like those mixed-metaphors? )

Assuming the distribution of the dates is random, 30% of them are ambiguous. You can't fix ambiguity with code. You were right when you said this must be fixed manually at the source, then use the correct datatype to prevent recurrences.

 

[gmmastros]

harebrain,

I was thinking the same thing prior to clicking the submit button. However, I decided to post the code anyway because it does highlight a method (with a little tweaking) that the original poster can use to sort out this problem.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[niall29]

Thanks everyone for your responses especially gmmastros for the code

 

[harebrain]

Just so you realize that the code only helps for the 70% of the cases where there is no ambiguity. Otherwise, it is biased to an American date, which might or might not be right.

 

[yetanotherjo]

George's code would be useful if you could isolate the batch of data loaded incorrectly....actually, it'll help me too 'cause I have some data with a similar problem which I've been avoiding confronting, so thanks guys