Date Function

Technical support

Moderators: Bob Cergol, Data Access, Cintac

Date Function

Postby WStansbury » Fri Oct 14, 2011 7:34 pm

I have a table with a STATUS field and a STATUS_DATE field. In my Conditions, I want to select all records where STATUS = APPFILED and STATUS_DATE is > 90 days old.

I think I should use the @now() function but I do not know the correct syntax for using it.

Can you help?
WStansbury
 
Posts: 19
Joined: Thu Sep 29, 2011 3:35 am

Re: Date Function

Postby admin » Fri Oct 14, 2011 9:27 pm

If you just want a "permanent" condition, use the conditions tab:
Image
-here you can enter the >now()-90 as a condition on a date field

Best regards

Bo
User avatar
admin
Certified Dynamic AI Professional
 
Posts: 177
Joined: Thu Jan 01, 1970 1:00 am
Location: Copenhagen, Denmark

Re: Date Function

Postby WStansbury » Mon Oct 17, 2011 3:05 am

I tried your equation and got the following error screen
>now()-90

Dynamic AI can't open the list APPFILED Cases > 90 days:

While connecting to the SQL Server database (CLAIMS), a problem arose.

The issue reported from the database is:

now is not a recognized built-in function name
WStansbury
 
Posts: 19
Joined: Thu Sep 29, 2011 3:35 am

Re: Date Function

Postby admin » Mon Oct 17, 2011 7:45 am

For sql server you can use getdate()

Best regards
Bo
User avatar
admin
Certified Dynamic AI Professional
 
Posts: 177
Joined: Thu Jan 01, 1970 1:00 am
Location: Copenhagen, Denmark

Re: Date Function

Postby WStansbury » Mon Oct 17, 2011 7:48 pm

While we are on the subject of dates ---

I want to get the elapsed time from the STATUS_DATE to today's date

I think I would need to create another column called TODAYDATE, @date(), and then create a formula of something like ddiff(d,TODAYDATE - STATUS_DATE)

Tried in a couple of areas and kept on getting SQL errors
WStansbury
 
Posts: 19
Joined: Thu Sep 29, 2011 3:35 am

Re: Date Function

Postby admin » Mon Oct 17, 2011 8:20 pm

You can do something like

cast(getdate()-status_date as integer)

- this will return a whole number of days between the two dates.
Best regards
Bo
User avatar
admin
Certified Dynamic AI Professional
 
Posts: 177
Joined: Thu Jan 01, 1970 1:00 am
Location: Copenhagen, Denmark

Re: Date Function

Postby WStansbury » Mon Oct 17, 2011 11:03 pm

Thank You Mr. Andersen for your accurate and quick responses!!
WStansbury
 
Posts: 19
Joined: Thu Sep 29, 2011 3:35 am


Return to Technical Support

cron