Pass through query (change date format)

[H1004]

Is there a way that I can change this date format into the standard.
For example:

My current format is '200403261200'
I want to change to the following: 03/26/2004
Is this possible?

 

[dhookom]

We need to know what your data type is rather than the format that displays. Also, your subject line suggest "pass through query". Is that relevant to your question? Where is the data actually stored?

Are you wanting to display the results in a form or report text box?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[H1004]

The thing is this...I want to use the end result of this query to build my form.
I want to change my date format so that I can use =Date() in the criteria row.
Since most computer uses this format mm/dd/yyyy. I don't think I can
compare Date() to yyyy/mm/dd.
Well, I use pass through query to get the data from SQL Server. Then I use the
pass through query to convert it into different date format.
And how it is stored in the SQL Server is varchar.
Any suggestion is greatly appreciated.

 

[Rolliee]

write a procedure to make the change and another to change back from. It is no big deal.


rollie@bwsys.net

 

[dhookom]

You can use sql in the pass-through like
[blue]
WHERE Convert(VarChar(8),GetDate(),112) = YourDateField
[/blue]
I'm not sure where the "1200" comes from since you first state the data was like [red]'200403261200'[/red] and later suggested it was [red]yyyy/mm/dd[/red].

If you want a real/useable date returned by the pass-through, you can create a column
[blue]
Convert(datetime,YourDateField)
[/blue]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[H1004]

I tried the following, but somehow it doesn't like date() nor getdate().
Basically, what I want is to get all the record for today's date.

SELECT USER_ID, DATE_TIME, TRUCK_NUMBER FROM TABLE_SON WHERE CAST(SUBSTRING(DATE_TIME,1,8) AS DATETIME) = DATE()

 

[Rolliee]

Use the Msgbox to look at both sides of the WHERE and you will see. One has 8 chars and the other more.

R

 

[dhookom]

I tested my response in Query Analyzer.
Try a p-t query of:
SELECT USER_ID, DATE_TIME, TRUCK_NUMBER FROM TABLE_SON WHERE Convert(VarChar(8),GetDate(),112) = DATE_TIME

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[H1004]

I did try it & it gave me nothing back.

 

[dhookom]

H1004,
How is the date field actually storing the date value on the SQL Server? You have given two different answers to this in the past and haven't responded to me when I questioned this previously.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[H1004]

The date field stored in the SQL Server is varchar 12 (example: 200403290000). So in my code
earlier, I have change them to DATETIME datatype, so it will be like (03292004).
That way, I can set my criteria to grab only today's date since the system date is in
datetime format.

 

[dhookom]

I don't understand if you still have a problem or if you changed the table in SQL server to be DateTime data type and it is now working as you expect.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.