Get Year/Month/Day from date time feild

[anuradhaa]

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

[PrashantSangle]

Hi,

Use Day(dateFieldName) as Day,

Month(dateFieldName) as Month,

Year(dateFieldName) as Year

at script level

Regards

[sushil353]

Try Below:

Day([Created at]) as Day,

Month([Created at])  as Month,

Year ([Created at]) as Year,

HTH

Sushil

[#4]

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

[its_anandrjs]

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

[anuradhaa]

Thank you ,

but i have months with one digit and two digit.

Then this will not 100% work.

Thanks

[its_anandrjs]

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

[anuradhaa]

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

[sushil353]

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

[jagan]

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.