How to substitute the DateSerial function to make my SQL execute in MS SQL Server

Posted on 2005-04-22
Last Modified: 2011-10-03
Is there a way to rewrite the code that follows, in Access, that I could place in a SQL string and execute in a MS SQL Server Stored Procedure:

I can't use the following code because DateSerial is not valid in MS SQL Server.
I need to take the date in tblA.dteA and compare it to 6/30th of the year determined by a user by selecting a year from a list box
named lstYear. The year selected is then reduced by the value in tblA.intProp.

tblA.dteA - value of a field from tblA                                                               (for ex: 02/22/1998)
lstYear - calendar year value selected from a list box                                       (for ex: 2005)
tblA.intProp - value of a field from tblA (integer value of 1 or 2 or 3 or 4 or 5)    (for ex: a value of 5

the WHERE CLAUSE I formerly used in my SQL string but which is not acceptable in MS SQL Server is as follows:

WHERE ((tblA.dteA <= DateSerial ("lstYear.Value) & "- tblA.intProp,6,30))

in my example above this WHERE clause translates to:

WHERE 2/22/1998 <= (2005 - 5) ,6,30 which translates to 2/22/1998 <= 6/30/2000

Question by:zimmer9
    LVL 11

    Expert Comment

    Try the following
    WHERE ((tblA.dteA <= DateSerial (Form!NameOfForm!lstYear.Value - tblA.intProp,6,30))

    should work in query


    Author Comment

    Do you think DateSerial is permissible to use in MS SQL Server stored procedures ?

    Author Comment

    Is there a way to convert this using the DateAdd fucntion instead ?
    LVL 11

    Accepted Solution

    Sorry my mistake, somehow I thought you wanted to convert from MS SQL Server to Access

    I would write DateAdd as follows in Access

    DateAdd("y",-tblA.intProp, #2005/6/30#)

    but you need some way to pass year as parameter value to your stored procedure and that's something I don't Know anything about

    LVL 11

    Expert Comment

    Thanks zimmer9


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now