I have a date column that displays date in the following format:
10/13/2011 13:59:49

I would like to extract only month and year from the date. What Date-Time function would work in this situation?

Thank you

This message has been edited. Last edited by: FP Mod Chuck,

Hi Gagan,
The following maybe one of the way to extract the month and year from date-time component.

DEFINE FILE CAR
DATE/HYYMDS WITH CAR= HGETC(10,'HYYMDS');
TRANSDATE_DATE/YYMD = HDATE(DATE, 'YYMD');
YEAR/YY=TRANSDATE_DATE;
MONTH/M=TRANSDATE_DATE;
END
TABLE FILE CAR
PRINT DATE TRANSDATE_DATE MONTH YEAR
BY TOP 1 CAR NOPRINT
END

HDATE converts date-time value to date format.

OR you can use FPRINT and EDIT functions,FPRINT converts date to alphanumeric and EDIT can mask unwanted values.

DEFINE FILE CAR
DATE/HYYMDS= HGETC(10,'HYYMDS');
ALPHA_DATE/A10= FPRINT(DATE,'HYYMDS','A10');
YEAR/A4=EDIT(ALPHA_DATE,'9999$$$$$$');
MONTH/A2=EDIT(ALPHA_DATE,'$$$$$99$$$');
END
TABLE FILE CAR
PRINT DATE MONTH YEAR
BY TOP 1 CAR NOPRINT
END

This message has been edited. Last edited by: Darshan Patil,

Better use DTPART in order to get SQL optimization:

DEFINE FILE ...
YEAR/I4=DTPART(DATETIMECOLUMN,YEAR);
MONTH/I2=DTPART(DATETIMECOLUMN,MONTH);
END

Below should also work with smartdate
DEFINE FILE abc
-* numeric year
THE_YEAR /YY = YourDateField;
-* numeric month
THE_MM /MONTH = YourDateField;
-* Alpha long month mixed case
THE_Month /Mtr = YourDateField;
-* Alpha long month upper case
THE_MONTH /MTR = YourDateField;
-* Alpha short month mixed case
THE_Mth /Mt = YourDateField;
-* Alpha short month upper case
THE_MTH /MT = YourDateField;
END

Checkout hpart or hdate, as mentioned above. Are you using IA or AS? You may be able to get this info at http://localhost:8080/ibi_apphelp/index.jsp, F1 from within AS.

@Gagan Marwah....this is the easiest and best way .....

quote:

Originally posted by MartinY:
Below should also work with smartdate
DEFINE FILE abc
-* numeric year
THE_YEAR /YY = YourDateField;
-* numeric month
THE_MM /MONTH = YourDateField;
-* Alpha long month mixed case
THE_Month /Mtr = YourDateField;
-* Alpha long month upper case
THE_MONTH /MTR = YourDateField;
-* Alpha short month mixed case
THE_Mth /Mt = YourDateField;
-* Alpha short month upper case
THE_MTH /MT = YourDateField;
END

quote:

Originally posted by vinodh:
@Gagan Marwah....this is the easiest and best way

It's easy, but why do you think it's the best way?

quote:

It's easy, but why do you think it's the best way?

... because they have never looked at the SQL trace to see how much of the code is translated to SQL!

T