select distinct dates ignoring time? 1

[theotrain]

how do you write a query that pulls distinct dates out of a column with consideration to the day/month/year part only and not the time of day part?

appreciate it!...

 

[r937]

select distinct datevalue(datetimecol) as dates_only
  from ...

r937.com | rudy.ca

 

[theotrain]

brilliant! works great.

 

[theotrain]

DANG i cant believe how much trouble such a simple thing is giving me. now i cant get a similar query to work.

the idea is that the first query is used to create a pull-down menu of dates in which entries were made into the database. once a date is selected, another query pulls all the data for that date... but anyway i try it it fails. if i avoid a syntax error i get an empty recordset returned. the last query i tried was

SELECT * FROM myTable
WHERE datevalue(tableDate) = 'datevalue(#Form.date#)'

any ideas?

 

[r937]

remove the quotes -- 'datevalue(#Form.date#)' is a string

r937.com | rudy.ca

 

[PHV]

Something like this ?
SELECT * FROM myTable
WHERE DateValue(tableDate) = DateValue([Forms]![name of open mainform]![name of date control])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[theotrain]

thanks guys, but still no luck. without the quotes i get a "missing operator" error(!?)

also no luck with
DateValue([Forms]![name of open mainform]![name of date control])

which i dont even understand gives me a "Too few parameters" error. excuse me for having my head so deep up my ars, but i didnt even know there was a "!" operator in SQL? and why would you have to do a DateValue() on a value as you pull it from the DB when it is supposed to be stored as a date? isnt that for making a string into a Date datatype?

maybe i need to clarify im working in coldfusion, i didnt think that would matter for the sql part.

i really need to get my head around the fundamentals of dealing with dates i think. are there any good resources you would recommend?

 

[r937]

ah, good old coldfusion

WHERE datevalue(tableDate) = ###Form.date###

assuming form.date is a date value (without time)

note that this type of date condition (applying a function to the column) is inefficient because it requires a table scan, so if the table is large, you should have an index on the column, and then use this datetime range condition instead --

WHERE tableDate >= ###Form.date###
AND tableDate < ###DateAdd("d",1,Form.date)###

r937.com | rudy.ca

 

[theotrain]

thanks, that did it! i would greatly appreciate a short explanation why that code blows up on me if i do it #var# but works with ###var###? in what circumstances are the extra # signs necesary?

 

[r937]

as you know coldfusion uses # to delimit coldfusion expressions

but what if you want to generate a # into the output?

then you need to code two of them in a row

for example, in a style sheet,

background-color: ##F9F3F7

same for generating sql

now, as you know, access requires # to delimit dates, for example

#2006-12-04#

therefore, to generate an access date expression from a coldfusion variable, you first use ## to represent the opening access date delimiter, then #Form.date# for the actual value, then ## for the closing access date delimiter

simple, when you break it down

r937.com | rudy.ca

 

[theotrain]

ahh...delicious and refreshing!
thanks r937.