topic problem with datetime format from ms sql in QlikView

problem with datetime format from ms sql

Fri, 09 Sep 2011 12:43:06 GMT

this is part of my script:

Calendar:

LOAD

DATEE as dtime,

Month(DATEE) AS Month,

num(Month(DATEE)) AS 'Monthn',

Day(DATEE) AS 'DDay',

Year(DATEE) AS 'YYear',

'K' & Ceil(Month(DATEE)/3) AS 'Quart'

resident TCalendar;

drop table TCalendar;

// Just a calendar

pos:

load po,code,

name as po_name,

date(floor(deliverytime)) as dtime,

sup_name,firstday,lastday,slday,received,

po_mat,po_cont;

sql select pp.po,pp.code,pp.name ,pp.deliverytime // in MS SQL Server 2008 - datetime, values 2011.08.29 12:34 e.g.

then, when my script executed I see some strange numbers in dtime field I expect date, but see number

what's wrong with this?

Fri, 09 Sep 2011 12:49:55 GMT

You might need to replace the '.' with a '/'

date(replace(text(DATEE),'.','/'))

Fri, 09 Sep 2011 14:25:54 GMT

you'll need to wrap your sql with TO_DATE I think

TO_DATE(pp.deliverytime) As deliverytime

hope that helps

thanks

Joe