topic Re: Get Year/Month/Day from date time feild in QlikView

Get Year/Month/Day from date time feild

anuradhaa — Tue, 25 Mar 2014 06:19:58 GMT

Hi ,

I have a column that has date time.

"3/8/2011 10:56:00 AM"

now i want to get year,month and day separately.

i can get date by using

date#(subfield([Created at],' ',1),'dd.MM.yyyy') as MyDate,

but i cant seperate year,month and day.

Pls help

Thank you

Re: Get Year/Month/Day from date time feild

PrashantSangle — Tue, 25 Mar 2014 06:22:37 GMT

Hi,

Use Day(dateFieldName) as Day,

Month(dateFieldName) as Month,

Year(dateFieldName) as Year

at script level

Regards

Re: Get Year/Month/Day from date time feild

sushil353 — Tue, 25 Mar 2014 06:24:14 GMT

Try Below:

Day([Created at]) as Day,

Month([Created at]) as Month,

Year ([Created at]) as Year,

HTH

Sushil

Re: Get Year/Month/Day from date time feild

Tue, 25 Mar 2014 06:25:05 GMT

Hi,

Use below code:

Year(Date(date#(subfield([Created at],' ',1),'dd.MM.yyyy')))

Month(Date(date#(subfield([Created at],' ',1),'dd.MM.yyyy')))

Day(Date(date#(subfield([Created at],' ',1),'dd.MM.yyyy')))

Regards

Ankur

Re: Get Year/Month/Day from date time feild

its_anandrjs — Tue, 25 Mar 2014 06:25:58 GMT

Hi,

Extract like

Year = Right(MyDate,4) as Year

Month = Mid(MyDate,4,2) as Month

Day = Left(MyDate,2) as Day

Hope this helps

Thanks & Regards

Re: Get Year/Month/Day from date time feild

anuradhaa — Tue, 25 Mar 2014 06:31:49 GMT

Thank you ,

but i have months with one digit and two digit.

Then this will not 100% work.

Thanks

Re: Get Year/Month/Day from date time feild

its_anandrjs — Tue, 25 Mar 2014 06:39:10 GMT

Hi,

You have to write some thing below in place of dots you have to use / and extract like this

date(date#(subfield(Datecol,' ',1),'d/M/yyyy'),'d/M/yyyy') as MyDate,

Year(date#(subfield(Datecol,' ',1),'d/M/yyyy')) as Year,

Month(date#(subfield(Datecol,' ',1),'d/M/yyyy')) as Month,

Day(date#(subfield(Datecol,' ',1),'d/M/yyyy')) as Day

Hope this helps

Thanks & Regards

Re: Get Year/Month/Day from date time feild

anuradhaa — Tue, 25 Mar 2014 06:40:41 GMT

date#(subfield([Created at],' ',1),'dd.MM.yyyy') as MyDate,

subfield(MyDate,'/',1) as month,

subfield(MyDate,'/',2) as day,

subfield(MyDate,'/',3) as Year solve my issue.

Thank you very much

Re: Get Year/Month/Day from date time feild

sushil353 — Tue, 25 Mar 2014 06:42:45 GMT

Hi,

You can try this as well

=Date(Date#('3/8/2011 10:56:00 AM','DD/MM/YYYY h:mm:ss TT'),'MMM')

=Date(Date#(DateField,'DD/MM/YYYY h:mm:ss TT'),'MMM') as Month

=Date(Date#(DateField,'DD/MM/YYYY h:mm:ss TT'),'DD') as Day

=Date(Date#(DateField,'DD/MM/YYYY h:mm:ss TT'),'YYYY') as Year

Here make sure that format define inside Date# should exactly match with the format in the datefield.

HTH

Sushil

Re: Get Year/Month/Day from date time feild

jagan — Tue, 25 Mar 2014 06:45:58 GMT

Hi,

Try like this

Data:

Load

Year(MyDate) AS Year,

Month(MyDate) AS Month,

Day(MyDate) AS Day;

Load

Date(date#(subfield([Created at],' ',1),'D/M/YYYY')) as MyDate,,

From DataSource;

Hope this helps you.

Regards,

jagan.

Re: Get Year/Month/Day from date time feild

its_anandrjs — Tue, 25 Mar 2014 06:46:37 GMT

Hi,

For month you can write this way also suggest you dont use subfield for final date because it is in string format try to convert this into date format see below script for month and also i provided you for month, year.

Mid(Date((date#(subfield(Datecol,' ',1),'d/M/yyyy')),'DD/MM/YYYY'),4,2) as NewMonth

it gives only 1 or 2 month values

Hope this helps

Thanks & Regards

Re: Get Year/Month/Day from date time feild

jagan — Tue, 25 Mar 2014 06:47:18 GMT

Hi,

If you use like this then it is considered as Text fields, while sorting this will create problems

subfield(MyDate,'/',1) as month,

subfield(MyDate,'/',2) as day,

subfield(MyDate,'/',3) as Year

Regards,

Jagan.

Re: Get Year/Month/Day from date time feild

CELAMBARASAN — Tue, 25 Mar 2014 06:57:46 GMT

Hi,

Instead of subfield you could use

Num(Month(MyDate), '00') as month,

Num(Day(MyDate), '00') as day,

Year(MyDate) as Year

Re: Get Year/Month/Day from date time feild

MK_QSL — Tue, 25 Mar 2014 07:08:38 GMT

Considering that you have more than one timestamp format in your apps, you can use below method in script to get Date, Month, Year etc from your field...

In my apps, Date,time format is using '/' separator..

You can change as per your apps.

=================================================

Load

Date(DateTimeField) as Date,

Day(DateTimeField) as Day,

Month(DateTimeField) as Month,

Year(DateTimeField) as Year,

Time(DateTimeField) as Time,

Hour(DateTimeField) as Hour,

Minute(DateTimeField) as Minute,

Second(DateTimeField) as Seconds;

Load

TimeStamp(Alt(Timestamp#(DateTimeField,'D/M/YYYY hh:mm:ss TT'),

Timestamp#(DateTimeField,'DD/MM/YYYY hh:mm:ss TT'),

Timestamp#(DateTimeField,'DD/M/YYYY hh:mm:ss TT'),

Timestamp#(DateTimeField,'D/MM/YYYY hh:mm:ss TT'))) as DateTimeField

Inline

[

DateTimeField

3/8/2011 10:56:00 AM

04/08/2011 11:10:00 AM

5/08/2011 11:11:11 AM

06/8/2011 11:12:12 AM

];