SQL Server Syntax

Posted on 2011-10-31
Last Modified: 2012-05-12
I've just switched the backend of my application from MS Access to SQL Server.
This sytax worked with my Access database but it won't return anything from SQL Server:
SELECT Count(id) AS Countid FROM MyTable where PersonAddedOn like '%"& (Date()) & "%'
Would you please let me know what the correct syntax is?
Thank you
Question by:baxtalo
    LVL 15

    Expert Comment

    Try this.

    '%' + CONVERT(varchar(10),GETDATE(),101) + '%'
    LVL 12

    Assisted Solution

    The function Date() in Access returns todays date.   The equivalent in SQL Server is GetDate( which returns current date time.  

    Also, concatenation is + rather than &

    However, getting the same results as your Access query will requiire you to control the way in which the date is rendered as a string.

    An example of one syntax is shown below

    Select Count(id) as CountId
    From MyTable
    Where PersonAddedOn like '%' + SELECT CONVERT(VARCHAR, GETDATE(), 101)  + '%'

    This formats todays date in MM/DD/YYYY format using the 101 format.
    Change the 101 if you need a different date format.
    More information on controlling the date format can be found here:


    Author Comment

    I've just tried it but it didn't return anything. I forgot to mention that I'm using Classic ASP on my page. This code is on my Confirmation page and my goal is to display how many submissions have been sent to the database today. In my SQL Server database the PersonAddedOn column is 'datetime' and it records the date and time of submission when I enter a record.
    Set objConn = CreateObject("ADODB.Connection")
    objConn.ConnectionString = "Provider=sqloledb;SERVER=DASTVMSQLCX01;DATABASE=MyDatabase;UID=DDUser;PWD=DB123456;"
    ' To get the RecordCount, without the [RecordSet = CreateObject]
    ' This keeps the code a little cleaner.
    Set sqlCount = Server.CreateObject("ADODB.Command")
    sqlCount.Prepared = true
    sqlCount.CommandText = "SELECT Count(id) AS Countid FROM MyTable where PersonAddedOn like '%' + CONVERT(varchar(10),GETDATE(),101) + '%' "
    set objRs = sqlCount.Execute
    CountID = int(objRs("CountID"))
    Set ObjRs = Nothing
    Submissions Today

    Open in new window

    LVL 15

    Accepted Solution

    You'll want to convert the PersonAddOn to then.  

    where CONVERT(varchar(10),PersonAddedOn,101) like '%' + CONVERT(varchar(10),GETDATE(),101) + '%'

    Author Closing Comment

    Thank you very much; it worked.
    I've learned a lot.
    LVL 12

    Expert Comment

    Since your date is stored as date time, then you just need to ignore the time part if that is your requirement

    Select Count(id) as CountId
    From MyTable
    Where  CONVERT(VARCHAR, PersonAddedOn , 101)  

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now