|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Oracle and asp.....still cannot figure this out.
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')" On 15 Jun 2006 12:00:36 -0700, clintto***@hotmail.com wrote:
Show quote >Could some please finish this line of code. For the life of me The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a>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')" 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' ) 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: Show quote > On 15 Jun 2006 12:00:36 -0700, clintto***@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_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')" > > > 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' ) On 16 Jun 2006 08:10:27 -0700, clintto***@hotmail.com wrote:
Show quote >Hello Turkbear, Hi..> >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, clintto***@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_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')" >> >> >> 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' ) 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') ) On Fri, 16 Jun 2006 10:48:51 -0500, Turkbear <no***@nowhere.com> wrote:
Show quote >On 16 Jun 2006 08:10:27 -0700, clintto***@hotmail.com wrote: to further clarify the code needed, be sure to concatenate the Now() with the Sql string accurately..> >>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, clintto***@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_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')" >>> >>> >>> 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') ) > > > Something like to_date(" & Now() & ",'dd/mm/yyyy hh24:mi:ss AM') )" 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" On Fri, 16 Jun 2006 12:05:36 -0400, "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote: Sorry about that..but some folks also complain when the 'trail' of thread has been lost..>Turkbear wrote: > >Please do some snipping instead of making us scroll through all the >irrelevant previous replies. Turkbear wrote:
> On Fri, 16 Jun 2006 12:05:36 -0400, "Bob Barrows [MVP]" I did not say to snip it all: just snip the stuff that is irrelevant to the > <reb01501@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.. 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" Turkbear wrote:
Show quote > On 16 Jun 2006 08:10:27 -0700, clintto***@hotmail.com wrote: Hi Turkbear,> > >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, clintto***@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_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')" > >> > >> > >> 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') ) 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'))" 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 -----------------------------------------------
Show quote
On Fri, 16 Jun 2006 17:28:35 +0100, "Pedro Leite" <naoehpreciso> wrote: Yes, you can use 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 >----------------------------------------------- > '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... On 16 Jun 2006 09:19:27 -0700, clintto***@hotmail.com wrote:
Show quote > That was correct...Oracle, by default, will not show the date and time unless you specifically request both ..>Turkbear wrote: >> On 16 Jun 2006 08:10:27 -0700, clintto***@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_Id,Date_Answer) VALUES >(" & SurveyID & ",'" & objRS("Question_Id") & "', '" & >answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy >hh:mi:ss PM'))" 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) Turkbear wrote:
Show quote > On 16 Jun 2006 09:19:27 -0700, clintto***@hotmail.com wrote: Hmmm, I'm confused on the last statement made only because I have> > > > >Turkbear wrote: > >> On 16 Jun 2006 08:10:27 -0700, clintto***@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_Id,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) 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? On 19 Jun 2006 05:26:43 -0700, clintto***@hotmail.com wrote:
> Please post the actual output from> > > >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? 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..) 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. ------------------------------------------------------------ Show quote "Turkbear" <no***@nowhere.com> escreveu na mensagem news:pbdd921t1q13d1m3dpjli96p2gvnclg7jg@4ax.com... > On 19 Jun 2006 05:26:43 -0700, clintto***@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..) > > Pedro Leite wrote:
Show quote > I REMEMBER hello Pedro,> > 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:pbdd921t1q13d1m3dpjli96p2gvnclg7jg@4ax.com... > > On 19 Jun 2006 05:26:43 -0700, clintto***@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..) > > > > 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. Turkbear wrote:
Show quote > On 19 Jun 2006 05:26:43 -0700, clintto***@hotmail.com wrote: Hello Turkbear,> > > > > > > > > > > >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..) 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 & ")" 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 <clintto***@hotmail.com> escreveu na mensagem Show quote news:1150731403.814320.280090@c74g2000cwc.googlegroups.com... > > Turkbear wrote: >> On 19 Jun 2006 05:26:43 -0700, clintto***@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 & ")" > another thing
just remembered. when constructing a date string, i had to enclose the string in hashes "#", but for sql server. ------------------------------------------------------- <clintto***@hotmail.com> escreveu na mensagem Show quote news:1150731403.814320.280090@c74g2000cwc.googlegroups.com... > > Turkbear wrote: >> On 19 Jun 2006 05:26:43 -0700, clintto***@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 & ")" > On 19 Jun 2006 08:36:43 -0700, clintto***@hotmail.com wrote:
Show quote > Shouldn't it be:>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_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 & ")" 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 Turkbear wrote:
Show quote > On 19 Jun 2006 08:36:43 -0700, clintto***@hotmail.com wrote: Sorry Turkbear,> > > > >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_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 & ")" > > 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 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 On 19 Jun 2006 10:58:07 -0700, clintto***@hotmail.com wrote:
Show quote > That looks correct, is it not?>Turkbear wrote: >> On 19 Jun 2006 08:36:43 -0700, clintto***@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 The entire dateTime values are being stored and returned.. Turkbear wrote:
Show quote > On 19 Jun 2006 10:58:07 -0700, clintto***@hotmail.com wrote: Hmmm I see. Does Oracle not store the time values or do you only see> > > > >Turkbear wrote: > >> On 19 Jun 2006 08:36:43 -0700, clintto***@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.. 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. On 19 Jun 2006 11:26:51 -0700, clintto***@hotmail.com wrote:
Show quote > Right..as I stated before, Oracle, unless 'asked to by the to_char function' will not show the time component, even>> > >> >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. 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 Turkbear wrote:
Show quote > On 19 Jun 2006 11:26:51 -0700, clintto***@hotmail.com wrote: This is perfect Turkbear. Really appreciate all your help and> > > > > >> > > >> >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 everyone's help on this. This really clarified a lot for me. Have a great day.  |
|||||||||||||||||||||||
Other interesting topics