UPDATING/INSERTING DATETIME FIELDS FROM ASP?

Hi,

Is there a quick way to understanding how to insert/update
datetime/shortdatetime fields in an SQL database from an ASP?

Many thanks,

Rob

Re: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

anon wrote:
> Hi,
>
> Is there a quick way to understanding how to insert/update
> datetime/shortdatetime fields in an SQL database from an ASP?
>
> Many thanks,
>
It couldn't be simpler. Use parameters.

Insert example:

dim cn, cmd,sql,dateval
dateval = dateserial(2008,4,15)
sql="insert into tablename(datetimecol) values (?)"
set cn=createobject("adodb.connection")
cn.open " ... "
set cmd=createobject("adodb.command")
cmd.commandtext = sql
cmd.commandtype = 1 'adCmdText
set cmd.activeconnection=cn
cmd.execute ,array(dateval), 128

Update example:

dim cn, cmd,sql,dateval
dateval = dateserial(2008,4,15)
sql="update tablename set datetimecol =?"
set cn=createobject("adodb.connection")
cn.open " ... "
set cmd=createobject("adodb.command")
cmd.commandtext = sql
cmd.commandtype = 1 'adCmdText
set cmd.activeconnection=cn
cmd.execute ,array(dateval), 128


Simpler still: use stored procedures.

--
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: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

Thanks Bob,

That seems to moved my problem forward a point, but I a get a value of
00:00:00 in my SQL field table.

Ideally I want to update/insert one column with the current date and another
with the current time.

Many thanks,

Rob

However I the field is being updated.inserted to a value of 01/01/1900 and
am having
"Bob Barrows [MVP]" wrote in message
news:eAskrq6nIHA.524@TK2MSFTNGP05.phx.gbl...
> anon wrote:
>> Hi,
>>
>> Is there a quick way to understanding how to insert/update
>> datetime/shortdatetime fields in an SQL database from an ASP?
>>
>> Many thanks,
>>
> It couldn't be simpler. Use parameters.
>
> Insert example:
>
> dim cn, cmd,sql,dateval
> dateval = dateserial(2008,4,15)
> sql="insert into tablename(datetimecol) values (?)"
> set cn=createobject("adodb.connection")
> cn.open " ... "
> set cmd=createobject("adodb.command")
> cmd.commandtext = sql
> cmd.commandtype = 1 'adCmdText
> set cmd.activeconnection=cn
> cmd.execute ,array(dateval), 128
>
> Update example:
>
> dim cn, cmd,sql,dateval
> dateval = dateserial(2008,4,15)
> sql="update tablename set datetimecol =?"
> set cn=createobject("adodb.connection")
> cn.open " ... "
> set cmd=createobject("adodb.command")
> cmd.commandtext = sql
> cmd.commandtype = 1 'adCmdText
> set cmd.activeconnection=cn
> cmd.execute ,array(dateval), 128
>
>
> Simpler still: use stored procedures.
>
> --
> 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: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

anon wrote:
> Thanks Bob,
>
> That seems to moved my problem forward a point, but I a get a value of
> 00:00:00 in my SQL field table.
>
How are you verifying this?
Have you run a trace using SQL Profiler to enable you to see the actual
sql statements being run on the sql server when the asp page runs?

> Ideally I want to update/insert one column with the current date and
> another with the current time.
>

As the name implies, datetime datatypes always store both date and time.
If no time is supplied, midnight (00:00:00) is stored. If no date is
supplied, the seed date (12/31/1899, I think - it's not really
important) is stored. If you don't need to do date or time arithmetic,
and it's absolutely critical that the date and time be stored
separately, then you might consider using another datatype.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

I think I will need to use calculations at some point, is there a way to
pass the Now() value into the datetime field.
I would have used the timestamp but it isn't available on my SQL server.

Many thanks,

Rob

"Bob Barrows [MVP]" wrote in message
news:%238E5eM8nIHA.4912@TK2MSFTNGP03.phx.gbl...
> anon wrote:
>> Thanks Bob,
>>
>> That seems to moved my problem forward a point, but I a get a value of
>> 00:00:00 in my SQL field table.
>>
> How are you verifying this?
> Have you run a trace using SQL Profiler to enable you to see the actual
> sql statements being run on the sql server when the asp page runs?
>
>> Ideally I want to update/insert one column with the current date and
>> another with the current time.
>>
>
> As the name implies, datetime datatypes always store both date and time.
> If no time is supplied, midnight (00:00:00) is stored. If no date is
> supplied, the seed date (12/31/1899, I think - it's not really
> important) is stored. If you don't need to do date or time arithmetic,
> and it's absolutely critical that the date and time be stored
> separately, then you might consider using another datatype.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

anon wrote:
> I think I will need to use calculations at some point, is there a way
> to pass the Now() value into the datetime field.

Sure, it was possible to pass the result of the dateSerial function, so
why would it not be possible to pass the result of the now() function?
But there is an easier way: use the equivalent T-SQL function. Like
this:

sql = insert into table (datetimefield) values (getdate())"
cn.execute sql,,129

Again, you will not get only time stored. There is no way to store a
time without a date in a datetime field. if you want to store the seed
date so that the time is disassociated with the current date then you
can do something like this:

sql = insert into table (timefield) values (" & _
dateadd(d,datediff(d,getdate(),0),getdate())"
cn.execute sql,,129


> I would have used the timestamp but it isn't available on my SQL
> server.

There is a timestamp datatype in SQL, but, despite its name, it does NOT
store anything to do with time. It's a binary value that is
automatically updated when a row changes and thus identifies a row's
"version". It provides a quick way to determine if a row's contents have
been changed since the last time you retrieved values from that row.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

That works brilliantly Bob, thank you so much, hope you have a nice day.

Rob


"Bob Barrows [MVP]" wrote in message
news:e7sxlp8nIHA.3976@TK2MSFTNGP03.phx.gbl...
> anon wrote:
>> I think I will need to use calculations at some point, is there a way
>> to pass the Now() value into the datetime field.
>
> Sure, it was possible to pass the result of the dateSerial function, so
> why would it not be possible to pass the result of the now() function?
> But there is an easier way: use the equivalent T-SQL function. Like
> this:
>
> sql = insert into table (datetimefield) values (getdate())"
> cn.execute sql,,129
>
> Again, you will not get only time stored. There is no way to store a
> time without a date in a datetime field. if you want to store the seed
> date so that the time is disassociated with the current date then you
> can do something like this:
>
> sql = insert into table (timefield) values (" & _
> dateadd(d,datediff(d,getdate(),0),getdate())"
> cn.execute sql,,129
>
>
>> I would have used the timestamp but it isn't available on my SQL
>> server.
>
> There is a timestamp datatype in SQL, but, despite its name, it does NOT
> store anything to do with time. It's a binary value that is
> automatically updated when a row changes and thus identifies a row's
> "version". It provides a quick way to determine if a row's contents have
> been changed since the last time you retrieved values from that row.
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>