Question & Answer
Question
How do I set the default date and time format within NPS?
Answer
NPS users may want to change the default output of the date format when selecting against date fields within the database. For example, the user may want to change the format from 2008-12-03 10:02:15 to 03.12.2008 10:02:39.
In Oracle, setting the default time and date format involves the following parameters:
- NLS_DATE_FORMAT
NLS_TIMESTAMP_FORMAT
This can be done at the instance-level or session-level.
The following SQL normalizes date/time representation between Sybase, Sybase IQ and Oracle:
- ALTER SESSION SET NLS_DATE_FORMAT = Mon dd yyyy hh:mi:ss":000"AM
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'Mon dd yyyy hh:mi:ss:ff3AM'
Validating the Default Date Time Setting
To display the date and time format that NPS is currently using as a default, log into nzsql and run the following command:
- nzsql -c "show datestyle"
Setting the Date Time Variable
When you configure the date time setting using the SET DATESTYLE command, your configuration exists as long as the lifespan of your connected session because DATESTYLE is a run-time variable. To make the change more permanent, consider two other ways to configure this format.
1. You may change the NZ_DATESTYLE environment variable used by the database directly on postmaster's start-up by adding a Bash export NZ_DATESTYLE ="FORMAT" variable setting to the postgres user's .bash_profile file. This serves as a global change for all date and time formatting performed by NPS.
2. You may change the NZ_DATESTYLE environment variable used by a client application (assuming that it was written with the libpq library) on its session start-up if you wish to have the client configure the output. If you set the NZ_DATESTYLE variable at a Bash prompt using the export command and then start nzsql, nzsql will recognize it and automatically configure itself for that style of output.
Datestyle Formats
The following are some date styles to choose from and examples of their formats:
Command | Resulting output |
set datestyle=ISO,US; | 2008-12-01 16:13:58 |
set datestyle=ISO,European; | 2008-12-01 16:13:58 |
set datestyle=SQL,US; | 12/01/2008 16:13:58 |
set datestyle=SQL,European; | 01/12/2008 16:15:24 |
set datestyle=Postgres,US; | Mon Dec 01 16:15:42 2008 |
set datestyle=Postgres,European; | Mon 01 Dec 16:16:01 2008 |
set datestyle=German; | 01.12.2008 16:16:16 |
set datestyle=German,US; | 01.12.2008 16:16:28 |
In-Query Date Format Manipulation
Command | Resulting output |
select to_char(now(),'Dy DD YYYY HH:MI:SS AM'); | Mon 01 2008 04:16:55 PM |
select to_char(now(),'Dy DD YYYY HH:MI:SS:AM') | Mon 01 2008 04:17:13:PM |
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ729889
Was this topic helpful?
Document Information
More support for:
IBM PureData System
Software version:
1.0.0
Document number:
466483
Modified date:
17 October 2019
UID
swg21574664