 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ASP / Database Access / April 2008UPDATING/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 > 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.  SignatureMicrosoft 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" 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 >> Hi, >> [quoted text clipped - 32 lines] > > Simpler still: use stored procedures. > 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. SignatureMicrosoft 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. 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 >> Thanks Bob, >> [quoted text clipped - 14 lines] > and it's absolutely critical that the date and time be stored > separately, then you might consider using another datatype. > 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. SignatureMicrosoft 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. That works brilliantly Bob, thank you so much, hope you have a nice day. Rob >> I think I will need to use calculations at some point, is there a way >> to pass the Now() value into the datetime field. [quoted text clipped - 24 lines] > "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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.