Posted on 2008-04-16 11:36:07 by Todd_Calhoun

#2: Re: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

Posted on 2008-04-16 12:14:46 by reb01501

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.

#4: Re: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

Posted on 2008-04-16 15:09:34 by reb01501

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.

#5: Re: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

Posted on 2008-04-16 15:27:28 by Todd_Calhoun

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]" <reb01501@NOyahoo.SPAMcom> 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.
>
>

#6: Re: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

Posted on 2008-04-16 16:01:39 by reb01501

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:

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:

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

#7: Re: UPDATING/INSERTING DATETIME FIELDS FROM ASP?

Posted on 2008-04-16 16:33:02 by Todd_Calhoun

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

Rob

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> 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.
>
>