Dates in PS Query
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page Dates in PS Query
(Created page with " PeopleSoft Query surrounds dates and datetimes from the database with a conversion to consistent format. This is similar to the effect of the PeopleCode %DateOut, %TimeOut and %...") |
|||
| Line 1: | Line 1: | ||
| - | + | [[PeopleSoft Query]] surrounds dates and datetimes from the database with a conversion to consistent format. This is similar to the effect of the PeopleCode %DateOut, %TimeOut and %DateTimeOut. The effect of this can be seen when you preview the SQL. | |
| - | PeopleSoft Query surrounds dates and datetimes from the database with a conversion to consistent format. This is similar to the effect of the PeopleCode %DateOut, %TimeOut and %DateTimeOut. The effect of this can be seen when you preview the SQL. | + | |
The date functions are particular to the database in use. For instance in Oracle: | The date functions are particular to the database in use. For instance in Oracle: | ||
| - | to_char(A.EFFDT,'YYYY-MM-DD') | + | <pre>to_char(A.EFFDT,'YYYY-MM-DD')</pre> |
The conversion to char means that the date functions will not work natively. You can convert this back to date by creating an expression as follows: | The conversion to char means that the date functions will not work natively. You can convert this back to date by creating an expression as follows: | ||
| - | to_date(A.EFFDT,'YYYY-MM-DD') | + | <pre>to_date(A.EFFDT,'YYYY-MM-DD')</pre> |
This means that you can then use date functions: | This means that you can then use date functions: | ||
| - | add_months(to_date(A.EFFDT,'YYYY-MM-DD'),-12) | + | <pre>add_months(to_date(A.EFFDT,'YYYY-MM-DD'),-12)</pre> |
Note that DateTimes can be converted differently and so care is needed when mixing data types. | Note that DateTimes can be converted differently and so care is needed when mixing data types. | ||
| + | |||
| + | ==More About Functions== | ||
| + | |||
| + | {| class="wikitable" | ||
| + | |- | ||
| + | |[[File:UsingFunctionsInPeopleSoftQuerySmall.jpg|90px|link=http://www.amazon.co.uk/gp/product/B013ZRND0I/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=B013ZRND0I&linkCode=as2&tag=springboardso-21]] | ||
| + | |||
| + | |For more information about functions in [[PeopleSoft Query]] and some other interesting stuff too, read our eBook: | ||
| + | |||
| + | [http://www.amazon.co.uk/gp/product/B013ZRND0I/ref=as_li_tl?ie=UTF8&camp=1634&creative=6738&creativeASIN=B013ZRND0I&linkCode=as2&tag=springboardso-21 Using Functions in PeopleSoft Query] | ||
| + | |||
| + | |} | ||
