Date feild Query

[Raghuvir]

Hi All,

i have a data item [Hire Date Calc] with the below expression.

cast(extract(day,[Hire date]) as varchar(2))+'/'+cast(extract(month,[Hire date]) as varchar(2))+'/'+cast(extract(year,current_date) as varchar(4))

this gives me the output as 15/12/2014.

my requirement is, i want to subtract one year from this so that i get the output as 15/12/2013.

Request you to guide me to achieve this.

Regards

[Raghuvir]

i used to below expression to convert it to date

cast(cast(extract(day,[Hire date]) as varchar(2))+'/'+cast(extract(month,[Hire date]) as varchar(2))+'/'+cast(extract(year,current_date) as varchar(4)),date)

but its throwing up the following error.

An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.

UDA-SQL-0144 An arithmetic exception was detected.UDA-EE-0029 A date/time value is invalid.UDA-SQL-0460 A general exception has occurred during local processing.UDA-CUR-0000 Unable to fetch the row.RSV-SRV-0042 Trace back:RSReportService.cpp(779): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(265): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(788): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(224): QFException: CCL_CAUGHT: RSASyncExecutionThread::run(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(838): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(606): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(277): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(77): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(344): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(176): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(294): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(300): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(294): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(173): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(294): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(135): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(344): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardRSResultSetIterator.cpp(1665): QFException: CCL_RETHROW: RSResultSetIterator::retrieveDataQFSPartialDataset.cpp(81): QFException: CCL_RETHROW: QFSPartialDataset::GetEdgeIteratorSource/QEDmResultSet.cpp(75): QFException: CCL_THROW: QE

[MFGF]

Hi All,

i have a data item [Hire Date Calc] with the below expression.

cast(extract(day,[Hire date]) as varchar(2))+'/'+cast(extract(month,[Hire date]) as varchar(2))+'/'+cast(extract(year,current_date) as varchar(4))

this gives me the output as 15/12/2014.

my requirement is, i want to subtract one year from this so that i get the output as 15/12/2013.

Request you to guide me to achieve this.

Regards

Hi,

Why are you doing this? Wouldn't it be far easier to simply use the desired date format in the Data format property of the item? Then you could use the _add_years() function to subtract a year from the date...

MF.

[Raghuvir]

Hi MFGF,

i have not prepared a layout yet. Working with the queries only.

in the data item [Hire Date Calc] i am using the Year from the system date, thats why trying to do it this way.

Regards

[MFGF]

Hi MFGF,

i have not prepared a layout yet. Working with the queries only.

in the data item [Hire Date Calc] i am using the Year from the system date, thats why trying to do it this way.

Regards

Wouldn't you just add a filter with the expression extract(year, [your date item]) = extract(year, current_date()) to bring in the current year? You could also subtract 1 from this to get last year :

extract(year, [your date item]) = extract(year, current_date()) - 1

It just seems to me like you are making life hugely complicated for yourself by doing theings the way you posted originally...

MF.

[Raghuvir]

Hi MFGF,

Maybe i just posted part of my requirement, apologies for the same.

I have a data item [Hire Date] which consists on hire dates of employees. for eg Dec 4,1988

there are data items [timesheet start date[ and [time sheet end date] which consists of the timesheet cycle which is semi monthly for e.g. start date Dec 1,2014 and end date Dec 15,2014. i have to find out employees having aniversary within this date range and also find out the total months worked from date hired and the hours entered for last one year and calculate the accrual (calculation of accrual rate i have figured out)

I am stuck with the date part.

Regards

[MFGF]

Hi MFGF,

Maybe i just posted part of my requirement, apologies for the same.

I have a data item [Hire Date] which consists on hire dates of employees. for eg Dec 4,1988

there are data items [timesheet start date[ and [time sheet end date] which consists of the timesheet cycle which is semi monthly for e.g. start date Dec 1,2014 and end date Dec 15,2014. i have to find out employees having aniversary within this date range and also find out the total months worked from date hired and the hours entered for last one year and calculate the accrual (calculation of accrual rate i have figured out)

I am stuck with the date part.

Regards

Wouldn't you just compare the month of the hire date with the timesheet start date month and the day of the hire date with the defined range values to identify your employees?

eg extract(month, [Hire date]) = extract(month, [time sheet start date]) and extract(day, [Hire date]) between extract(day, [time sheet start date]) and extract(day, [time sheet end date])

This ought to give you all employees whose anniversary is within the timesheet cycle.

To get their total months worked from date hired, it would be _months_between([time sheet start date], [Hire date])

You haven't indicated anything about how hours entered are held so I can't give you any suggestions for that :)

MF.

[Raghuvir]

Hi MFGF,

Maybe i just posted part of my requirement, apologies for the same.

I have a data item [Hire Date] which consists on hire dates of employees. for eg Dec 4,1988

there are data items [timesheet start date[ and [time sheet end date] which consists of the timesheet cycle which is semi monthly for e.g. start date Dec 1,2014 and end date Dec 15,2014. i have to find out employees having aniversary within this date range and also find out the total months worked from date hired and the hours entered for last one year and calculate the accrual (calculation of accrual rate i have figured out)

I am stuck with the date part.

Regards

hi MFGF,

The first condition to find out list of employees having aniversary in the date range works perfectly...thanks for that.

i have used prompt to select the timesheet start date and timesheet end date.

regarding the months worked from date hired, it should be the number od months from date hired to the aniversary date.

for e.g hire date is Dec 1,2000 it should calculate no of months worked from Dec 1,2000 to Nov 30,2014
similarly if hire date is Dec 5,1987 it should calculate no of months worked from Dec 5,1987 to Dec 4,2014

could you help me to achieve this...thanks in advance

Regards

[Raghuvir]

how do i convert the below expression to datatype date?

cast(extract(month,[Hire date]),varchar(2))+'-'+cast(extract(day,[Hire date]),varchar(2))+'-'+cast(extract(year,?E_DT?),varchar(4))

Regards

[BigChris]

I would try

cast( cast(extract(month,[Hire date]),varchar(2))+'-'+cast(extract(day,[Hire date]),varchar(2))+'-'+cast(extract(year,?E_DT?),varchar(4)) , date)

[Raghuvir]

I would try

cast( cast(extract(month,[Hire date]),varchar(2))+'-'+cast(extract(day,[Hire date]),varchar(2))+'-'+cast(extract(year,?E_DT?),varchar(4)) , date)

Hi Chris,

i exactly did the same but got the attached error.

Regards

[BigChris]

Hi Raghuvir,

Looking at that error message it suggests that there's a false date in your data. I'd take a look through the data to see if you can spot anything obvious...32nd of the month, 30th of February etc.etc.

[MFGF]

regarding the months worked from date hired, it should be the number od months from date hired to the aniversary date.

for e.g hire date is Dec 1,2000 it should calculate no of months worked from Dec 1,2000 to Nov 30,2014
similarly if hire date is Dec 5,1987 it should calculate no of months worked from Dec 5,1987 to Dec 4,2014

could you help me to achieve this...thanks in advance

Regards

I wrote originally:

To get their total months worked from date hired, it would be _months_between([time sheet start date], [Hire date])

Wouldn't you simply use the day and month of their hire date and the current year to make their anniversary date?

ie _make_timestamp(extract(year, [time sheet end date]), extract(month, [Hire date]), extract(day, [Hire date]))

If the anniversary day was a day before this, you could use the _add_days() function to find this

eg _add_days(_make_timestamp(extract(year, [time sheet end date]), extract(month, [Hire date]), extract(day, [Hire date])), -1)

You could then use this in your _months_between() function as above?

eg
_months_between(_add_days(_make_timestamp(extract(year, [time sheet end date]), extract(month, [Hire date]), extract(day, [Hire date])), -1), [Hire date])

Does this work for you?

MF.

[Raghuvir]

Hi Raghuvir,

Looking at that error message it suggests that there's a false date in your data. I'd take a look through the data to see if you can spot anything obvious...32nd of the month, 30th of February etc.etc.

Hi Chris,

Thanks for the guidance. i guess the datatype of the data item was not date thats why i was getting those errors. i dont have the rights to access the package right now so was not able to confirm.

Regards

[Raghuvir]

I wrote originally:

Wouldn't you simply use the day and month of their hire date and the current year to make their anniversary date?

ie _make_timestamp(extract(year, [time sheet end date]), extract(month, [Hire date]), extract(day, [Hire date]))

If the anniversary day was a day before this, you could use the _add_days() function to find this

eg _add_days(_make_timestamp(extract(year, [time sheet end date]), extract(month, [Hire date]), extract(day, [Hire date])), -1)

You could then use this in your _months_between() function as above?

eg
_months_between(_add_days(_make_timestamp(extract(year, [time sheet end date]), extract(month, [Hire date]), extract(day, [Hire date])), -1), [Hire date])

Does this work for you?

MF.

Hi MFGF,

You are my saviour  :) thanks for the patience and ultimate guidance. i am almost near to complete the requirement...currently working to complete it.

Really appreciate your help.

Regards