• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1014
  • Last Modified:

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.
0
ruemichael
Asked:
ruemichael
1 Solution
 
nmcdermaidCommented:
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.

0
 
ruemichaelAuthor Commented:
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=?))
0
 
dbbishopCommented:
One of the problems is knowing that the date will always be in a very specific format. For example, will September 5, 2007 be '9/5/2007', or could it be '09/05/2007', '09/5/2007', '0/05/2007'? All of these are different if compared to the character string '09/05/2007' and only one will match.

You can use CONVERT (or CAST) as nmcdermaid suggested, and if the format is mm/dd/yyyy, you are almost forced to. However, be aware that if you use CAST or CONVERT on the date and DRAWING_ID is indexed, you lose the use of the index on the column.

The format would be--change:
(CUST_ORDER_LINE.DRAWING_ID='9/20/07')
to (assuming this is a stored procedure):

CREATE PROCEDURE myProc
@Date1 DATETIME,
@Date2 DATETIME,
@PartID INT -- or whatever datatype it is.
AS
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 ((CAST(CUST_ORDER_LINE.DRAWING_ID='9/20/07' AS DATETIME) BETWEEN @DATE1 AND @DATE2)) AND (CUSTOMER_ORDER.STATUS='R') AND (PART.ID=@PartID))

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Scott PletcherSenior DBACommented:
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 ...
0
 
dbbishopCommented:
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)
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now