ASP and Oracle

[oneeye]

Does anyone know how to reformat the date so that I can insert a record with .asp into oracle. The format I need is dd-mmm-yyyy . I typically use Dreamweaver to insert data but it doesn't work in this case because of the difference in date format. Any one have some sample asp code that inserts a date field into oracle, or converts the default value of the Date() funtion to the format specified above. I was supposed to have this done last week and am in a real bind. Any help would be greatly appreciated.

Thanks,

Oneeye

[paulhiles]

Hi there Oneeye,

This article from the 4GuysFromRolla site looks like it should answer your question. They use a function to ensure that no matter what the native environment, the date string will be read in "dd-MMM-yyy" format.

Function MediumDate (str)
Dim aDay
Dim aMonth
Dim aYear

aDay = Day(str)
aMonth = Monthname(Month(str),True)
aYear = Year(str)

MediumDate = aDay & "-" & aMonth & "-" & aYear
End Function

An example showing how you might use it with a database update:

strSQLUpdate = "UPDATE tblOrders " & _
"SET fldDateOrdered = #" & MediumDate( Date() ) & "# " & _
"WHERE fldStatus = open ; "



Let me know how you get on,

Paul

[sma]

You'd better use to_date() & to_char() oracle functions to be able to work for any date format setting.
(e.g.
- update my_table set my_date = to_date('20040117', 'yyyymmdd');
- select to_char(my_date, 'yyyy-mon-dd) from my_table;
)

[paulhiles]

Hi Oneeye,

If you're still having problems, you might want to take a look at the following online tool.
A Gentle Introduction to SQL: INSERT, UPDATE and DELETE
http://sqlzoo.net/howto/oracletip674865.htm

[oneeye]

I will giv it a try, Thanks for helping the Oracle Handicapped.

Oneeye