asked on

IDC, HTX(Ms access)

Question- formatting idc returned variables?

I am wondering whether there's any way to format, the returned variable's from the database(Ms access, IIS).
example.. if the returned var is %start date% and when I print it, it looks like 1899:12:12 14:34:23

is there any way to print only the 14:34:23 part
does anyone know SQL code to accomplish this?
I know that ltrim and rtrim does it in SQL, But how do i write that in idc-sql format?

ASKER CERTIFIED SOLUTION

nodas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anusha

ASKER

Thanks for helping out.
But you answer didn't quite work .(its giving me query errors)
Can you help ?

here's my actual idc file.

Datasource: HELP_DSK
Template: try.htx
DefaultParameters: Caller Name=%%
SQLStatement:
+Select "Ticket Number", "Department", "Caller ID", "Caller Name", FORMAT(Start Date,"hh:nn:ss") AS NewDate, "Start Time", "Problem Description", "Problem Resolution", "Severity", "Status", "Close Date", "Close Time", "Request Type", "AssignTo" +From "Table - Help Desk"
+Where "Caller Name" LIKE '%Caller Name%%'
#IDC-Search FrontHTM-try.htm ReportHTX-try.htx

also i tried all possible combinations of
FORMAT(Start Date,"hh:nn:ss") AS NewDate
FORMAT("Start Date","hh:nn:ss") AS "NewDate"

none worked!

nodas

Sorry anusha, You're right
The following works fine (I've tested):
Format$([StartDate], 'hh:nn:ss') AS NewDate

...nodas

anusha

ASKER

Nodas..

I tried this:
Format$([StartDate], 'hh:nn:ss') AS NewDate

But I am not getting any results from it.. as in the it prints an empty space!(atleast it doesn't give me query errors now)

Can u help please?
Thanks!
Anusha.

nodas

Just one question :
Does your .htx file contains Newdate variable ? :

Try.htx
-------
<HTML><HEAD>
...
Ticket Number : <%Ticket Number%> <BR>
DepartmentDate: <%DepartmentDate%><BR>
...
Date : <%Newdate%>
...
</Body></Html>

TIME FORMAT (from Access Help)
h      Hour in one or two digits, as needed (0 to 23).
hh      Hour in two digits (00 to 23).
n      Minute in one or two digits, as needed (0 to 59).
nn      Minute in two digits (00 to 59).
s      Second in one or two digits, as needed (0 to 59).
ss      Second in two digits (00 to 59).

Hope this helps !

anusha

ASKER

Thanks Nodas :-)

Ur answer worked! I really appriciate it