Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhavvibudagam
Creator II
Creator II

date from Timestamp

Hi Experts,

Can any one please help me on this.

I have a date field like below in Time stamp format.

How to extract the Date, Quarter and Monthyear from below field.

Date.png

Thanks in advance

1 Solution

Accepted Solutions
pradosh_thakur
Master II
Master II

if not in date format use this

tablename1:

load timestamp#(date,'DD/MM/YYYY hh:mm')) AS Date

from table_name;


Load 

               floor(Date) as Date, 

               week(floor(Date)) As Week, 

               Year(floor(Date)) As Year, 

           Month(floor(Date)) As Month, 

               Day(floor(Date)) As Day, 

           date(monthstart(floor(Date)), 'MMM-YYYY') as MonthYear, 

               'Q' & Ceil(Month(floor(Date) / 3) AS Quarter, as Quarter, 

               Week(weekstart(floor(Date))) & '-' & WeekYear(floor(Date)) as WeekYear, 

               WeekDay(floor(Date)) as WeekDay 

resident table_name1;

Learning never stops.

View solution in original post

6 Replies
kakani87
Specialist
Specialist

Export this date Field with timestamp into an excel and share

parthesh
Creator
Creator

Hello,

try this

Date#('12/02/2018','dd/mm/yyyy')

QuarterName(Date#('12/02/2018','dd/mm/yyyy'))

Month(Date#('12/02/2018','dd/mm/yyyy'))

Year(Date#('12/02/2018','dd/mm/yyyy'))

pradosh_thakur
Master II
Master II

if date is already in date format use this


Load 

               floor(Date) as Date, 

               week(floor(Date)) As Week, 

               Year(floor(Date)) As Year, 

           Month(floor(Date)) As Month, 

               Day(floor(Date)) As Day, 

           date(monthstart(floor(Date)), 'MMM-YYYY') as MonthYear, 

               'Q' & Ceil(Month(floor(Date) / 3) AS Quarter, as Quarter, 

               Week(weekstart(floor(Date))) & '-' & WeekYear(floor(Date)) as WeekYear, 

               WeekDay(floor(Date)) as WeekDay 

from table_name;

Learning never stops.
pradosh_thakur
Master II
Master II

if not in date format use this

tablename1:

load timestamp#(date,'DD/MM/YYYY hh:mm')) AS Date

from table_name;


Load 

               floor(Date) as Date, 

               week(floor(Date)) As Week, 

               Year(floor(Date)) As Year, 

           Month(floor(Date)) As Month, 

               Day(floor(Date)) As Day, 

           date(monthstart(floor(Date)), 'MMM-YYYY') as MonthYear, 

               'Q' & Ceil(Month(floor(Date) / 3) AS Quarter, as Quarter, 

               Week(weekstart(floor(Date))) & '-' & WeekYear(floor(Date)) as WeekYear, 

               WeekDay(floor(Date)) as WeekDay 

resident table_name1;

Learning never stops.
kakani87
Specialist
Specialist

use this in script and front end format in Number tab as date

Properties > Number Tab > Over ride > check Date format



Date:

LOAD Date

FROM

(biff, embedded labels, table is Sheet1$);


Load


               date(floor(Date)) as Date,

               Year(floor(Date)) As Year,

              Month(floor(Date)) As Month,

              Day(floor(Date)) As Day,

              date(monthstart(floor(Date)), 'MMM-YYYY') as MonthYear,

              'Q' & Ceil(Month(floor(Date) / 3)) AS Quarter 


             


Resident Date;

kakani87
Specialist
Specialist

In the script replace

date(floor(Date)) as Date with Date(Date, 'MM/DD/YY') as Dt

not even required to format in front end