Help with filtering a query with a date as string column

Report · May 13, 2008

Hello -- I've got a table column that contains dates as string/text type (access db). I need to make a query of my table that includes only the dates that match today's date (example 5-13-2008). I need two parts. One is to capture today's date, and another to set the filter in the query.
Here's my base query
<cfquery name="someQuery" datasource="someDS">
SELECT col1, col2, dateCol, col4
FROM someTable
WHERE dateCol = "today's date"
</cfquery>

What's the best way to capture today's date? And then what's the best way to set it to a string so I have an object to compare with dateCol. I know #now()# will get today's date, however it returns date + time. How can I format it as M-D-YYYY? Then, how do I convert it to a string so I can evaluate in my query? Any help is appreciated!

Report · May 13, 2008

Redesign your database and store dates as dates instead of strings.

Report · May 13, 2008

quote:

Originally posted by: Dan Bracuk
Redesign your database and store dates as dates instead of strings.

I would love to do this, however I couldn't figure out how to store date and time values AS DATE to my database. My front end is Flex with a Coldfusion backend. When a form is filled, I need it to send the current time and the current date of submission. Using Date(), I can obtain the current date (date + time). However it's not easy to separate the [date] and [time] values of the Date() result without using a dateformatter with a mask. By doing this, I've now taken my date as DATE and converted it to date and time both AS STRING. Well after several trial and error tries as well as looking for any help online, I just decided to use the dateformatter, convert date and time to string and then save each to their own columns.

The other way of doing this is to just have one column that holds both the date and time, and use Date() to pass the correct data-type to my db. However, when passing Date() to my db, it only stores the date but sets the time to 00:00:00. Now if I could find a way to get both correct date and time (example 5-13-2008 1:31:xx PM), I could just use a dateformatter in my front end to display the dates as I want. Again, the problem is I need the TIME value as well, and it keeps defaulting to 00:00:00 in access.

@paross1-- I will try what you've provided just to get this thing working, regardless if they are strings in the db instead of dates.

If anyone can solve my default time issue mentioned above, that would be great too..

Report · May 13, 2008

In Access, use now() for current date/time (without the #, since that would make it think that you wanted the ColdFusion NOW() function.

SELECT col1, col2, now()
FROM yourTable... etc.

As for your date string, you might try the Access CDate() function to convert it to a date/time datatype...

<cfquery name="someQuery" datasource="someDS">
SELECT col1, col2, dateCol, col4
FROM someTable
WHERE CDate(dateCol) = now()
</cfquery>

However, be advised that using CDate will convert your "string" date column to a date/time object, which means that it will contain both the date and the time components. Same with now(), so CDate(dateCol) = now() is only going to be "true" for one short period of time during the day. What you probably want to actually do would be to "truncate" the date/time object to date only for both, then compare the two. If memory servers me correctly, Access doesn't have an nifty functions to do this, but you can try using the format() function to convert both to strings...

<cfquery name="someQuery" datasource="someDS">
SELECT col1, col2, dateCol, col4
FROM someTable
WHERE format(dateCol, 'm-d-yyyy') = format(now(), 'm-d-yyyy')
</cfquery>

Phil

Report · May 13, 2008

If you really want a string format to use you can use the dateFormat()
function.

I.E. WHERE dateCol = '#dateFormat(now(),"M-D-YYYY")#'

Of course this is a poor performing solution as mentioned before. A
better bet is to use a date column in the database then you can make use
of all the date functions available both in database management systems
and CFML to make more scalable of flexible comparisons.

Report · May 13, 2008

quote:

If anyone can solve my default time issue mentioned above, that would be great too

I thought that I did solve it. Use format(now(), 'm-d-yyyy') to give you your current date in a text format. Oh, I made an error on my previous example since dateCol was already a string.

<cfquery name="someQuery" datasource="someDS">
SELECT col1, col2, dateCol, col4
FROM someTable
WHERE dateCol = format(now(), 'm-d-yyyy')
</cfquery>

Sorry,

Phil

Report · May 13, 2008

I appreciate the help Phil.

Ideally, Dan is correct above in that I should use date-type columns for dates. Using date-type values, I have more options for manipulating my data in coldfusion.

My real issue is that I cannot pass Date() data to my access db without losing the time component. You see, Date() in Flex gives me the current day's date in this format: Tue May 13 15:17:35 GMT-0400 2008

When I pass this to my db, it stores at 5-13-2008 and loses the time completely. Then when I call this column, I get 5-13-2008 00:00:00 AM. I have tried altering the mask format of the field to use 'General Date', which formats as M-D-YYYY H:MM:SS TT (example 5-13-2008 3:17:35 PM), however because of the awkward format that Date() gives, I think the time is lost.

There's not much documentation on Flex date/time stuff, nor are there examples using access db's. I hope this is just a problem with access though, as I plan on using SQL for our bigger apps.

Report · May 13, 2008

I'm sure that you will have the same type of issue with SQL Server, since this seems to be more of a Flex problem. SQL Server stores its "dates" as date/time objects as well, but has a different set of built-in functions.

Phil

Report · May 13, 2008

quote:

Originally posted by: EvolvedDSM
I appreciate the help Phil.

Ideally, Dan is correct above in that I should use date-type columns for dates. Using date-type values, I have more options for manipulating my data in coldfusion.

My real issue is that I cannot pass Date() data to my access db without losing the time component. You see, Date() in Flex gives me the current day's date in this format: Tue May 13 15:17:35 GMT-0400 2008

When I pass this to my db, it stores at 5-13-2008 and loses the time completely. Then when I call this column, I get 5-13-2008 00:00:00 AM. I have tried altering the mask format of the field to use 'General Date', which formats as M-D-YYYY H:MM:SS TT (example 5-13-2008 3:17:35 PM), however because of the awkward format that Date() gives, I think the time is lost.

There's not much documentation on Flex date/time stuff, nor are there examples using access db's. I hope this is just a problem with access though, as I plan on using SQL for our bigger apps.

Flex passes back to a cfc? This gives you some options. The easiest, which only works if you want the current date and time is to use the MS Access function that returns that.

Slightly more difficult is to parse the string you get back from flex using cold fusion functions.

Report · May 13, 2008

EvolvedDSM wrote:
> I appreciate the help Phil.
>
> Ideally, Dan is correct above in that I should use date-type columns for
> dates. Using date-type values, I have more options for manipulating my data in
> coldfusion.
>
> My real issue is that I cannot pass Date() data to my access db without losing
> the time component. You see, Date() in Flex gives me the current day's date in
> this format: Tue May 13 15:17:35 GMT-0400 2008
>
> When I pass this to my db, it stores at 5-13-2008 and loses the time
> completely. Then when I call this column, I get 5-13-2008 00:00:00 AM. I have
> tried altering the mask format of the field to use 'General Date', which
> formats as M-D-YYYY H:MM:SS TT (example 5-13-2008 3:17:35 PM), however because
> of the awkward format that Date() gives, I think the time is lost.
>
> There's not much documentation on Flex date/time stuff, nor are there examples
> using access db's. I hope this is just a problem with access though, as I plan
> on using SQL for our bigger apps.
>

Have you tried the formatODBCDateTime() function which can help getting
date objects correctly into a database.