Change dateformat - SQL Query template

[HariCS23]

Hi There, I have a column ( of type datetime)  coming from SQL and it is getting formatted to to XML datetime format . (YYYY-MM-DDThh:mm:ss).

I am feeding results of query template ( XML ) to a front end component which is displaying data.

But i want to format date to different format ? Is there any way to do that on query template level ?

I got  a thought of looping thorough each record and apply transformation using the function datefromxmlformat(<<ipcolumn>>, "MM/dd/yyyy HH:mm:ss") but looping through each record takes toll on performance.

Any idea on how to apply date format transformation with out looping thorough each record ?

(I am not using MII display template,so i can not use 'date and Time Format' setting of display template) .

Thanks

Hari

[Nikhil_Makhija · Accepted solution]

Hi Hari,

How about converting the date column in SQL query template itself . Example below-

SELECT CONVERT(VARCHAR(10), <<ipcolumn>>, 101) AS formatdate from Table

Input : 2014-05-27T14:33:20

Output : 05/27/2014 (You can change number to accomodate any date format)

Nikhil

[HariCS23 · Accepted solution]

Good idea.After converting date column to string,it is not messing up by adding T inbetween date and time components.

Thanks

Hari