Converion of a long date to a short date

[llkhoutx]

I have a passthrough query which will not generate what I want, i.e. using

UPDATE 
dbo.tdw 
SET 
manifest_date = CONVERT(varchar(11),[manifest_date],101)
WHERE report_state='IL' AND 
report_year=2012 AND 
report_month=1;

manifest_date, initial text 'Jan 1, 2012 09:33AM', and returns 'Jan 1, 2012' also text.

I want '01/01/2012' (text), MM and DD are always 2 digits.

Is the only way to do this conversion is to parse the date by m, d and y and then build the string date expected? Maybe I have to convert the long date to an actual date before the conversion???

Thank you in advance for any responses.

 

[boblarson]

Have you tried CAST instead of Convert?

 

[llkhoutx]

NO, I'll give it a try. Thanks.

 

[pbaldy]

Is it a date/time field? CONVERT works for me.

 

[llkhoutx]

No it is not a date field, it's a text field. That's probably the issue.

I'll try CAST Monday, I'm trying to get out of dodge for the weekend. Happy Holiday!

 

[pbaldy]

You may need them both; CAST to change it to a date/time field, CONVERT to get the desired format.

 

[llkhoutx]

pbaldy, thanks to your hint, the following SQL Server query produces the desired result, e.g.

select
convert(varchar(11),cast(cast(manifest_date as varchar(11)) as datetime),101)
FROM
dbo.tdw
WHERE
report_state='IL' AND
report_year=2012 AND
report_month=1

The SQL Server crowd disparages Access queries, but it's substantially easier but slower than SQL Server. Passthrough queries are great time savers as compared to Access queries.

Thanks again for your tip.

 

[pbaldy]

No problem. I use both products, and at least half the time I use Access queries against SQL Server linked tables. Access (JET/ACE) is very good at passing SQL back to the server to be processed when it can, so many/most Access queries will run just fine. When they don't, I'll switch to a pass-thru, stored procedure or view.

You're supposed to be gone; get out of there!!