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

[its_anandrjs]

Hi,

For month you can write this way also suggest you dont use subfield for final date because it is in string format try to convert this into date format see below script for month and also i provided you for month, year.

Mid(Date((date#(subfield(Datecol,' ',1),'d/M/yyyy')),'DD/MM/YYYY'),4,2) as NewMonth

it gives only 1 or 2 month values

Hope this helps

Thanks & Regards

[jagan · Accepted solution]

Hi,

If you use like this then it is considered as Text fields, while sorting this will create problems

subfield(MyDate,'/',1) as month,

  subfield(MyDate,'/',2) as day,

  subfield(MyDate,'/',3) as Year

Regards,

Jagan.

[CELAMBARASAN]

Hi,

Instead of subfield you could use

  Num(Month(MyDate), '00') as month,

  Num(Day(MyDate), '00') as day,

  Year(MyDate) as Year

[MK_QSL]

Considering that you have more than one timestamp format in your apps, you can use below method in script to get Date, Month, Year etc from your field...

In my apps, Date,time format is using '/' separator..

You can change as per your apps.

=================================================

Load

  *,

  Date(DateTimeField) as Date,

  Day(DateTimeField) as Day,

  Month(DateTimeField) as Month,

  Year(DateTimeField) as Year,

  Time(DateTimeField) as Time,

  Hour(DateTimeField) as Hour,

  Minute(DateTimeField) as Minute,

  Second(DateTimeField) as Seconds;

Load

TimeStamp(Alt(Timestamp#(DateTimeField,'D/M/YYYY  hh:mm:ss TT'),

  Timestamp#(DateTimeField,'DD/MM/YYYY  hh:mm:ss TT'),

  Timestamp#(DateTimeField,'DD/M/YYYY  hh:mm:ss TT'),

  Timestamp#(DateTimeField,'D/MM/YYYY  hh:mm:ss TT'))) as DateTimeField

Inline

[

  DateTimeField

  3/8/2011  10:56:00 AM

  04/08/2011  11:10:00 AM

  5/08/2011  11:11:11 AM

  06/8/2011  11:12:12 AM

];