How to extract Month from start date

[vanir88]

Hi 

i have column as [start date ] , and i have to extract [month]  as a separate column and [quarter] as a separate column to use as filter, could you please help

In [start date] column records are like below format

  2023-01-01T08:50:59Z

 2023-01-23T09:49:51Z

i have to add this in script.

[vikasmahajan · Accepted solution]

hi,

you can try like this :

Month(floor([start date] )) as Month,

'Q' & alt(if(Month(floor([start date] ))<4,4),if(Month(floor([start date] ))<7,1),if(Month(floor([start date] ))<10,2),3) AS Quarter

in your script.

Vikas

[MayilVahanan]

Hi

If your data is in string format, try like below

Load *, Month(Date) as Month, 'Q'&Ceil(Month(Date)/4) as Quarter;
LOAD *, Date(Date#(Left(SampleDate, 10), 'YYYY-MM-DD')) as Date INLINE [
SampleDate
2023-01-01T08:50:59Z
2023-01-23T09:49:51Z
];

[vanir88]

yes , this is working, but jan month taking it as Q4.

but we need  like below.

IF(3,4,5) then Q3

IF(6,7,8) then Q4

IF(9,10,11) then Q1

IF(12,1,2) then Q2

[MayilVahanan · Accepted solution]

Oh Quarter starts differently? You can alter based on ur requirement.

Load *, Month(Date) as Month, 'Q'&If(Match(Num(Month(Date)), 3,4,5), '3', If(Match(Num(Month(Date)), 6,7,8), '4', If(Match(Num(Month(Date)), 9, 10, 11), '1', '2'))) as Quarter;
LOAD *, Date(Date#(Left(SampleDate, 10), 'YYYY-MM-DD')) as Date INLINE [
SampleDate
2023-01-01T08:50:59Z
2023-01-23T09:49:51Z
];