Toad World® Forums

Date formating issues


#1

I have a strange date pattern format issue.

In XP, I have set the Data Type formatting to "General date/time with seconds
pattern (G). And in the custom Tab, I set DateTime2 as yyyy/MM/dd
HH:mm:ss.fffffff
The displayed formatting is 24/08/2010 3:26:46 PM
When I use ‘2010-08-23’ as an input string for a date column, TDA executes and
inserts the field correctly.

In WinServer2003, I set it exactly the same, but the displayed formatting is
8/24/10 3:26:46 PM
And trying to insert ‘2010-08-23’ yields a “Literal does not match input string”
error.

Is there anything in the PC’s local setting that must match? I am trying to
transport a complicated script which uses string patter formation in date
columns from XP to run on the server’s TDA (same build) but I get a “Literal
does not match input string” error for a script that works from my XP box.
(Oracle)

rewriting the script to work on the Server would take a lot of time as there are
dozens of date columns involved in nested subqueries.


#2

I’m going to leave this one for Shawn to answer. He will be back tomarrow.

Debbie


#3

So I ensured the User Locale and Region settings were exactly the same, but the
displayed formatting is still different.

I worked around this by ensuring that I always code
to_date(‘2010-08-23’,‘YYYY-MM-DD’)
In other words, I make sure I qualify the pattern for my input strings (it took
awhile to edit!), but I still don’t understand why in XP, I can insert
‘2010-08-23’ as a date, but in WinServer2003, that string pattern produces an
error…


#4

Tom,

This is usually caused by a mismatch in the NLS_DATE_FORMAT parameter between
servers. The best way to keeps scripts compatible is to use your solution of
using TO_DATE. Alternatively, you can set the NLS_DATE_FORMAT parameter for the
session at the beginning of your script:

ALTER SESSION SET NLS_DATE_FORMAT=‘DD-MON-YYYY HH24:MI:SS’;

Shawn…