Can I Update a Query?

scripter73 · Feb 8, 2002

Hi,

I have a query that returns a date to me as a text field in the format "yyyymmdd". It isn't in the date format.

However, I need to do a date compare (based on user selected dates) that are in the format "mm/dd/yyyy".

What I'd like to do is a Query1 that returns the normal database data, and then do a Query2 that updates that date field into the correct format. That way, I can use Query2 to pull accurate records based on a date compare.

I can't update the database, because other people need the date in that format (running AS/400 database).

Can I do this? I think I'm in the right direction with a query within a query, but I'm not sure. My logic in my SQL is a little messed up though.

Here's my code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>Date Compare</title>
</head>

<body>
Get AS/400 dates and compare with form dates.

<cfquery name="Query1" datasource="#dsource#">
select last_name,
first_name,
middle_initial,
tran_date
from PLWEBACH
</cfquery>


<CFLOOP QUERY="Query1">

<CFQUERY NAME="Query2" DATASOURCE="#dsource#">

<cfset achmonth = Mid(tran_date,1,4)>
<cfset achday = Mid(tran_date,5,2)>
<cfset achyear = Mid(tran_date,7,2)>

SELECT last_name, first_name, middle_initial, tran_date
FROM ??PLWEBACH
WHERE
????? I want to update the wach_tran_date to a format #achmonth#/#achday#/#achyear#.

<CFOUTPUT QUERY="Query2">
#last_name#<br>
#first_name#<br>
#middle_initial#<br>
.....updated date here.....<br>
</CFOUTPUT>

</CFLOOP>


</body>
</html>

Any help you can provide is greatly appreciated.

Thanks,
scripter73
Change Your Thinking, Change Your Life.

pigsie · Feb 8, 2002

A few points. If you are using CF5 you can use queries of queries to create a second query out of the first. However I dont think you can modify real queries (i queries from a database)you may be able to modify queries generated from another query. you can however make your own queries using the queryNew function however you would have to add one row at a time and for large queries this may take a while.If you dont need to work on every row at once then this should not be a problem

What you could do is use the create date function:
CreateDate("yyyy","mm","dd&quot

you would have to split the string to pass each arguement to the createdate function but then you would have a valid date which you could use for comparions. you may need to convert it to an odbc date if comparing it to an odbc date.

HTH

KOla

scripter73 · Feb 11, 2002

Thanks pigsie, I'll give it a try.

Change Your Thinking, Change Your Life.

Guest_imported · Feb 11, 2002

You might try experimenting with the to_date() function to see if it wiil make a useful conversion like this:

<cfquery name="Query1" datasource="#dsource#">
select last_name, first_name, middle_initial, tran_date
from PLWEBACH
where to_date(tran_date) > #CreateODBCDate(selected_date)#
</cfquery>

and the CF DateFormat() function,

update PLWEBACH
set tran_date = '#DateFormat(new_date, "yyyymmdd&quot

#'
where ...

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Can I Update a Query?

scripter73

Programmer

pigsie

Programmer

scripter73

Programmer

Guest_imported

New member

Similar threads

Part and Inventory Search

Sponsor