topic Re: Pull Year from a Date in QlikView

Pull Year from a Date

fanninam — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to pull only the year from this date. The date format is: 01/01/1947 0:00:00

What code statement would I use to pull only the year?

Re: Pull Year from a Date

Frank_Hartmann — Mon, 03 Jul 2017 15:20:52 GMT

try like that

year(Date('01/01/1947 0:00:00','MM/DD/YYYY h:mm:ss')) or

year(Date(YourDatefield,'MM/DD/YYYY h:mm:ss'))

hope this helps

Re: Pull Year from a Date

juan_patrick — Mon, 03 Jul 2017 15:22:04 GMT

Hi Amber,

Year function:

year ‒ QlikView

¡Good luck!

Re: Pull Year from a Date

Anil_Babu_Samineni — Mon, 03 Jul 2017 15:26:00 GMT

How about this?

Load ..., Year(Date) as Year from Table where Year(Date) = 1947;

Re: Pull Year from a Date

fanninam — Mon, 03 Jul 2017 15:31:46 GMT

I've tried all of these below and it's not pulling anything. I'm getting confused.

Re: Pull Year from a Date

fanninam — Mon, 03 Jul 2017 15:33:21 GMT

Could it be the AM that is throwing it off? If so, how would I include the AM portion to the code statement?

1/14/1953 12:00:00 AM

Re: Pull Year from a Date

Anil_Babu_Samineni — Mon, 03 Jul 2017 15:42:43 GMT

You can define it as tt for AM and TT for PM ??

Re: Pull Year from a Date

Frank_Hartmann — Mon, 03 Jul 2017 16:27:47 GMT

can u prepare and upload a sample qvw including your date field?

Re: Pull Year from a Date

juan_patrick — Mon, 03 Jul 2017 16:31:18 GMT

Try this:

year(floor(YourDatefield))

Re: Pull Year from a Date

juan_patrick — Mon, 03 Jul 2017 16:32:55 GMT

Another thing,

Where are you doing this? in the load where you connecto to the DB? If yes, do it from a resident table!

Re: Pull Year from a Date

vishsaggi — Mon, 03 Jul 2017 16:45:29 GMT

Try this?

LOAD *,

Year(DateField2) AS YearField;

LOAD *,

Date(DateNum, 'MM/DD/YYYY') AS DateField2

LOAD field1,

field2,

Num(Floor(YourdateField)) AS DateNum

FROM yoursource;

Re: Pull Year from a Date

rwunderlich — Mon, 03 Jul 2017 18:30:16 GMT

Frank,

I think you meant:

year(Date#('01/01/1947 0:00:00','MM/DD/YYYY h:mm:ss')) or

year(Date#(YourDatefield,'MM/DD/YYYY h:mm:ss'))

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Pull Year from a Date

Frank_Hartmann — Tue, 04 Jul 2017 06:57:18 GMT

Hi Rob,

thank you for your hint.

But can you explain me why my proposed expression works in the attached qvw without using #?

I tried this expression in one of the former opened qvw´s from the forum

After your response i tried it in a new qvw and its not working now!

Re: Pull Year from a Date

PrashantSangle — Tue, 04 Jul 2017 07:02:48 GMT

Hi,

Date#() use to convert date from string to date

Date() is use to format existing date

If your datefield value is store in string then you required 1st convert it to date i.e. using date#() to date

Regards,

Prashant

Re: Pull Year from a Date

Frank_Hartmann — Tue, 04 Jul 2017 07:13:33 GMT

Yes, but in the attached qvw '01/01/1947 0:00:00' is obviously a string.

and the expression works without using #. When i open a new qvw its not working anymore

and the question is why its working in one qvw but not in the other qvw?

Re: Pull Year from a Date

PrashantSangle — Tue, 04 Jul 2017 07:19:48 GMT

Can you post both qvw where it is working and where it is not working.

Regards,

Re: Pull Year from a Date

Frank_Hartmann — Tue, 04 Jul 2017 07:21:44 GMT

see attached files

Re: Pull Year from a Date

Frank_Hartmann — Tue, 04 Jul 2017 07:24:40 GMT

i know now why. its because of this line in script

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

versus

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

Re: Pull Year from a Date

PrashantSangle — Tue, 04 Jul 2017 08:52:25 GMT

Well great to heard that...

Re: Pull Year from a Date

Colin-Albert — Tue, 04 Jul 2017 10:50:14 GMT

Here are a few useful blog posts on how Qlik interprets dates...

Why don’t my dates work?

Get the Dates Right

The Date Function

QlikView Date fields