DateDiff function throwing error in Where clause
in [Queries]
|
From: Petr Danes on 3 May 2010 20:31 Pretty sure - when I show the values without trying a compare, I don't see anything wrong, and when I change the query to spin the DateDiff value out to a table, the table contains only valid integers, positive and negative. I can subsequently sort on that integer column and see that there is nothing bad at either end. I can also select only the positive ones to get the records I want, but creating an unnecessary temp table is an awkward way to do it. The comparison should work. Pete "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> p�e v diskusn�m p��sp�vku news:e6r7sOw6KHA.1424(a)TK2MSFTNGP04.phx.gbl... > You sure you've got valid values for both fields in every row? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "Petr Danes" <skruspammers(a)no.spam> wrote in message > news:uT4baAv6KHA.5476(a)TK2MSFTNGP06.phx.gbl... >>I just tried the last query in another form, using a direct comparison >>instead of the DateDiff function. Same error. >> >> SELECT Stvoreni_Convert.DatumStvoreni, CDate([DatumStvoreni]) AS Rozdil, >> Stvoreni_Convert.AkcesPodrobnostiAutoID INTO STV >> FROM Stvoreni_Convert >> WHERE (((CDate([DatumStvoreni]))>CDate([From date]))); >> >> Pete >> >> >> >> "Tom Lake" <toml_12953(a)hotmail.com> p�se v diskusn�m pr�spevku >> news:uFzLV2u6KHA.3504(a)TK2MSFTNGP05.phx.gbl... >>> >>> "Petr Danes" <skruspammers(a)no.spam> wrote in message >>> news:OqbHUsu6KHA.420(a)TK2MSFTNGP02.phx.gbl... >>>> I have a set of queries that generate dates and want to use DateDiff to >>>> select those records with certain dates are greater than others. The >>>> following clause keeps giving me an incompatible data type error: >>>> >>>> WHERE DateDiff('d',[From date],[DatumStvoreni])>0 >>>> >>>> I have the exact expression in the SELECT clause and it show positive >>>> and negative integers just fine, but when I add this test, I get the >>>> error. There are no bad values in the dataset, I've looked, and when I >>>> export the DateDiff column to a temp table and run a query on that, it >>>> works fine. Only when I use the test directly with the DateDiff >>>> function does it bomb. I also tried putting CDate() around the field >>>> expressions and it didn't help. >>> >>> I get an error when I use single quotes. I have to use this: >>> >>> DateDiff("d",[From date],[DatumStvoreni]) >>> >>> The whole expression I tried was this: >>> >>> WHERE (DateDiff("d",[From date],[DatumStvoreni])>0) >>> >>> Tom Lake >>> >>> >>> >> >> > >
From: Petr Danes on 6 May 2010 10:45
Never did find any answers to this, even when comparing dates directly in the WHERE clause and avoiding the DateDiff function altogether, so I wound up converting all my dates to strings, formatted as 'yyyy-mm-dd' and used ordinary string comparisons to get what I needed. Year-month-day is the only universally correct way to show dates anyway, but it's pretty lame that dates can't be compared directly. Pete "Petr Danes" <skruspammers(a)no.spam> p�e v diskusn�m p��sp�vku news:OqbHUsu6KHA.420(a)TK2MSFTNGP02.phx.gbl... >I have a set of queries that generate dates and want to use DateDiff to >select those records with certain dates are greater than others. The >following clause keeps giving me an incompatible data type error: > > WHERE DateDiff('d',[From date],[DatumStvoreni])>0 > > I have the exact expression in the SELECT clause and it show positive and > negative integers just fine, but when I add this test, I get the error. > There are no bad values in the dataset, I've looked, and when I export the > DateDiff column to a temp table and run a query on that, it works fine. > Only when I use the test directly with the DateDiff function does it bomb. > I also tried putting CDate() around the field expressions and it didn't > help. > > Pete > > > > -- > This e-mail address is fake, to keep spammers and their address harvesters > out of my hair. If you want to get in touch personally, I am 'pdanes' and > I use yahoo mail. But please use the newsgroup when possible, so that all > may benefit from the exchange of ideas. > > |