Data formatting in cfgrid

[Anj01]

Hi

I am currently pulling data into a cfgrid. One of the fields is date. I want
to format the date , but how can I specify the format I want it to be displayed
in the grid
This is what my code for date looks like
<CFGRIDCOLUMN NAME="LDate" HEADERALIGN="LEFT" DATAALIGN="LEFT" SELECT="No"
DISPLAY="Yes" >
where can I specify the format

Thanks
Anj

[paross1]

If you are using a query to populate your CFGRID, then you may consider
changing the format of the date there by converting the date column value to a
character string. The particular function used would depend on the particular
database, such as TO_CHAR() with Oracle, or CONVERT() with SQL Server, FORMAT()
with Access, etc. Of course, if you do change a date/time data type to a
character type, then it may affect sorting by that column within the CFGRID.
Also, if you are using a stored procedure to populate the CFGRID, then you
would have to make the change there.

Phil

[Anj01]

Thanks Phil

I tried doing the same in the query. The Mid function works fine in access but
I dont know how to do the same in SQL
I tried the query
Select Mid(Date,5,2)+Mid(Date,7,2)+Mid(Date,1,4) from ZZZ
this works well in MS Access, but SQL Does not recognise Mid
Can you suggest what I can use here

Thanks a lot for your help
Anj

[paross1]

The SUBSTRING() function will allow you to select particular portions of a
string. CONVERT() will allow you to convert a datetime column to a varchar of a
given format.

CONVERT(varchar, Date_col, 101) will display a date in the mm/dd/yyyy format,
so you would see something like 03/31/2005. If you wanted just the numbers
without the / characters, then you could use SUBSTRING to pull out the various
fields, such as SUBSTRING(CONVERT(varchar, Date_col, 101), 1,
2)+SUBSTRING(CONVERT(varchar, Date_col, 101), 3, 2)+SUBSTRING(CONVERT(varchar,
Date_col, 101), 7, 4). Function DATEPART() wil also allow you to extract parts
of a datetime column, but you still would have to go through the concatenation
process to put the parts together.

Phil

[Anj01]

Thanks Phil

I have been able to format the data , but it cant be done in a SQL statement.
The data has to be converted and manipulated after a few checks.

Is there a way to do the formatting before the parameters are called in the
cfgrid
The cfgrid tag specifies the parameters from the cfquery but does not have any
method to place user defined function formatted parameters

Do you have any idea how this can be done

Thanks
Anj01

[paross1]

I'm not sure if I am picturing your problem clearly. Maybe you could include some of your code for us to get an idea as to what you have done so far.

[Anj01]

Hi

As of now I am pulling data into a cfgrid with the query

<cfquery name="GetInfo" >
SELECT Providers.ProviderName, Schedules.LDate, Events.ActualArriveTime AS
ActPUTime,Events.Esttime AS SchedTime, Events_1.ActualArriveTime AS
ActDropOffTime , Events.BookingId
FROM (((((Events INNER JOIN Schedules ON Events.SchId = Schedules.SchId)
INNER JOIN Events AS Events_1 ON (Schedules.SchId = Events_1.SchId) AND
(Events.BookingId = Events_1.BookingId)) INNER JOIN EventStrings ON
(Schedules.SchId = EventStrings.SchId) AND (Events.EvStrId =
EventStrings.EvStrId)) INNER JOIN Clients ON Events.ClientId =
Clients.ClientId) INNER JOIN BookingLegs ON Events.LegId = BookingLegs.LegId)
INNER JOIN Providers ON EventStrings.ProviderId = Providers.ProviderId
WHERE (((Providers.ProviderName)='#session.UserName#') AND
((Schedules.LDate)=#SchDate#) AND ((Events.Activity)=0) AND
((Events_1.Activity)=1))
ORDER BY [EvStrName]+' ', SchedTime

</cfquery>


once I establish the grid and specify the columns
<CFGRID NAME="TransitGrid"
WIDTH="95%" height="350px"
QUERY="GetInfo"
SELECTMODE="EDIT" SELECTCOLOR="Red"
SORT="Yes"
SORTASCENDINGBUTTON="Sort ASC"
SORTDESCENDINGBUTTON="Sort DESC">

<CFGRIDCOLUMN NAME="ActPUTime" HEADER="Act PU Time"
HEADERALIGN="LEFT" DATAALIGN="LEFT"
SELECT="Yes" DISPLAY="Yes" >

Now I cant format any time fields , I always get an error that It is not
indexable

The query get the SchedTime which is in seconds
This needs to be displayed in a more user friendly format like hhmm or any
other time formats
Also the user enters time in hhmm format
this needs to be formatted and stored in seconds
like 0500 is 5 A.M and is stored as 18000
Also if there was no time specified then the default for time is -1

I am presently formatting the seconds into hhmm by :

<cfif #ActPUTime# eq "-1">
<cfset PUTime = #ActPUTime#>
<cfelse>
<cfset HrSec = int(int(#ActPUTime#)/3600)>
<cfset MinSec = int((int(#ActPUTime#) -(#HrSec#*3600))/60)>
<cfset PUTime = #HrSec#*100+#MinSec#>
<cfif #PUTime# LT 1000>
<cfset variables.PUTime = ToString(#PUTime#)>
<cfset variables.PUTime = "0"&variables.PUTime>
</cfif>
</cfif>

Can I use the cfgrid , since this was the most convenient way to make updates
to multiple rows

Thanks for all the help

[paross1]

It is still unclear to me why you couldn't format it as a VARCHAR in the QUERY.

[Anj01]

How do I format it in the query.... need help here

[paross1]

What is the actual data type of the Events.ActualArriveTime column in the database? Is it a datetime, smalldatetime, varchar, integer, etc.?

Phil

[Anj01]

The datatype for time is int

Anj

[paross1]

I'm curious why you are using an int column for a "time" value? It appears that
this column represents seconds since midnight, but storing this as an integer
representing seconds causes all sorts of conversion headaches, as you are
seeing here. Why couldn't you use a datetime data type, and then just retrieve
the part that you need, like hours, minutes, etc.?

Also, if you intend on using CFGRIDUPDATE, you are going to have problems if
you perform data type conversions within the query driving the CFGRID, and then
want to change the value of the column via an UPDATE in CFGRIDUPDATE.

Phil

[Anj01]

How i wish that the time wasnt int type. I cant change it since this is the
database from a software application supplied. We are tryin to develop a small
web module to view some of the data. So I wanted to use cfgrid to madke my life
easier.
Well I guess nothing comes easy. So in light of this Ive changed it from grid
to pulling data and looping through it in html
Only catch now is that only one row can be updated. I dont know how to update
multiple rows simultaneously

Anywaz thanks, you have been a gr8 help
-Anj

[FENAUGHTY]

Try using <CFGRIDCOLUMN NAME='LDate' HEADERALIGN='LEFT' DATAALIGN='LEFT' SELECT='No' DISPLAY='Yes' MASK='dd/mm/yyyy'>

Hope this is what your after

Adam