HOWTO: SAP Sybase IQ / SQL Anywhere : searching date, datetime, time fields

If you’re new to IQ (or SQL Anywhere) you may run into an oddity that if you connect with ISQL the following code works but doesn’t work in DBISQL:

SELECT col1_date
FROM mytable
WHERE col1_date >= '06/01/2014'

This is because any connection to IQ using the native connection (SQL Anywhere connection) that is used by dbisql and the like expects string to datetime format of “YYYY-MM-DD HH:NN:SS.SSS”. This is not changeable by setting an option. The string needs to be converted using a style (datetime string style # 103) prior to being used as a SARG.

SELECT col1_date
FROM mytable
WHERE col1_date >= convert(datetime, '06/01/2014', 103)

Even those of us that have been around for eons tend to forget ‘little’ gotchas like this.

From http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1570/html/blocks/X41864.htm :

Table 2-3: Date format conversions using the style parameter
Without century (yy) With century (yyyy) Standard Output
Key “mon” indicates a month spelled out, “mm” the month number or minutes. “HH ”indicates a 24-hour clock value, “hh” a 12-hour clock value. The last row, 23, includes a literal “T” to separate the date and time portions of the format.
0 or 100 Default mon dd yyyy hh:mm AM (or PM)
1 101 USA mm/dd/yy
2 2 SQL standard yy.mm.dd
3 103 English/French dd/mm/yy
4 104 German dd.mm.yy
5 105 dd-mm-yy
6 106 dd mon yy
7 107 mon dd, yy
8 108 HH:mm:ss
9 or 109 Default + milliseconds mon dd yyyy hh:mm:ss AM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
13 113 yy/dd/mm
14 114 mm/yy/dd
14 114 hh:mi:ss:mmmAM(or PM)
15 115 dd/yy/mm
16 or 116 mon dd yyyy HH:mm:ss
17 117 hh:mmAM
18 118 HH:mm
19 hh:mm:ss:zzzAM
20 hh:mm:ss:zzz
21 yy/mm/dd HH:mm:ss
22 yy/mm/dd HH:mm AM (or PM)
23 yyyy-mm-ddTHH:mm:ss

The default values (style 0 or 100), and style 9 or 109 return the century (yyyy). When converting to char or varchar from smalldatetime, styles that include seconds or milliseconds show zeros in those positions.

Share Button

HowTo: List locked, expired and to expire dates for Oracle logins query for sqlplus/scripting

Oracle

set pagesize 5000
set linesize 999
set trimspool on

column "Expire Date" format a20
column "Locked Date" format a20

select 
    gn.GLOBAL_NAME as "Instance",
    du.username, 
    du.expiry_date as "Expire Date",
    du.account_status,
    du.lock_date as "Locked Date",
    du.profile,
    dp.limit as "Profile Password Expiration"
from dba_users du, global_name gn, dba_profiles dp
where (du.account_status IN ( 'OPEN', 'LOCKED' )  OR du.account_status like '%EXPIRE%')
  and du.profile = dp.profile
  and dp.resource_name = 'PASSWORD_LIFE_TIME'
order by du.account_status, du.expiry_date, du.username

Updated to show which login profile is being by a user and what the password expiration interval is per the login profile.

Share Button

How to change the default NLS_DATE_FORMAT (Date format) in Oracle 10g/11g

It is really really easy to change the default NLS_DATE_FORMAT setting but to be honest, you should set it at a session level IMHO.

We basically just need to run “ALTER SYSTEM SET NLS_DATE_FORMAT=’YYYY-MM-DD’ SCOPE=SPFILE” as a user with sysdba privileges. If you started the Oracle instance without a spfile (it should be located at $ORACLE_HOME/dbs/spfile[instance name].ora), you will receive the ORA-32001 error.

ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD' SCOPE=SPFILE
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

Just create a new spfile, restart:

select INSTANCE_NAME from v$instance;
INSTANCE_NAME
----------------
UAT2
create spfile='/oracle/10g/dbs/spfileUAT2.ora' from pfile='/oracle/10g/dbs/initUAT2.ora';

*restart*

select INSTANCE_NAME from v$instance;
INSTANCE_NAME
----------------
UAT2
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD' SCOPE=SPFILE;
System altered.

*restart*

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';
VALUE
----------------------------------------------------------------
YYYY-MM-DD

That’s it. 🙂

Share Button