Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Jan 2007
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Set x="Date() 8:00:00AM"

    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.

    In ASP:

    <%x="Date() 8:00:00AM" %>

    and do a sql statement:

    <% mysql="Update Table1 set Field1=DateAdd(d, 1, x) where ID='"&Request("something")&"'"
    con.execute(mysql)%>

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

    Thanks

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Have you done a response.write( x )?

    I think that you will find that it doesn't display the way you want it.

    Try:
    Code:
    <% 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:

    Code:
    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    New Coder
    Join Date
    Jan 2007
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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??

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #5
    New Coder
    Join Date
    Jan 2007
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #6
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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:
    http://devguru.com/technologies/t-sql/index.asp

    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #7
    New Coder
    Join Date
    Jan 2007
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #8
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    the date() function is not provided by mssql, use getdate() instead
    I am the luckiest man in the world


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •