Home » Developer & Programmer » Precompilers, OCI & OCCI » How can I convert a char to date?
icon7.gif  How can I convert a char to date? [message #110975] Fri, 11 March 2005 10:54 Go to next message
ipq2418
Messages: 1
Registered: March 2005
Junior Member
Hi!

I am developing an aplication with SQL Embeded and I am having some problems with it:

I have a variable whose type is char and I want to make an insert statement with it but when I execute it the date inserted is 00/00/0000.

Here is the code:
.
.
EXEC SQL BEGIN DECLARE SECTION;
CHAR DATEJOUR[9];
EXEC SQL END DECLARE SECTION;

(I inicialize the variable with a date with format 'DD/MM/YY' ended with '\0')

EXEC SQL INSERT INTO hrb (hrb,REBUT,NO_OF,INDICE_OF,OPERATION,MACHINE,POSTE,COMPAGNON,QTE_REBUT,DATE_CONSTAT) DATE_CONSTAT (:no_ordre,:no_ordre,:cle_ofc.of,:cle_ofc.indice,:indice,:ope_suiv,'0',:compagnon,:compagnon,:poids);


How I could make the conversion between types?

Thanks:)

IPQ";)







[Updated on: Fri, 11 March 2005 10:56]

Report message to a moderator

Re: How can I convert a char to date? [message #112559 is a reply to message #110975] Sun, 27 March 2005 15:43 Go to previous messageGo to next message
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
Senior Member

can you provide the table description.

Looking at your code I don't see the datejour variable on the insertion list. There is a '0' though, is this the date by any chance?

Michael Hartley.
icon13.gif  Re: How can I convert a char to date? [message #112580 is a reply to message #112559] Mon, 28 March 2005 02:12 Go to previous messageGo to next message
basaksumit
Messages: 4
Registered: March 2005
Location: India
Junior Member
use to_date function
icon7.gif  Re: How can I convert a char to date? [message #112688 is a reply to message #112580] Mon, 28 March 2005 13:57 Go to previous messageGo to next message
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
Senior Member

For input and output of dates, the standard Oracle default date format is DD-MON-RR. For example:

'13-NOV-1992'

Therefore there is no implicit requirement to convert a string date representation to a date type within the insert DML statement. Unless the format of the date string is something other than the default.

Michael Hartley.

Re: How can I convert a char to date? [message #112745 is a reply to message #112688] Tue, 29 March 2005 01:13 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Michael, I don't agree at all! The default date format is depending on X factors (both server as client have their influence on this). I've been trying to convince end users and forum members that date manipulation in Oracle should always go through proper casting (it's the only way to be sure that date handling goes well).

Michael Hartley

For input and output of dates, the standard Oracle default date format is DD-MON-RR. For example:

'13-NOV-1992'


What is it? 'DD-MON-RR' or 'DD-MON-YYYY' or (like at our shop) 'DD-MON-YY'? See my point?

[EDIT: typo]
MHE

[Updated on: Tue, 29 March 2005 01:14]

Report message to a moderator

Re: How can I convert a char to date? [message #112759 is a reply to message #112745] Tue, 29 March 2005 02:44 Go to previous messageGo to next message
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
Senior Member

Hi

you should read the Oracle documentation because it clearly states the default date format.

The RR for the year uses a swing date mechanism that you need to understand, however as a default it both resolves the y2k issue and complies with current dates very easily.

You are correct that the standard date format can be modified in many ways: spfile parameter, NLS_DATE on the database host and client

The fact your local site does not use the default date format has no influence on the out of the box default date format.

kind regards,

Michael.
Re: How can I convert a char to date? [message #112761 is a reply to message #112759] Tue, 29 March 2005 03:35 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Clearly you didn't see my point then...oh well:
I just wanted to make it clear that implicit casting is not a good idea. That's all.

Quote:

The fact your local site does not use the default date format has no influence on the out of the box default date format.
Euhr...you are convinced that the entire world is part of the United Kingdom? NLS_TERRITORY specifies the implicit default date format (what you were referring to I guess). And for UK it is, indeed (drum roll Laughing), 'DD-MON-RR'. But our local site is just another example of the real world, with its own defaults.

No offence, but there are other territories (even out the box). Very Happy

PS: I don't want to start an entire discussion on territories or whatever, I'm just stating that explicit casting is the way to go.

[EDIT: Read the manual, it's stated nowhere that the default date format is set to 'DD-MON-RR', at least not in the online docs.]
[EDIT2: Yes it does, see followup Very Happy]
MHE

[Updated on: Tue, 29 March 2005 04:51]

Report message to a moderator

Re: How can I convert a char to date? [message #112764 is a reply to message #112761] Tue, 29 March 2005 03:55 Go to previous messageGo to next message
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
Senior Member

Hi,

Default date format is defined in the following online document:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adfnstyp.htm#425134

--cut
Date Format

For input and output of dates, the standard Oracle default date format is DD-MON-RR. For example:

'13-NOV-1992'
--cut

My final comment on whether the to_date() function should be called every time is with respect to communication. All the developers should undestand the coding standards for data, if they are falling back to defining their own date formats and making their code compatible with the database by making explicit calls to the to_date() function there will inevitably be a loss of presentation style for the user.

Keeping the date format simple and standardised across all the applications that access the database, not only provides centralised support for date maintenance, keeps the date mechanisms simple and keeps date mechanisms documented.

kind regards

Michael Hartley
Re: How can I convert a char to date? [message #112769 is a reply to message #112764] Tue, 29 March 2005 04:50 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I stand corrected Thumbs Up.

Regards,
MHE
Previous Topic: Date
Next Topic: TABLE NAME / JOIN / ATTR_GET / OCI
Goto Forum:
  


Current Time: Fri Mar 29 06:43:24 CDT 2024