Comparing text field with Dates

Transact-SQL
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

1 Like
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)
1 Like
10

worked! a million thanks