Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Thanks in advance
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;
Export this date Field with timestamp into an excel and share
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'))
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;
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;
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;
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