By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Oracle and asp.....still cannot figure this out.

P: n/a
Could some please finish this line of code. For the life of me
whatever I try it does not work.

I am trying to insert the current date time into oracle. Now I know
that in oracle if I run

select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual

result would be 15/06/06 14:49:35

Now I want to insert this syntax into my asp code so what I have done
is created a variable and in my insert statement called the variable.
Still does not work

testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , "& testing & ", 'user')"

Also, I would like to insert into my insert statement but have no idea
Dim userrequested
userrequested = Request.ServerVariables("LOGON_USER")

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
hh24:mi:ss'), 'userrequested')"

Jun 15 '06 #1
Share this Question
Share on Google+
24 Replies


P: n/a
On 15 Jun 2006 12:00:36 -0700, cl********@hotmail.com wrote:
Could some please finish this line of code. For the life of me
whatever I try it does not work.

I am trying to insert the current date time into oracle. Now I know
that in oracle if I run

select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual

result would be 15/06/06 14:49:35

Now I want to insert this syntax into my asp code so what I have done
is created a variable and in my insert statement called the variable.
Still does not work

testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , "& testing & ", 'user')"

Also, I would like to insert into my insert statement but have no idea
Dim userrequested
userrequested = Request.ServerVariables("LOGON_USER")

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
hh24:mi:ss'), 'userrequested')"

The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a
Response.Write, so instead of sending your
statement to Oracle, do a

Response.Write(ssqlCheckBox)
assume :
Survey_ID=Meantest
objRS("Question_ID") =23
answerChoices(answerChoice) = Yes
user =JVG
then it will need to end up ( somehow) like this:

Insert into Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_ID)
VALUES ('Meantest','23','Yes',To_char(sysdate,'dd/mm/yy hh24:mi:ss'),'JVG' )


Jun 15 '06 #2

P: n/a
Hello Turkbear,

This was an excellent suggestion as now I have the insert statement
that I need to be processed into the database. However, now I am
confused as to why it is not inserting. Once I try and actually insert
this into the database I get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-01843: not a valid month
/itsurvey/testSubmission.asp, line 72

Any ideas?

Here is the insert string
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_Id)
VALUES (1,'1', 'e', '16/06/2006 11:09:20 AM')

I have used the function Now() for datetime.
Thanks again
Turkbear wrote:
On 15 Jun 2006 12:00:36 -0700, cl********@hotmail.com wrote:
Could some please finish this line of code. For the life of me
whatever I try it does not work.

I am trying to insert the current date time into oracle. Now I know
that in oracle if I run

select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual

result would be 15/06/06 14:49:35

Now I want to insert this syntax into my asp code so what I have done
is created a variable and in my insert statement called the variable.
Still does not work

testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , "& testing & ", 'user')"

Also, I would like to insert into my insert statement but have no idea
Dim userrequested
userrequested = Request.ServerVariables("LOGON_USER")

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
hh24:mi:ss'), 'userrequested')"

The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a
Response.Write, so instead of sending your
statement to Oracle, do a

Response.Write(ssqlCheckBox)
assume :
Survey_ID=Meantest
objRS("Question_ID") =23
answerChoices(answerChoice) = Yes
user =JVG
then it will need to end up ( somehow) like this:

Insert into Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_ID)
VALUES ('Meantest','23','Yes',To_char(sysdate,'dd/mm/yy hh24:mi:ss'),'JVG' )


Jun 16 '06 #3

P: n/a
On 16 Jun 2006 08:10:27 -0700, cl********@hotmail.com wrote:
Hello Turkbear,

This was an excellent suggestion as now I have the insert statement
that I need to be processed into the database. However, now I am
confused as to why it is not inserting. Once I try and actually insert
this into the database I get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-01843: not a valid month
/itsurvey/testSubmission.asp, line 72

Any ideas?

Here is the insert string
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_Id)
VALUES (1,'1', 'e', '16/06/2006 11:09:20 AM')

I have used the function Now() for datetime.
Thanks again
Turkbear wrote:
On 15 Jun 2006 12:00:36 -0700, cl********@hotmail.com wrote:
>Could some please finish this line of code. For the life of me
>whatever I try it does not work.
>
>I am trying to insert the current date time into oracle. Now I know
>that in oracle if I run
>
>select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual
>
>result would be 15/06/06 14:49:35
>
>Now I want to insert this syntax into my asp code so what I have done
>is created a variable and in my insert statement called the variable.
>Still does not work
>
>testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
>ssqlCheckBox = "INSERT INTO
>Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
>VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
>answerChoices(answerChoice) & "' , "& testing & ", 'user')"
>
>Also, I would like to insert into my insert statement but have no idea
>Dim userrequested
>userrequested = Request.ServerVariables("LOGON_USER")
>
>ssqlCheckBox = "INSERT INTO
>Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
>VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
>answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
>hh24:mi:ss'), 'userrequested')"

The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a
Response.Write, so instead of sending your
statement to Oracle, do a

Response.Write(ssqlCheckBox)
assume :
Survey_ID=Meantest
objRS("Question_ID") =23
answerChoices(answerChoice) = Yes
user =JVG
then it will need to end up ( somehow) like this:

Insert into Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_ID)
VALUES ('Meantest','23','Yes',To_char(sysdate,'dd/mm/yy hh24:mi:ss'),'JVG' )


Hi..
Please do not top-post..it makes it difficult to follow the thread..
You MUST use the to_date Function with the correct mask , not the raw result on NOW() for the database -
like: to_date(Now(),'dd/mm/yyyy hh24:mi:ss AM').

Doing this wil result it a insert statement something like this:

INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_Id)
VALUES (1,'1', 'e', to_date('16/06/2006 11:09:20 AM','dd/mm/yyyy hh24:mi:ss AM') )


Jun 16 '06 #4

P: n/a
On Fri, 16 Jun 2006 10:48:51 -0500, Turkbear <no***@nowhere.com> wrote:
On 16 Jun 2006 08:10:27 -0700, cl********@hotmail.com wrote:
Hello Turkbear,

This was an excellent suggestion as now I have the insert statement
that I need to be processed into the database. However, now I am
confused as to why it is not inserting. Once I try and actually insert
this into the database I get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-01843: not a valid month
/itsurvey/testSubmission.asp, line 72

Any ideas?

Here is the insert string
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_ Id,Date_Answer,User_Id)
VALUES (1,'1', 'e', '16/06/2006 11:09:20 AM')

I have used the function Now() for datetime.
Thanks again
Turkbear wrote:
On 15 Jun 2006 12:00:36 -0700, cl********@hotmail.com wrote:

>Could some please finish this line of code. For the life of me
>whatever I try it does not work.
>
>I am trying to insert the current date time into oracle. Now I know
>that in oracle if I run
>
>select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual
>
>result would be 15/06/06 14:49:35
>
>Now I want to insert this syntax into my asp code so what I have done
>is created a variable and in my insert statement called the variable.
>Still does not work
>
>testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
>ssqlCheckBox = "INSERT INTO
>Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
>VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
>answerChoices(answerChoice) & "' , "& testing & ", 'user')"
>
>Also, I would like to insert into my insert statement but have no idea
>Dim userrequested
>userrequested = Request.ServerVariables("LOGON_USER")
>
>ssqlCheckBox = "INSERT INTO
>Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
>VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
>answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
>hh24:mi:ss'), 'userrequested')"
The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a
Response.Write, so instead of sending your
statement to Oracle, do a

Response.Write(ssqlCheckBox)
assume :
Survey_ID=Meantest
objRS("Question_ID") =23
answerChoices(answerChoice) = Yes
user =JVG
then it will need to end up ( somehow) like this:

Insert into Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_ID)
VALUES ('Meantest','23','Yes',To_char(sysdate,'dd/mm/yy hh24:mi:ss'),'JVG' )


Hi..
Please do not top-post..it makes it difficult to follow the thread..
You MUST use the to_date Function with the correct mask , not the raw result on NOW() for the database -
like: to_date(Now(),'dd/mm/yyyy hh24:mi:ss AM').

Doing this wil result it a insert statement something like this:

INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_Id)
VALUES (1,'1', 'e', to_date('16/06/2006 11:09:20 AM','dd/mm/yyyy hh24:mi:ss AM') )

to further clarify the code needed, be sure to concatenate the Now() with the Sql string accurately..

Something like

to_date(" & Now() & ",'dd/mm/yyyy hh24:mi:ss AM') )"
Jun 16 '06 #5

P: n/a
Turkbear wrote:

Please do some snipping instead of making us scroll through all the
irrelevant previous replies.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jun 16 '06 #6

P: n/a

Turkbear wrote:
On 16 Jun 2006 08:10:27 -0700, cl********@hotmail.com wrote:
Hello Turkbear,

This was an excellent suggestion as now I have the insert statement
that I need to be processed into the database. However, now I am
confused as to why it is not inserting. Once I try and actually insert
this into the database I get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-01843: not a valid month
/itsurvey/testSubmission.asp, line 72

Any ideas?

Here is the insert string
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_Id)
VALUES (1,'1', 'e', '16/06/2006 11:09:20 AM')

I have used the function Now() for datetime.
Thanks again
Turkbear wrote:
On 15 Jun 2006 12:00:36 -0700, cl********@hotmail.com wrote:

>Could some please finish this line of code. For the life of me
>whatever I try it does not work.
>
>I am trying to insert the current date time into oracle. Now I know
>that in oracle if I run
>
>select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual
>
>result would be 15/06/06 14:49:35
>
>Now I want to insert this syntax into my asp code so what I have done
>is created a variable and in my insert statement called the variable.
>Still does not work
>
>testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
>ssqlCheckBox = "INSERT INTO
>Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
>VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
>answerChoices(answerChoice) & "' , "& testing & ", 'user')"
>
>Also, I would like to insert into my insert statement but have no idea
>Dim userrequested
>userrequested = Request.ServerVariables("LOGON_USER")
>
>ssqlCheckBox = "INSERT INTO
>Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,User_ID)
>VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
>answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
>hh24:mi:ss'), 'userrequested')"
The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a
Response.Write, so instead of sending your
statement to Oracle, do a

Response.Write(ssqlCheckBox)
assume :
Survey_ID=Meantest
objRS("Question_ID") =23
answerChoices(answerChoice) = Yes
user =JVG
then it will need to end up ( somehow) like this:

Insert into Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_ID)
VALUES ('Meantest','23','Yes',To_char(sysdate,'dd/mm/yy hh24:mi:ss'),'JVG' )


Hi..
Please do not top-post..it makes it difficult to follow the thread..
You MUST use the to_date Function with the correct mask , not the raw result on NOW() for the database -
like: to_date(Now(),'dd/mm/yyyy hh24:mi:ss AM').

Doing this wil result it a insert statement something like this:

INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,User_Id)
VALUES (1,'1', 'e', to_date('16/06/2006 11:09:20 AM','dd/mm/yyyy hh24:mi:ss AM') )


Hi Turkbear,

Sorry about Top posting. I hope I understood you correctly. If not
please indicate waht top posting is. I took it to mean that I posted
my message at the top of the text as opposed to the end. If I am
mistaken please explain.

Thanks for the suggestion. It does insert into the database however
the time is not showing in the database. I still only get the date.
Any other suggestions.
here is the insert statement

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer) VALUES
(" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'))"

Jun 16 '06 #7

P: n/a
hi

just a thought but cant you make the default value on the table set to
system date ?
most likely you have tried it but sometimes we miss the simple solution.
don't even know if is possible in oracle.

hth

Pedro Leite
-----------------------------------------------
Jun 16 '06 #8

P: n/a
On 16 Jun 2006 09:19:27 -0700, cl********@hotmail.com wrote:

Turkbear wrote:
On 16 Jun 2006 08:10:27 -0700, cl********@hotmail.com wrote:
Hi Turkbear,

Sorry about Top posting. I hope I understood you correctly. If not
please indicate waht top posting is. I took it to mean that I posted
my message at the top of the text as opposed to the end. If I am
mistaken please explain.

Thanks for the suggestion. It does insert into the database however
the time is not showing in the database. I still only get the date.
Any other suggestions.
here is the insert statement

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer) VALUES
(" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'))"


That was correct...Oracle, by default, will not show the date and time unless you specifically request both ..

Select to_char(datetimefield,'dd/mm/yyyy hh24:mi:ss AM') from table...

To get Just date, but in that format omit the time mask ( the hh24:mi:ss AM part)
To get just the time , use ONLY that part.
It is the 'reverse' of the to_date function..

Oracle , by default, shows dates as DD-MON-YY ( the exact format depends on your NLS_ parameters)

Jun 16 '06 #9

P: n/a
On Fri, 16 Jun 2006 17:28:35 +0100, "Pedro Leite" <naoehpreciso> wrote:
hi

just a thought but cant you make the default value on the table set to
system date ?
most likely you have tried it but sometimes we miss the simple solution.
don't even know if is possible in oracle.

hth

Pedro Leite
-----------------------------------------------


Yes, you can use a

'Alter table table_name modify column_name defgalt SYSDATE;'

Sql statement so that all future inserts that do not specify a date will insert the current sysdate value.

Good catch...

Jun 16 '06 #10

P: n/a
On Fri, 16 Jun 2006 12:05:36 -0400, "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote:
Turkbear wrote:

Please do some snipping instead of making us scroll through all the
irrelevant previous replies.

Sorry about that..but some folks also complain when the 'trail' of thread has been lost..
Jun 16 '06 #11

P: n/a
Turkbear wrote:
On Fri, 16 Jun 2006 12:05:36 -0400, "Bob Barrows [MVP]"
<re******@NOyahoo.SPAMcom> wrote:
Turkbear wrote:

Please do some snipping instead of making us scroll through all the
irrelevant previous replies.

Sorry about that..but some folks also complain when the 'trail' of
thread has been lost..


I did not say to snip it all: just snip the stuff that is irrelevant to the
reply you are making. Leave just enough that we see the context for your
reply.

Yes, this is sometimes a hard judgement to make. But in general it is better
to err on the snip-too-much side rather than the snip-not-enough side.

That said (Eugene), one should always quote just enough of the post to which
you are replying to provide context for your readers.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jun 17 '06 #12

P: n/a

Turkbear wrote:
On 16 Jun 2006 09:19:27 -0700, cl********@hotmail.com wrote:

Turkbear wrote:
On 16 Jun 2006 08:10:27 -0700, cl********@hotmail.com wrote:

Hi Turkbear,

Sorry about Top posting. I hope I understood you correctly. If not
please indicate waht top posting is. I took it to mean that I posted
my message at the top of the text as opposed to the end. If I am
mistaken please explain.

Thanks for the suggestion. It does insert into the database however
the time is not showing in the database. I still only get the date.
Any other suggestions.
here is the insert statement

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer) VALUES
(" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'))"


That was correct...Oracle, by default, will not show the date and time unless you specifically request both ..

Select to_char(datetimefield,'dd/mm/yyyy hh24:mi:ss AM') from table...

To get Just date, but in that format omit the time mask ( the hh24:mi:ss AM part)
To get just the time , use ONLY that part.
It is the 'reverse' of the to_date function..

Oracle , by default, shows dates as DD-MON-YY ( the exact format depends on your NLS_ parameters)


Hmmm, I'm confused on the last statement made only because I have
specified both date and time and it is still only submitting date.
Here is the code

to_char('" & Now() & "','dd/mm/yyyy hh24:mi:ss PM') Any other ideas?

Jun 19 '06 #13

P: n/a
On 19 Jun 2006 05:26:43 -0700, cl********@hotmail.com wrote:

Hmmm, I'm confused on the last statement made only because I have
specified both date and time and it is still only submitting date.
Here is the code

to_char('" & Now() & "','dd/mm/yyyy hh24:mi:ss PM') Any other ideas?


Please post the actual output from
Response.Write(ssqlCheckBox )...

And from

Response.Write(Now())

You maty have to assign the output of the Now() function to a variable an duse that in the concatenation:

Dim cdandt = Now();
to_date('" & cdandt & "','dd/mm/yyyy hh24:mi:ss PM') ( Remember : To_Date when INSERTING, To_Char when retreiving..)
Jun 19 '06 #14

P: n/a
I REMEMBER

had this trouble before on an MS Product.

it was a month thing

English --> Portuguese

JAN --> JAN
FEV --> FEV
MAR --> MAR
APR --> ABR **
JUN --> JUN
JUL --> JUL
AUG --> AGO **
SEP --> SET
OCT --> OUT **
NOV --> NOV
DEC --> DEZ **

some months were ok, some not, and it was the literal form, ie, trying to
insert ABR when shoule be APR.

Locale was the man.

hth

Pedro Leite From Portugal.
------------------------------------------------------------
"Turkbear" <no***@nowhere.com> escreveu na mensagem
news:pb********************************@4ax.com...
On 19 Jun 2006 05:26:43 -0700, cl********@hotmail.com wrote:


Hmmm, I'm confused on the last statement made only because I have
specified both date and time and it is still only submitting date.
Here is the code

to_char('" & Now() & "','dd/mm/yyyy hh24:mi:ss PM') Any other ideas?


Please post the actual output from
Response.Write(ssqlCheckBox )...

And from

Response.Write(Now())

You maty have to assign the output of the Now() function to a variable an
duse that in the concatenation:

Dim cdandt = Now();
to_date('" & cdandt & "','dd/mm/yyyy hh24:mi:ss PM') ( Remember :
To_Date when INSERTING, To_Char when retreiving..)

Jun 19 '06 #15

P: n/a

Turkbear wrote:
On 19 Jun 2006 05:26:43 -0700, cl********@hotmail.com wrote:


Hmmm, I'm confused on the last statement made only because I have
specified both date and time and it is still only submitting date.
Here is the code

to_char('" & Now() & "','dd/mm/yyyy hh24:mi:ss PM') Any other ideas?


Please post the actual output from
Response.Write(ssqlCheckBox )...

And from

Response.Write(Now())

You maty have to assign the output of the Now() function to a variable an duse that in the concatenation:

Dim cdandt = Now();
to_date('" & cdandt & "','dd/mm/yyyy hh24:mi:ss PM') ( Remember : To_Date when INSERTING, To_Char when retreiving..)


Hello Turkbear,

yes I did assign a variable and I read from the variable and it has not
been working. Here is what you requested.

Here is the output of Response.Write(ssqlCheckBox )...
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer) VALUES
(1,'1', 'b', to_date('19/06/2006 11:33:06 AM','dd/mm/yyyy hh:mi:ss
PM'))

And here is the output of Response.Write(Curdate)
19/06/2006 11:34:32 AM

here is my syntax
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'), " & user & ")"

Jun 19 '06 #16

P: n/a

Pedro Leite wrote:
I REMEMBER

had this trouble before on an MS Product.

it was a month thing

English --> Portuguese

JAN --> JAN
FEV --> FEV
MAR --> MAR
APR --> ABR **
JUN --> JUN
JUL --> JUL
AUG --> AGO **
SEP --> SET
OCT --> OUT **
NOV --> NOV
DEC --> DEZ **

some months were ok, some not, and it was the literal form, ie, trying to
insert ABR when shoule be APR.

Locale was the man.

hth

Pedro Leite From Portugal.
------------------------------------------------------------
"Turkbear" <no***@nowhere.com> escreveu na mensagem
news:pb********************************@4ax.com...
On 19 Jun 2006 05:26:43 -0700, cl********@hotmail.com wrote:


Hmmm, I'm confused on the last statement made only because I have
specified both date and time and it is still only submitting date.
Here is the code

to_char('" & Now() & "','dd/mm/yyyy hh24:mi:ss PM') Any other ideas?


Please post the actual output from
Response.Write(ssqlCheckBox )...

And from

Response.Write(Now())

You maty have to assign the output of the Now() function to a variable an
duse that in the concatenation:

Dim cdandt = Now();
to_date('" & cdandt & "','dd/mm/yyyy hh24:mi:ss PM') ( Remember :
To_Date when INSERTING, To_Char when retreiving..)


hello Pedro,

Locale. How do I check this. You are referring to the locale on the
server. Any idea of the comand in Oracle to check this? Thanks.

Jun 19 '06 #17

P: n/a
hi

as turkbear suggested, response.write both now() and the sqlstatement
generated by the asp scrip.
still on your side, copy + paste to oracle sql debugger ( whatever ) and
execute it.

what shows ?? errors or execute ok ??

Locale is the country settings on client and server machines ( assuming ms )
check control panel > regional settings. see if there is any difference on
language, date format, something related.

PLeite
<cl********@hotmail.com> escreveu na mensagem
news:11**********************@c74g2000cwc.googlegr oups.com...

Turkbear wrote:
On 19 Jun 2006 05:26:43 -0700, cl********@hotmail.com wrote:
>

>
>
>
>Hmmm, I'm confused on the last statement made only because I have
>specified both date and time and it is still only submitting date.
>Here is the code
>
>to_char('" & Now() & "','dd/mm/yyyy hh24:mi:ss PM') Any other ideas?


Please post the actual output from
Response.Write(ssqlCheckBox )...

And from

Response.Write(Now())

You maty have to assign the output of the Now() function to a variable an
duse that in the concatenation:

Dim cdandt = Now();
to_date('" & cdandt & "','dd/mm/yyyy hh24:mi:ss PM') ( Remember :
To_Date when INSERTING, To_Char when retreiving..)


Hello Turkbear,

yes I did assign a variable and I read from the variable and it has not
been working. Here is what you requested.

Here is the output of Response.Write(ssqlCheckBox )...
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer) VALUES
(1,'1', 'b', to_date('19/06/2006 11:33:06 AM','dd/mm/yyyy hh:mi:ss
PM'))

And here is the output of Response.Write(Curdate)
19/06/2006 11:34:32 AM

here is my syntax
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'), " & user & ")"

Jun 19 '06 #18

P: n/a
another thing

just remembered.

when constructing a date string, i had to enclose the string in hashes "#",
but for sql server.

-------------------------------------------------------
<cl********@hotmail.com> escreveu na mensagem
news:11**********************@c74g2000cwc.googlegr oups.com...

Turkbear wrote:
On 19 Jun 2006 05:26:43 -0700, cl********@hotmail.com wrote:
>

>
>
>
>Hmmm, I'm confused on the last statement made only because I have
>specified both date and time and it is still only submitting date.
>Here is the code
>
>to_char('" & Now() & "','dd/mm/yyyy hh24:mi:ss PM') Any other ideas?


Please post the actual output from
Response.Write(ssqlCheckBox )...

And from

Response.Write(Now())

You maty have to assign the output of the Now() function to a variable an
duse that in the concatenation:

Dim cdandt = Now();
to_date('" & cdandt & "','dd/mm/yyyy hh24:mi:ss PM') ( Remember :
To_Date when INSERTING, To_Char when retreiving..)


Hello Turkbear,

yes I did assign a variable and I read from the variable and it has not
been working. Here is what you requested.

Here is the output of Response.Write(ssqlCheckBox )...
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer) VALUES
(1,'1', 'b', to_date('19/06/2006 11:33:06 AM','dd/mm/yyyy hh:mi:ss
PM'))

And here is the output of Response.Write(Curdate)
19/06/2006 11:34:32 AM

here is my syntax
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'), " & user & ")"

Jun 19 '06 #19

P: n/a
On 19 Jun 2006 08:36:43 -0700, cl********@hotmail.com wrote:

Turkbear wrote: Hello Turkbear,

yes I did assign a variable and I read from the variable and it has not
been working. Here is what you requested.

Here is the output of Response.Write(ssqlCheckBox )...
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer) VALUES
(1,'1', 'b', to_date('19/06/2006 11:33:06 AM','dd/mm/yyyy hh:mi:ss
PM'))

And here is the output of Response.Write(Curdate)
19/06/2006 11:34:32 AM

here is my syntax
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'), " & user & ")"


Shouldn't it be:
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & CurDate & "','dd/mm/yyyy hh:mi:ss PM'), " & user & ")"
Now, please show the output you get with a

Select to_char(Date_Answer,'dd/mm/yyyy hh:mi:ss PM') from Survey_User_Answer;

in SqlPlus



Jun 19 '06 #20

P: n/a

Turkbear wrote:
On 19 Jun 2006 08:36:43 -0700, cl********@hotmail.com wrote:

Turkbear wrote:

Hello Turkbear,

yes I did assign a variable and I read from the variable and it has not
been working. Here is what you requested.

Here is the output of Response.Write(ssqlCheckBox )...
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer) VALUES
(1,'1', 'b', to_date('19/06/2006 11:33:06 AM','dd/mm/yyyy hh:mi:ss
PM'))

And here is the output of Response.Write(Curdate)
19/06/2006 11:34:32 AM

here is my syntax
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_I d,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'), " & user & ")"


Shouldn't it be:
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id ,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & CurDate & "','dd/mm/yyyy hh:mi:ss PM'), " & user & ")"
Now, please show the output you get with a

Select to_char(Date_Answer,'dd/mm/yyyy hh:mi:ss PM') from Survey_User_Answer;

in SqlPlus


Sorry Turkbear,

You are correct in the syntax. I was trying to get it to work and when
I sent you the syntax I incorrectly sent you the wrong thing. Anyways,
here is the output with the above select that you mentioned

19/06/2006 01:55:25 PM

Jun 19 '06 #21

P: n/a
On 19 Jun 2006 10:58:07 -0700, cl********@hotmail.com wrote:

Turkbear wrote:
On 19 Jun 2006 08:36:43 -0700, cl********@hotmail.com wrote:
>


Now, please show the output you get with a

Select to_char(Date_Answer,'dd/mm/yyyy hh:mi:ss PM') from Survey_User_Answer;

in SqlPlus


Sorry Turkbear,

You are correct in the syntax. I was trying to get it to work and when
I sent you the syntax I incorrectly sent you the wrong thing. Anyways,
here is the output with the above select that you mentioned

19/06/2006 01:55:25 PM


That looks correct, is it not?

The entire dateTime values are being stored and returned..


Jun 19 '06 #22

P: n/a

Turkbear wrote:
On 19 Jun 2006 10:58:07 -0700, cl********@hotmail.com wrote:

Turkbear wrote:
On 19 Jun 2006 08:36:43 -0700, cl********@hotmail.com wrote:

>


Now, please show the output you get with a

Select to_char(Date_Answer,'dd/mm/yyyy hh:mi:ss PM') from Survey_User_Answer;

in SqlPlus


Sorry Turkbear,

You are correct in the syntax. I was trying to get it to work and when
I sent you the syntax I incorrectly sent you the wrong thing. Anyways,
here is the output with the above select that you mentioned

19/06/2006 01:55:25 PM


That looks correct, is it not?

The entire dateTime values are being stored and returned..

Hmmm I see. Does Oracle not store the time values or do you only see
this when you do a to_char? I guess that is where I am having the
issues. I am used to Datetime in Sql Server and actually see the whole
datetime as opposed to only seeing a date and then do a to_char on that
date. I apologize then, it is working I was just confused with how
oracle treats and stores the value.

Jun 19 '06 #23

P: n/a
On 19 Jun 2006 11:26:51 -0700, cl********@hotmail.com wrote:

>
>19/06/2006 01:55:25 PM


That looks correct, is it not?

The entire dateTime values are being stored and returned..

Hmmm I see. Does Oracle not store the time values or do you only see
this when you do a to_char? I guess that is where I am having the
issues. I am used to Datetime in Sql Server and actually see the whole
datetime as opposed to only seeing a date and then do a to_char on that
date. I apologize then, it is working I was just confused with how
oracle treats and stores the value.

Right..as I stated before, Oracle, unless 'asked to by the to_char function' will not show the time component, even
though it is stored in the database - Do not confuse what the DISPLAY format is with how it is actually stored ( it is
really stored as a compound number that gets translated according to the mask requested for display - the system
default is DD-MON-YY )

This can be very, very confusing to newcomers to Oracle and there is absolutely no need to apologise....
Select sysdate from dual;
19-JUN-06

Select to_char(sysdate,'mm/dd/yyyy hh:mi:ss AM') from dual;
06/19/2006 02:14:37 PM
Jun 19 '06 #24

P: n/a

Turkbear wrote:
On 19 Jun 2006 11:26:51 -0700, cl********@hotmail.com wrote:

>
>19/06/2006 01:55:25 PM

That looks correct, is it not?

The entire dateTime values are being stored and returned..

Hmmm I see. Does Oracle not store the time values or do you only see
this when you do a to_char? I guess that is where I am having the
issues. I am used to Datetime in Sql Server and actually see the whole
datetime as opposed to only seeing a date and then do a to_char on that
date. I apologize then, it is working I was just confused with how
oracle treats and stores the value.

Right..as I stated before, Oracle, unless 'asked to by the to_char function' will not show the time component, even
though it is stored in the database - Do not confuse what the DISPLAY format is with how it is actually stored ( it is
really stored as a compound number that gets translated according to the mask requested for display - the system
default is DD-MON-YY )

This can be very, very confusing to newcomers to Oracle and there is absolutely no need to apologise....
Select sysdate from dual;
19-JUN-06

Select to_char(sysdate,'mm/dd/yyyy hh:mi:ss AM') from dual;
06/19/2006 02:14:37 PM


This is perfect Turkbear. Really appreciate all your help and
everyone's help on this. This really clarified a lot for me. Have a
great day.

Jun 19 '06 #25

This discussion thread is closed

Replies have been disabled for this discussion.