vb.net - date

[charlotte]

hi, help needed!

i'm doing a vb.net project!
i use MS Access as my database at the beginning.
so i use:-

CDate(valid_date) < # " & DateTime.Now.ToString("dd/MM/yyyy") & "#

to get some record that the valid_date is < than today date!
it work well when using MS Access.
(NOTE: data type for valid_date is in VARCHAR not in Date)

but when my move my database to Oracle.
it has a problem!
i cannot use the CDate function. an error as below occur!


One or more errors occurred during processing of command. ORA-00911: invalid character

[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: One or more errors occurred during processing of command. ORA-00911: invalid character[/font]




so any help can be provided regarding compare the date when database is store in Oracle!


regards;
charlotte

 

[JuggaloBrotha]

i know that CDate is a vb function so it would either work for both or not work for either databases

you should check the contents of valid_date before you try to convert it to the date type

 

[charlotte]

tx juggaloBrotha!

it really only work with the MS Acess!
i dunno what is going wrong!

besides, i still wan maintain the data type of the valid_date as VARCHAR.
i dun wan it to change to the Date type!

any others suggestion?
anyway thanks a lot!


regards;
charlotte

 

[kulrom]

ORA-00911:invalid character

Cause:Special characters are valid only in certain places. If special characters other than $, _, and # are used in a name and the name is not enclosed in double quotation marks ("), this message will be issued. One exception to this rule is for database names; in this case, double quotes are stripped out and ignored.

Action:Remove the invalid character from the statement or enclose the object name in double quotation marks

remove # from there and use Ctype(valid_date, Date) instead vb CDate

Cheers

 

[dashley]

Charlotte

Oracle has a to_date function http://www.techonthenet.com/oracle/functions/to_date.htm
and a to_char function
http://www.techonthenet.com/oracle/functions/to_char.htm

Check out the links above for the particulars. You cant use the pound (#) signs in Oracle.
The normal construct for the to_dDate function for oracle is:
TO_DATE('" & Now & "','MM/DD/YYYY')


Good luck.

Dan

 

[charlotte]

tx kulrom.

i try to delete the #
and using

Ctype(a.APP_VDATE_TO, Date) < '" & DateTime.Now.ToString() & "'

it display another error for me!

One or more errors occurred during processing of command. ORA-00936: missing expression

[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: One or more errors occurred during processing of command. ORA-00936: missing expression
[/font]
[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif][/font]
[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif][/font]
[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif][/font]
[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]may i know what is going wrong?[/font]
[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]thanks for the help![/font]
[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif][/font]
[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]regards;[/font]
[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]charlotte
[/font]

 

[dashley]

Charlotte,

DIM MySql as String

MySql = "Select * from my db where date_field < TO_DATE('" & Now & "','MM/DD/YYYY')"


Will work with Oracle as a sql statement

 

[charlotte]

thanks dashley!

i try to use
MySql = "Select * from ESC_APPLY where valid_to < TO_DATE('" & Now & "','MM/DD/YYYY')"
i change my data type for valid_to to Date in my database already!


but an error occur!
ORA-01830: date format picture ends before converting entire input string

[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: ORA-01830: date format picture ends before converting entire input string[/font]



may i know what is going wrong?
pls help me! thanks

regards;
charlotte

 

[dashley]

Charlotte,


The date format picture is this part of the sql statement

DD-MM-YYYY HH:MM:SS PM ( or AM)

If you passing moths as May or June istead of 05 or 06 then use this picture instead

DD-MON-YYYY HH:MM:SS PM (or AM)

If you passing it date like 6/12/2005 You'll have problems when dates like 10/12/2005 show up unless all dates passed are the same format like mm/dd/yyyy .

You should be able to standardize (formatdatetime) all dates before pusing them into the data base


Dan