Need help with complex query

[BHA TECH]

You are a genius! I am an idiot. I used the wrong code in the wrong area. now works perfect.

here is the completed query which is now working seamlessly finding all matching dates in all fields.

SELECT *
FROM DETAILS
WHERE (((DETAILS.PaymentDate)>=Date() And (DETAILS.PaymentDate)<Date()+1)) OR (((DETAILS.PaymentDate1)>=Date() And (DETAILS.PaymentDate1)<Date()+1)) OR (((DETAILS.PaymentDate2)>=Date() And (DETAILS.PaymentDate2)<Date()+1)) OR (((DETAILS.PaymentDate3)>=Date() And (DETAILS.PaymentDate3)<Date()+1)) OR (((DETAILS.PaymentDate4)>=Date() And (DETAILS.PaymentDate4)<Date()+1)) OR (((DETAILS.PaymentDate5)>=Date() And (DETAILS.PaymentDate5)<Date()+1)) OR (((DETAILS.PaymentDate6)>=Date() And (DETAILS.PaymentDate6)<Date()+1)) OR (((DETAILS.PaymentDate7)>=Date() And (DETAILS.PaymentDate7)<Date()+1)) OR (((DETAILS.PaymentDate8)>=Date() And (DETAILS.PaymentDate8)<Date()+1)) OR (((DETAILS.PaymentDate9)>=Date() And (DETAILS.PaymentDate9)<Date()+1)) OR (((DETAILS.PaymentDate10)>=Date() And (DETAILS.PaymentDate10)<Date()+1)) OR (((DETAILS.PaymentDate11)>=Date() And (DETAILS.PaymentDate11)<Date()+1));

With at least this much working, I am forever in your debt. Finally I can undo layers of bad antiquated programming.

 

[Brianwarnock]

Namliam
he is only looking for =Date(), I assume that he has got rid of the Like.

It looks like you are saying that you cannot just get a Date Value on SQL SERVER.

Brian

EDit BHA has posted whilst I was typing and yes it looks like he has times in there afterall.
Can he not use DateValue(DETAILS.PaymentDate1) to just get the Date part.

 

[BHA TECH]

As a side note, you wouldn't believe how many text fields they have for saving dates in this DB. It's no wonder I've been so frustrated trying to get anything to work properly with them. It seems so clear now. Thank you to everyone who helped on this learning experience. I am grateful.

 

[namliam]

It looks like you are saying that you cannot just get a Date Value on SQL SERVER.

That is exactly what I am saying, it doesnt just look like it... it just doesnt excist, doesnt in Oracle either BTW and in many other databases... You have to "calculate" it

EDit BHA has posted whilst I was typing and yes it looks like he has times in there afterall.

Formatting on table level has its drawbacks and this is one of them.

As a side note, you wouldn't believe how many text fields they have for saving dates in this DB. It's no wonder I've been so frustrated trying to get anything to work properly with them. It seems so clear now. Thank you to everyone who helped on this learning experience. I am grateful.

While you are working on it, having fields like PaymentDate1, PaymentDate2, PaymentDate3 etc in any way shape or form is a definate sign of BAD DESIGN.
Right now this database is allowing for 12 payments (per year?) what happens if you have only 2? 10 empty fields
What happens if you have 15 payments, you have a problem cause you cannot place the 3 extra.

It is usually better design to work with a seperate payment table that would record every payment in a seperate record.

 

[Brianwarnock]

I worded my comment badly, even access always has a time value, but it can be 0, surely if on the SQLSERVER only a date was entered the time would be 0 and comparing for =Date would retrieve the data.

Brian

PS not sure why I am continueing as I am retired and will never need this info, but I'm really curious but feel that I shouldn't waste your time.

Brian

 

[namliam]

well yes and no...

While we have Date() in Access to fetch "15-Jan-2010 00:00:00" there is no such equivalent on SQL Server. There is only Getdate which will include the current server date/time, equivalent to Now().
There simply is no (that I know of) date only thing.

Yes any date/time will always have a time component, always, at the very least 00:00:00. A date is 'not really a date' it is a number of days that have passed since 01-jan-1900.
Here is an excercise for you:
1) Enter into A1 in excel the date: 01-Jan-2010
2) Enter into A2 in excel the number: 1
3) copy from A1 and paste the format onto A2, visible date: 01-Jan-1900
4) Go back to A1 and make it display numbers (format...etc), visible data: 40179

there have been 40179 days between the two dates. Now change the 40179 to 40179.6 and apply a date only format (dd-mmm-yyyy) visible: 01-Jan-2010
Now apply a time only format (HH:MM:SS), visible 14:24:00
Now apply a date/time format (dd-mmm-yyyy hh:mm:ss), visible 01-Jan-2010 14:24:00

Now the 0.6 might be a bit hidden, but change it to 0.25 and you get 6:00:00
Change to 0.5, 12:00:00
Change to 0.75 for 18:00:00

Going back to the question: "surely if on the SQLSERVER only a date was entered the time would be 0 and comparing for =Date would retrieve the data."
The short and quick answer is Yes.

 

[Brianwarnock]

Thankyou for that Namliam, tho' I really do understand how dates work in ACCESS, Its the

Going back to the question: "surely if on the SQLSERVER only a date was entered the time would be 0 and comparing for =Date would retrieve the data."
The short and quick answer is Yes.

That is interesting as way back in the thread I said

Just to satisfy my curiosity I tried it on a simple test db with a couple of date fields and it worked fine using Like, so I'm left with only one thought which is unlikely from what you have said and that is that the other field has some how got a time element in it that is not 00:00:00.

To help more we probably need to get hold of the DB, as for switching to Text dates that is a real no go.

But please note I had already said use = Date() not Like Date()

I was assured that there were no times, pity so much time was wasted.

Brian

 

[namliam]

Thankyou for that Namliam, tho' I really do understand how dates work in ACCESS

Glad you do, so many people dont. The date thing is a mistery to people due to " the date thing " if you think of them as they are, numbers, it makes things SO much easier

I was assured that there were no times, pity so much time was wasted.

Well the source of the problem offcourse lies in
1) Design
2) Design
3) Understanding of the OP
4) Design
5) Formatting
6) The expectation to "jump into access easily"
7) Design/formatting
8) Things like
"It's not you, it's me. I need flexibility, reliability, speed and most importantly, true support."

People are so biggated against Access that at the first sign of "trouble" they quit and give up. Having "studied" MySQL or Oracle for Years they expect to handle Access in a day, which they cannot because it is different and (contrary to popular believe) it requires real skill to work with it in the right way.

Just today in my workplace I encountered atleast 4 simular examples of people going "oh this is quick and easy" only to go "oh this is not working it is impossible have to use a "real" datebase like Oracle or SQL Server with a working web front end".
My rear end, they just dont know their job... or MY job ... and should stay the hell out of the kitchen

 

[datAdrenaline]

>> A date is 'not really a date' it is a number of days that have passed since 01-jan-1900. <<

In MSAcess the base date is 30-jan-1899 00:00:00 {edit --- CORRECTION: MSAccess date base is 30 dec 1899}

>> There simply is no (that I know of) date only thing. <<

mailman has already said it, in the same post as the quoted string above, ALL dates, even those returned by the Date() function have a Time component, its just 0, and Access/VBA trims it off for display purpose or coersion (both forced and implied) to Text. As a matter of fact, if Now() is executed at the EXACTLY midnight from VBA's Immediate window, with a command like this (note this will change your computers time) ...

Time = #12:00 AM# : ? Now()

.. you will not see the 00:00:00 that is part of the date.

So, with SQL Server not having a function that takes the floor (integer) of the value returns by GetDate() is really not that surprizing since we have the ability to use Floor() and Cast().

----

As a point of note, you can use a Text base comparison by concatenating your date fields into one long string with a separator, then using a LIKE operator against a trimmed GETDATE() result ...

WHERE '~' + CONVERT(char(10),[Field1],20) + 
          '~' + CONVERT(char(10),[Field2],20) +
          '~' + CONVERT(char(10),[Field3],20) + 
          '~' ... <etc> ... + '~'
      LIKE '%~' + CONVERT(char(10),getdate(),20) + '~%'

...

Or, as a more terse method to set the time component to 0 on your columns (and GETDATE() for that matter, you can use an expression like this:

DATEADD(DD,0,DATEDIFF(DD,0,[your date column])

Then your WHERE clause would be like:

WHERE DATEADD(DD,0,DATEDIFF(DD,0,[Field1]) = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE())
      OR DATEADD(DD,0,DATEDIFF(DD,0,[Field2]) = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE())
      OR DATEADD(DD,0,DATEDIFF(DD,0,[Field3]) = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()) ... <etc>

Note that the DATEADD/DATEDIFF expression is ever so slightly less efficient than the CAST and FLOOR method, but I kinda like the brevity of it and with my non scientific testing with a 100,000 recordset the CAST | FLOOR method was ~70ms faster ... not much, but if your are dealing with millions of rows ... well ... it might be worth the extra expression length.

 

[namliam]

>> A date is 'not really a date' it is a number of days that have passed since 01-jan-1900. <<

In MSAcess the base date is 30-jan-1899 00:00:00

If your nitpicking, pick right... it is 30-dec-1899, not jan, then also that is 0.... assuming it is the base... Could as well be 1... for the 31-dec

.. you will not see the 00:00:00 that is part of the date.

So, with SQL Server not having a function that takes the floor (integer) of the value returns by GetDate() is really not that surprizing since we have the ability to use Floor() and Cast().

Oracle can Trunc(adate) to take away the time part directly, why the need for a cast (floor(cast(adate))) vs just Floor(adate)
SQL Server in general is much more strict with types though, so that is probalby where it is originating. Floor requires a number format (float/decimal/etc) thus cannot accept a "date-number"

As a point of note, you can use a Text base comparison by concatenating your date fields into one long string with a separator, then using a LIKE operator against a trimmed GETDATE() result ...

While this works, the big drawback is, like doesnt use Indexes . Which can put a real dent in your performance

Or, as a more terse method to set the time component to 0 on your columns (and GETDATE() for that matter, you can use an expression like this:

DATEADD(DD,0,DATEDIFF(DD,0,[your date column])

Wow intresting twist...

An overview of some of the different options:
cast ( DATEDIFF(DD, 0, Getdate() ) as datetime)
convert ( datetime, CONVERT(char(10), Getdate(), 20), 20)
Cast( Floor ( Cast ( Getdate() as Float ) ) as datetime )
DATEADD(DD, 0, DATEDIFF(DD, 0, Getdate() ) )

vs Oracle: Trunc(Sysdate)
vs Access: Date()

 

[datAdrenaline]

>> If your nitpicking, pick right... it is 30-dec-1899, not jan <<

ROFL!!! ... I can't beleive I actually wrote Jan! <shaking my head shamelessly .... I should have just went to bed! > ... Thank you for correcting the information, {I will edit the post to indicate error!}

>> the big drawback is {in reference to Text concatenation}, like doesnt use Indexes . Which can put a real dent in your performance <<

Agreed. Any time you wrap a column with an expression and set your criteria against the result of the expression, indexes are not used. So your suggestions that use the raw column value are speedier if an index is declared on each one of those non-normalized PaymentDate columns.

>> An overview of some of the different options:
cast ( DATEDIFF(DD, 0, Getdate() ) as datetime)
convert ( datetime, CONVERT(char(10), Getdate(), 20), 20)
Cast( Floor ( Cast ( Getdate() as Float ) ) as datetime )
DATEADD(DD, 0, DATEDIFF(DD, 0, Getdate() ) )

vs Oracle: Trunc(Sysdate)
vs Access: Date() <<

Oh c'mon, you mean that its more obvious how to do this in Oracle and Access? {toungue and cheek } ... Also, it makes SQL Server T-SQL look that much more complex, thus allowing the boss to be impressed ....

All kinding aside, I normally do like the stronger data typing of SQL Server ... but this is not one of those cases.

 

[namliam]

>> the big drawback is {in reference to Text concatenation}, like doesnt use Indexes . Which can put a real dent in your performance <<

Agreed. Any time you wrap a column with an expression and set your criteria against the result of the expression, indexes are not used. So your suggestions that use the raw column value are speedier if an index is declared on each one of those non-normalized PaymentDate columns.

Not completely true for all databases, not sure which but some allow you to build an index on "calaculated fields" i.e. To_Char(Yourdate,'YYYYMMDD')
If you then do: Where To_Char(Yourdate,'YYYYMMDD') = '20100115'
The index is used..... but then the index has to exist and its only some of the databases (I think). Then again, why create this stupid index if you can just use the "normal" field instead??

All kinding aside, I normally do like the stronger data typing of SQL Server ... but this is not one of those cases.

Both have their pro's and cons, both have their CONS as well.
I love Oracle but hate SQL Server, then again I do Love to hate it... lol