MS Query - Converting Date/Time to just Date

[Ally72]

Hi

I'm hoping this is the right forum as I couldn't find anything else that I should perhaps post under.

I'm using MS Query, to query an external database and return data into Excel. I know Access and Excel, but not much SQL and and very new to MS Query.

I need to convert the Date/Time fields to just Date as I'm doing calculations where I'm trying to find out where one is greater than the other to find errors.

Where for example the Date/Time has a time entered and the other field that I'm subtracting from is the same date but doesn't have a time entered, this causes a return when in fact there shouldn't be. I hope that's clear.

In Excel we can get round this using INT(...) but I get INT is not recognised as a built-in function ... as I do with CDate(), DateValue() and a heap of others I've tried.

The furthest I've got is by using:
Convert(Char,STKTIA_DTONSET,103)
which is great to begin with, as it gives the data in the correct 'format' but I can't then subtract one from the other, as it's string ie:

Convert(Char,STKTIA_DTONSET,103)-Convert(Char,STKTIA_DTREF,103)
as I get the error
Operand data type Char is invalid for subtract operator. Statement(s) could not be prepared.

I had originally tried:
Convert(DateTime,STKTIA_DTREF,103) but this did not make any difference to the data.

Don't want to give too much more information in case no-one really knows MS Query, but I'm really struggling and have posted on another couple of boards but haven't gotten anywhere.

I have (hopefully) attached a screenshot of the query in MS Query and hope that someone might be able to help please!!

Thanks

Ally

 

[gmmastros]

Sounds like you want to use the DateDiff function. Ex:

[tt][blue]
DateDiff(Day, STKTIA_DTONSET,STKTIA_DTREF)
[/blue][/tt]

The DateDiff function calculates the number of "boundaries" crossed, so if both dates are on the same day, but have different times, DateDiff will return 0. Likewise, if two dates are only a second off, but on different days, DateDiff will return 1. Ex:

Select DateDiff(Day, '2010-09-21 23:59:59','2010-09-22 0:00')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ally72]

Thanks for replying. I tried DateDiff earlier on and got the same error message about it not being a recognised built-in function etc.

 

[Ally72]

Sorry - please ignore that. Realised I'd input my DateDiff wrong. Just going to try it again.

 

[Ally72]

Fantastic - thank you so much!! Really don't know why I didn't think of that before.