SQLTeam.com | Weblogs | Forums

Comparing text field with Dates


#1

Hi All,

I have a text field that mainly contains properly formatted dates (01/01/2000), but it's a text field, so normal text may exist (customer ordered an extra kit)

How can I write a query that will compare this field to another (real) dateTime field? I keep getting a " Conversion failed when converting date and/or time from character string." error, my query looks like this:

when
salesOrderRelease.fshipdate > CAST('salesOrder.fdate' AS date)

Thank you!


#2

Could something like

salesOrderRelease.fshipdate > CAST(CASE WHEN ISDATE(salesOrder.fdate) THEN salesOrder.fdate ELSE NULL END  AS date)

help? This is not the best code but hopefully it gives you an idea


#3

makes sense but I keep getting a " SQL Server Database Error: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'." error :frowning:


#4

Slight change to @djj55's code:

salesOrderRelease.fshipdate > 
	CAST( 
		CASE WHEN ISDATE(salesOrder.fdate) = 1 THEN salesOrder.fdate 
		ELSE NULL END  
	AS date)

#5

@JamesK, good catch.


#6

?

SQL Server Database Error: Argument data type text is invalid for argument 1 of isdate function.


#7
salesOrderRelease.fshipdate > 
	CAST( 
		CASE WHEN ISDATE(CAST(salesOrder.fdate AS VARCHAR(MAX))) = 1 THEN salesOrder.fdate 
		ELSE NULL END  
	AS date)

#8

sorry but I keep getting the "SQL Server Database Error: Explicit conversion from data type text to date is not allowed." error

how can I run:

salesOrderRelease.fshipdate >
CAST(
CASE WHEN ISDATE(CAST(salesOrder.fdate AS VARCHAR(MAX))) = 1 THEN salesOrder.fdate
ELSE NULL END
AS date)

by itself to make sure it is sound?


#9

cast one more time.

salesOrderRelease.fshipdate > 
CAST( 
CASE WHEN ISDATE(CAST(salesOrder.fdate AS VARCHAR(MAX))) = 1 
THEN CAST(salesOrder.fdate AS VARCHAR(32))
ELSE NULL END
AS date)

#10

worked! a million thanks