Saturday, March 8, 2008

ITM 6.1 DW Timestamps ' Updated

If you read my previous poston DW TimeStamps, "ITM 6.1 DW Timestamps '?What's Up", I was complaining about creating reports using the CTIME timestamps in the ITM 6.1 DW. It looks like I found some nose plugs.

I was poking around in the TEP looking at some of the ITM supplied custom queries and I noticed a function called DATEFORM. I couldn't find any documentaion on it but I played with it from WinSQL and found it to be very useful. Here is an example:

select "Server_Name", "ITMUSER".DateForm(WRITETIME,'D') "(YYYY/MM/DD)"
from "NT_Processor_MV"

Where the DateForm function converts the CTIME format into a YYYY/MM/DD format. I played around with the flags and found the following:

DateForm(xxx,'Y')???formats as '?YYYY

DateForm(xxx,'M')???formats as '?YYYY/MM

DateForm(xxx,'D')???formats as '?YYYY/MM/DD

DateForm(xxx,'H')???formats as '?YYYY/MM/DD HH:MM

I could not figure out how to format as YYYY/MMM/DD HH:MM:SS but I can live with that for now.

No comments: