08-21-2007, 03:19 AM
Hi there...I am trying to do a really simple thing but cant seem to get the syntax right:(
I am trying to set a variable x which should be in datetime format and equal to todays date and time=8:00:00 AM. Then I am trying to just add 1 to todays date so I can basically get tomorrow morning 8 clock. This is for a reminder that should popup tomm morning at 8 am.
<%x="Date() 8:00:00AM" %>
and do a sql statement:
<% mysql="Update Table1 set Field1=DateAdd(d, 1, x) where ID='"&Request("something")&"'"
My problem is that the update works great however the value of variable x is incorrect...I tried doing a bunch of things like GetDate() or '"&Date()&"', however none work fine...However if I replace Date() with say 8/20/2007, everything works fine...I just need help getting the right syntax....
08-21-2007, 03:26 AM
Have you done a response.write( x )?
I think that you will find that it doesn't display the way you want it.
<% x = Date() & " 08:00:00" %>
Timestamp (or datetime fields) do not accept AM or PM information, only 24 hour time. The Regional Settings of your computer determine if you show the AM / PM information for the hours.
If you want to update this data in the database, try:
UPDATE table1 SET Field1 = concat(DateAdd("d",1,Date()),' 08:00:00') WHERE ID = '"&Request("something")&"'"
Also, as a side note, if ID is numeric, you do not need to wrap it in single quotes.
08-21-2007, 02:53 PM
Hey Daemon....Thanks a lot for your reply...it worked like a charm:)
However the concat didn't work...it dint update the table when I used concat...any ideas??
08-21-2007, 03:57 PM
Are you using MySQL, MSSQL, or MS Access?
MySQL and MSSQL, it should have worked fine. However, Access doesn't understand a lot of the standard SQL, and may not know the CONCAT function.
08-21-2007, 04:41 PM
Hey there...I think iam using Tsql and asp---by the way how do i find out if its tsql or mysql or MSsql...sorry complete newbie here:(
08-21-2007, 05:42 PM
That's OK -- Newbies are always welcome, as long as they learn. :)
TSQL is more commonly known as Transact-SQL, an "extension" of SQL by Microsoft and Sybase.
T-SQL does not know "concat", which is an ANSI standard SQL statement.
Here is a list of all of the known functions in T-SQL:
You are probably using the Microsoft version, which comes with SQL Server and SQL Server Express.
That's why you can't use CONCAT.
I do not know how to determine your version in T-SQL, so your best bet is to use Add/Remove Programs under CONTROL PANEL and look there.
In Access, you can open up your DB and it will tell you, or you can just use "SELECT VERSION;"
In MySQL, it's "SHOW VARIABLES LIKE 'VERSION';".
However, by far the easiest way is to look at your Connection string. If you are using DSNLess connections, the PROVIDER section should tell you the database Vendor and version you are using.
08-21-2007, 08:14 PM
Hey there....I saw in Add/Remove programs, it says Microsoft SQL Server 2000...so I guess thats it then:)
Thanks again for your help and thanks for the link to the list of functions supported by tsql:)
08-21-2007, 09:05 PM
the date() function is not provided by mssql, use getdate() instead