Date Format

[anuradhaa]

I have a Data set which has "Time" as "17-SEP-12 01.08.47.530179000 AM"

So i need to divide taht time to,

Year

Month

Day

Hours

minutes

Please help.

At least tell me how to devide it in to Year,Month,Day

Thanks

[#2]

Hi Anuradhaa,

Try this format.

          '20' & mid(Time,8,2) as Year,

          mid(Time,4,3) as Month,

          mid(Time,1,2) as Day,

          mid(Time,11,2) as Hour,

          mid(Time,14,2) as Minute

Best Regards,

Janzen

[anuradhaa]

Not works.

but i have done some modifications to your code then Hour and minute is ok, but others have problem.

This is my modified code ,

mid(Time,1,2) as Hour,

          mid(Time,4,2) as Minute,

I think this fuction ignores first part , take the data after the space

Thanks

[#4]

Hi,

If you use your data as string, It should display proper values. Using your modified code, mid(Time,1,2) will display '17' which I thought is a day, mid(Time,4,2) will display 'SE'.

May I know what is the format of your data?

Thanks,

Janzen

[anuradhaa]

In the Data Base it appiers as "TIMESTAMP(6)"

But i have expot the data to excel and use that excel in qlikview,

The data type in excel is "General"

Thanks

[jonathandienst]

Hi

First convert the value to a date/time and use the standard QV functions for the date parts. In a load statement you could do this (assuming Time is the name of the timestamp field):

LOAD Month(Date) As Month,

     Year(Date) As Year

     Day(Date) as Day,

     Hour(Date) As Hour,

     Minute(Date) As Minute;

LOAD Date(Date#(Time, 'DD-MMM-YY hh.mm.ss.fffffffff tt')) As Date,

     ...

     ...

FROM ...;

Hope that helps

Jonathan

[anuradhaa]

where do i need to change the data type?

DB or excel?

Is there a way to do it by using data type as "Time Stamp" in excel.

Thanks

[hic]

You do not need to change the data type. To QlikView this is a string that needs to be interpreted. Just use the time interpretation function that Jonathan suggests.

HIC

[anuradhaa]

Thanks,

But i was confused, have problems in put that code

here is my code, please help me to put that in side here

LOAD MACHINE_NAME,

     IP,

     CMD,

     HEADER,

     LABLE,

     STATUS,

     TIME,

     if(STATUS='GREEN',1,0)as UP

FROM

.......

[#10]

Hi,

May I ask for your sample data.

Thanks,

Janzen