To get records from start of month till current date

[former_member610952]

Hi All,

I have a requirement in my project where there is a date column in  a table that needs to extract records from 1st day of the till current date.

For instance, if the job runs on May 10th, all the records from May 1st till May 10th has to be extracted.

Pls help me write a function to meet this requirement.

Sindhura T

[akhileshkiran · Accepted solution]

Hi ,

Assuming that you have a source as shown below.

You need to extract the data from the first month of May. Id's from 7-15.

You can achieve this by two methods. Either by converting the source to date or directly converting the sysdate() to your source format.

Method :1

Query."DATE" >= (to_char( sysdate( ),'YYYYMM')||'01')

output:

Method:2 Converting both to date.

to_date( Query."DATE",'YYYYMMDD') >= to_date( (to_char( sysdate( ),'YYYYMM')||'01'),'YYYYMMDD')

Output:

Hope this will help you out.

Regards,

Akhilesh Kiran.

[former_member610952 · Accepted solution]

Hi Akhilesh Kiran ,

My date field is in the below format-

And the data type is date. The above function still works? Cause i see it doesn't.

[former_member610952 · Accepted solution]

Hi,

I used the below function and it worked-

date_field<= sysdate( ) and date_field >= to_date( to_char(sysdate( ),'YYYY')||to_char(sysdate( ),'MM')||'01','YYYYMMDD')

[akhileshkiran · Accepted solution]

Hi ,

You can use.

Source:

Below Logic: From and To Dates

Mapping :

Query."DATE" >= to_date(to_char( sysdate( ),'YYYY.MM')||'.01','YYYY.MM.DD')
and 
Query."DATE" <= to_date(sysdate( ),'YYYY.MM.DD')

Output:

Regards,

Akhilesh Kiran.

[former_member187605 · Accepted solution]

date_field<= sysdate( ) and date_field > last_date(add_months(sysdate(),-1)

[mageshwaran_subramanian]

date_field >= to_char(sysdate(),'YYYYMM')||'01' AND date_field <= to_char(sysdate(),'YYYYMMDD')

make sure date_field  is in YYYYMMDD format

[former_member610952]

Hi Magesh Subramanian ,

This dint work for me.

My date field is already in format 'yyyymmdd'. Why should i use a to_char function here?