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

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')"

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

On 15 Jun 2006 12:00:36 -0700, clinttoris@hotmail.com wrote:
[color=blue]
>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')"[/color]


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' )

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

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:[color=blue]
> On 15 Jun 2006 12:00:36 -0700, clinttoris@hotmail.com wrote:
>[color=green]
> >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')"[/color]
>
>
> 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' )[/color]

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

On 16 Jun 2006 08:10:27 -0700, clinttoris@hotmail.com wrote:
[color=blue]
>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:[color=green]
>> On 15 Jun 2006 12:00:36 -0700, clinttoris@hotmail.com wrote:
>>[color=darkred]
>> >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')"[/color]
>>
>>
>> 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' )[/color][/color]

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') )

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

On Fri, 16 Jun 2006 10:48:51 -0500, Turkbear <noone@nowhere.com> wrote:
[color=blue]
>On 16 Jun 2006 08:10:27 -0700, clinttoris@hotmail.com wrote:
>[color=green]
>>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:[color=darkred]
>>> On 15 Jun 2006 12:00:36 -0700, clinttoris@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' )[/color][/color]
>
>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') )
>
>
>[/color]
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') )"

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

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"

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

Turkbear wrote:[color=blue]
> On 16 Jun 2006 08:10:27 -0700, clinttoris@hotmail.com wrote:
>[color=green]
> >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:[color=darkred]
> >> On 15 Jun 2006 12:00:36 -0700, clinttoris@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' )[/color][/color]
>
> 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') )[/color]



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'))"

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

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
-----------------------------------------------

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

On 16 Jun 2006 09:19:27 -0700, clinttoris@hotmail.com wrote:
[color=blue]
>
>Turkbear wrote:[color=green]
>> On 16 Jun 2006 08:10:27 -0700, clinttoris@hotmail.com wrote:[/color][/color]
[color=blue]
>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'))"[/color]

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)

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

On Fri, 16 Jun 2006 17:28:35 +0100, "Pedro Leite" <naoehpreciso> wrote:
[color=blue]
>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
>-----------------------------------------------
>[/color]

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...

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

On Fri, 16 Jun 2006 12:05:36 -0400, "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote:
[color=blue]
>Turkbear wrote:
>
>Please do some snipping instead of making us scroll through all the
>irrelevant previous replies.[/color]


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

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

Turkbear wrote:[color=blue]
> On Fri, 16 Jun 2006 12:05:36 -0400, "Bob Barrows [MVP]"
> <reb01501@NOyahoo.SPAMcom> wrote:
>[color=green]
>> Turkbear wrote:
>>
>> Please do some snipping instead of making us scroll through all the
>> irrelevant previous replies.[/color]
>
>
> Sorry about that..but some folks also complain when the 'trail' of
> thread has been lost..[/color]

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"

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

Turkbear wrote:[color=blue]
> On 16 Jun 2006 09:19:27 -0700, clinttoris@hotmail.com wrote:
>[color=green]
> >
> >Turkbear wrote:[color=darkred]
> >> On 16 Jun 2006 08:10:27 -0700, clinttoris@hotmail.com wrote:[/color][/color]
>[color=green]
> >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'))"[/color]
>
> 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)[/color]



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?

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

On 19 Jun 2006 05:26:43 -0700, clinttoris@hotmail.com wrote:
[color=blue]
>[/color]
[color=blue]
>
>
>
>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?[/color]

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..)

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

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" <noone@nowhere.com> escreveu na mensagem
news:pbdd921t1q13d1m3dpjli96p2gvnclg7jg@4ax.com...[color=blue]
> On 19 Jun 2006 05:26:43 -0700, clinttoris@hotmail.com wrote:
>[color=green]
>>[/color]
>[color=green]
>>
>>
>>
>>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?[/color]
>
> 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..)
>
>[/color]

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

Turkbear wrote:[color=blue]
> On 19 Jun 2006 05:26:43 -0700, clinttoris@hotmail.com wrote:
>[color=green]
> >[/color]
>[color=green]
> >
> >
> >
> >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?[/color]
>
> 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..)[/color]

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 & ")"

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

Pedro Leite wrote:[color=blue]
> 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" <noone@nowhere.com> escreveu na mensagem
> news:pbdd921t1q13d1m3dpjli96p2gvnclg7jg@4ax.com...[color=green]
> > On 19 Jun 2006 05:26:43 -0700, clinttoris@hotmail.com wrote:
> >[color=darkred]
> >>[/color]
> >[color=darkred]
> >>
> >>
> >>
> >>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?[/color]
> >
> > 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..)
> >
> >[/color][/color]

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.

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

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


<clinttoris@hotmail.com> escreveu na mensagem
news:1150731403.814320.280090@c74g2000cwc.googlegr oups.com...[color=blue]
>
> Turkbear wrote:[color=green]
>> On 19 Jun 2006 05:26:43 -0700, clinttoris@hotmail.com wrote:
>>[color=darkred]
>> >[/color]
>>[color=darkred]
>> >
>> >
>> >
>> >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?[/color]
>>
>> 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..)[/color]
>
> 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 & ")"
>[/color]

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

another thing

just remembered.

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

-------------------------------------------------------
<clinttoris@hotmail.com> escreveu na mensagem
news:1150731403.814320.280090@c74g2000cwc.googlegr oups.com...[color=blue]
>
> Turkbear wrote:[color=green]
>> On 19 Jun 2006 05:26:43 -0700, clinttoris@hotmail.com wrote:
>>[color=darkred]
>> >[/color]
>>[color=darkred]
>> >
>> >
>> >
>> >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?[/color]
>>
>> 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..)[/color]
>
> 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 & ")"
>[/color]

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

On 19 Jun 2006 08:36:43 -0700, clinttoris@hotmail.com wrote:
[color=blue]
>
>Turkbear wrote:[/color]
[color=blue]
>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 & ")"[/color]

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

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

Turkbear wrote:[color=blue]
> On 19 Jun 2006 08:36:43 -0700, clinttoris@hotmail.com wrote:
>[color=green]
> >
> >Turkbear wrote:[/color]
>[color=green]
> >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 & ")"[/color]
>
> 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[/color]

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

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

On 19 Jun 2006 10:58:07 -0700, clinttoris@hotmail.com wrote:
[color=blue]
>
>Turkbear wrote:[color=green]
>> On 19 Jun 2006 08:36:43 -0700, clinttoris@hotmail.com wrote:
>>[color=darkred]
>> >[/color][/color]
>[color=green]
>>
>> 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[/color]
>
>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[/color]

That looks correct, is it not?

The entire dateTime values are being stored and returned..

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

Turkbear wrote:[color=blue]
> On 19 Jun 2006 10:58:07 -0700, clinttoris@hotmail.com wrote:
>[color=green]
> >
> >Turkbear wrote:[color=darkred]
> >> On 19 Jun 2006 08:36:43 -0700, clinttoris@hotmail.com wrote:
> >>
> >> >[/color]
> >[color=darkred]
> >>
> >> 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[/color]
> >
> >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[/color]
>
> That looks correct, is it not?
>
> The entire dateTime values are being stored and returned..[/color]


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.

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

On 19 Jun 2006 11:26:51 -0700, clinttoris@hotmail.com wrote:
[color=blue]
>[/color]
[color=blue][color=green][color=darkred]
>> >
>> >19/06/2006 01:55:25 PM[/color]
>>
>> That looks correct, is it not?
>>
>> The entire dateTime values are being stored and returned..[/color]
>
>
>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.[/color]


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

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

Turkbear wrote:[color=blue]
> On 19 Jun 2006 11:26:51 -0700, clinttoris@hotmail.com wrote:
>[color=green]
> >[/color]
>[color=green][color=darkred]
> >> >
> >> >19/06/2006 01:55:25 PM
> >>
> >> That looks correct, is it not?
> >>
> >> The entire dateTime values are being stored and returned..[/color]
> >
> >
> >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.[/color]
>
>
> 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[/color]

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.