asked on

Microsoft Query on SQL varchar field

I have created an Excel sheet with a Microsoft Query to a MSSQL database. I'm having issues querying a date range on a field being used for date but configured as a varchar data type. Changing the data type tinternally to SQL is not an option.

nmcdermaid

You can use this query as the source for Excel

SELECT <Fields>
FROM YourTable
WHERE YourDateField = CONVERT(DATETIME,<yourvarcharfield>)

However because your source field is varchar, I expect a lot of data in there will be invalid, so the CONVERT function will fail a lot of the time.

ruemichael

ASKER

Actually the field in question is being populated through another automated process so data integrity isn't much of a question; always being m/d/yy. My current query is below. My challenge is to perform the same query on a date range rather than the static date. The DRAWING_ID (no idea how they came up with that) is data type varchar.

SELECT CUST_ORDER_LINE.DRAWING_ID, CUST_ORDER_LINE.ORDER_QTY, CUST_ORDER_LINE.TOTAL_USR_SHIP_QTY, PART.ID
FROM VEPMW.dbo.CUST_ORDER_LINE CUST_ORDER_LINE, VEPMW.dbo.CUSTOMER CUSTOMER, VEPMW.dbo.CUSTOMER_ORDER CUSTOMER_ORDER, VEPMW.dbo.PART PART
WHERE CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID AND CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.ID AND CUST_ORDER_LINE.PART_ID = PART.ID AND ((CUST_ORDER_LINE.DRAWING_ID='9/20/07') AND (CUSTOMER_ORDER.STATUS='R') AND (PART.ID=?))

ASKER CERTIFIED SOLUTION

D B

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Scott Pletcher

dbbishop is correct. Moreover, to prevent possible conversion errors, add a check to make sure it is a valid date before CASTing it to datetime:

((CASE WHEN ISDATE(CUST_ORDER_LINE.DRAWING_ID) = 1 THEN
CAST(CUST_ORDER_LINE.DRAWING_ID AS DATETIME) ELSE '19000101' END BETWEEN @date1 ...

D B

Just noticed an error in my code. Change:
((CAST(CUST_ORDER_LINE.DRAWING_ID='9/20/07' AS DATETIME)
to:
((CAST(CUST_ORDER_LINE.DRAWING_ID AS DATETIME)

Computer101

Forced accept.

Computer101
EE Admin