Pass Through Query Formatting

In a passthrough query I am pulling a text field from SQL Server that  always contains a date value.  Is there a way, in the passthrough syntax, that I can format this as a date so it is treated as a date when it arrives in Access?  Something similar to cDATE, but for SQL?

Thanks...
snyperjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
You can use CAST or CONVERT.  For example:

CAST(MyDateString AS datetime)
0
snyperjAuthor Commented:
Yes, that works.  Unfortunately I am finding that sometimes there is no value which then causes all values in the pass through result to change to #Error.  Can that be accomodated somehow?
0
Patrick MatthewsCommented:
Are these "no value" records null?  A zero length string?  All spaces?  Text not convertible to a date?  Some combination thereof?

For such cases, what would you want the return value to be?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

snyperjAuthor Commented:
They are null and they can stay as nulls.... just don't want them to cause everything else to error.

Also not sure if this matters or not, but I see some entries as 3/15/2011 some as 3/15/11

There is no way to control the input on the business system front end to force any kind of uniformity...

Thx
0
Anthony PerkinsCommented:
The best you can do is something like this:
CASE ISDATE(YourTextColumnGoesHere)
      WHEN 1 THEN CAST(YourTextColumnGoesHere as datetime)
      ELSE NULL
END

But even that is not bullet-proof.
0
snyperjAuthor Commented:
How would I incorporate that into a pass through.   Below is the pass through in question, 'Customer_Defined_Value' is the field.
SELECT             ORDER_HEADER.Company_Code,ORDER_HEADER.Customer_No,  ORDER_HEADER.Order_No,CUSTOMER_DEFINED.Field_Name,CUSTOMER_DEFINED.Customer_Defined_Value
                         
FROM               ORDER_HEADER  INNER JOIN CUSTOMER_DEFINED_HDR_INFO ON (ORDER_HEADER.Order_No = CUSTOMER_DEFINED.Order_No) 
                         
WHERE           (ORDER_HEADER.Date_Entered BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE()) AND (ORDER_HEADER.Customer_No = 'UNI052') AND (CUSTOMER_DEFINED_HDR_INFO.Field_Name = 'DELIVERY DATE')

Open in new window

0
Anthony PerkinsCommented:
Try it this way:
SELECT  o.Company_Code,
        o.Customer_No,
        o.Order_No,
        c.Field_Name,
        -- c.Customer_Defined_Value,
		CASE ISDATE(c.Customer_Defined_Value)
			WHEN 1 THEN CAST(c.Customer_Defined_Value as datetime)
			ELSE NULL
		END Customer_Defined_Value
FROM    ORDER_HEADER o
        INNER JOIN CUSTOMER_DEFINED_HDR_INFO c ON o.Order_No = c.Order_No
WHERE   o.Date_Entered BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE()
        AND o_HEADER.Customer_No = 'UNI052'
        AND c.Field_Name = 'DELIVERY DATE'

Open in new window

0
Anthony PerkinsCommented:
Let me try that again:
SELECT  o.Company_Code,
        o.Customer_No,
        o.Order_No,
        c.Field_Name,
        -- c.Customer_Defined_Value,
        CASE ISDATE(c.Customer_Defined_Value)
                WHEN 1 THEN CAST(c.Customer_Defined_Value as datetime)
                ELSE NULL
        END Customer_Defined_Value
FROM    ORDER_HEADER o
        INNER JOIN CUSTOMER_DEFINED_HDR_INFO c ON o.Order_No = c.Order_No
WHERE   o.Date_Entered BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE()
        AND o_HEADER.Customer_No = 'UNI052'
        AND c.Field_Name = 'DELIVERY DATE'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.