Results 1 to 6 of 6
  1. #1
    AJ_25 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2018
    Posts
    12

    SQL - Need correct Syntax for SQL Server

    Hi,

    I have the following code taken from Access SQL below but need the the correct syntax for this to work in SQL server, does anyone know how to amend my SQL code to work please (It's a lot to ask I do appreciate),or anyone know of a conversion system which automatically converts for us (unlikely I know)?


    DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in])),1) AS Date_From,

    DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in]))+IIf(Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)=0,1,Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)),1)-1 AS Date_To,



    DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in]))+IIf(Round((DateValue([Date_Required])-DateValue(DateSerial(Year([Date_in]),Month([Date_in]),1)))/(365/12),0)=0,1,Round((DateValue([Date_Required])-DateValue(DateSerial(Year([Date_in]),Month([Date_in]),1)))/(365/12),0)),1)-1 AS Date_To_new,

    DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in]))+IIf(Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)=0,1,Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)),1)-1 AS Date_To_old,

    DateSerial(Year([Date_Required]),Month([Date_Required])+1,0) AS Date_To2,

    Year([Date_From]) & Format(Month([Date_From]),"00") AS Period_from,

    Year([Date_To]) & Format(Month([Date_To]),"00") AS Period_to,

    Year([Date_To2]) & Format(Month([Date_To2]),"00") AS Period_to2,

    IIf(Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)=0,1,Round((DateValue([Date_Required])-DateValue([Date_in]))/(365/12),0)) AS Months,

    Year([Date_To]+1) & Format(Month([Date_To]+1),"00") AS Renewal_Period,

    Thanks all in advance for your help

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    dateserial is a vba function which won't work on sql server - although an odbc drive will do what it can but the query will be exceedingly slow

    use dateadd instead

    for example this

    DateSerial(Year(DateValue([Date_in])),Month(DateValue([Date_in])),1)

    can be written as this

    Dateadd("d",-day(Date_in)-1,[Date_in])


    although sql server uses other things rather than 'd' - see this link for more info

    https://www.w3schools.com/sql/func_s...er_dateadd.asp

    See this for the other functions

    https://www.w3schools.com/sql/sql_ref_sqlserver.asp

    I presume your fields are dates so not sure why you are using all those datevalues

  3. #3
    AJ_25 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2018
    Posts
    12
    Thanks for the tips but I am really struggling to get the correct syntax, any additional help would be useful please as I can't crack it sorry

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have a read here for a load odate examples in SQL
    https://blog.sqlauthority.com/2008/0...pecific-dates/
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    As SQL Servers built-in date functions are lacking, and you usually need them often in various databases, create a separate database where you define various UDF's (User defined Functions). (You can create UDF's in System too, but there may be complications.)

    E.g. you create database Utiities. In Utilities.Programmability.Functions.Scalar-valued Functions you create a function udf_Date
    Code:
    USE [utilities]
    GO...SET ANSI_NULLSON
    GO
    SET QUOTED_IDENTIFIERON
    GO
    ...
    CREATE FUNCTION [dbo].[udf_Date] 
    (
    -- Add the parameters for the function here
        @Year INT, @Month INT, @Day INT
    )
    RETURNS datetime
    AS
    BEGIN
         -- Declare the return variable here
         DECLARE @Date DATETIME
         -- Add the T-SQL statements to compute the return value here
         SET @Date =
              (CASE
                   WHEN @Month = 2 AND @Day >28 AND @Day < 31 THEN
                        DateAdd(yyyy,@Year-1900,DateAdd(m,@Month,0))- 1 -DateAdd(yyyy,@Year-1900,DateAdd(m,@Month-1,31-1)) 
                   ELSE 0 
              END)+
              DateAdd(yyyy,@Year-1900,DateAdd(m,@Month-1,@Day-1))
         --    RETURN @Date
         RETURN @Date
    END
    GO
    
    You have to allow all users access to database Utilities.

    Now your 1st formula will be
    Code:
    utilities.dbo.udf_Date(Year([Date_in]),Month(([Date_in]),1)
    The code used in another one ( a function which adds a number of days to today's date, you can easily change it to add a number of days to any date - add a parameter @Shift to function udf_Date(), an add last SET expression from udf_FromToday)
    Code:
    ...CREATE FUNCTION [dbo].[udf_FromToday] 
    
    (-- Add the parameters for the function here
    
    
        @Shift int= 0
    
    )
    DECLARE @Date DATETIME
    
    
    -- Add the T-SQL statements to compute the return value here
    DECLARE @Year smallint
    DECLARE @Month smallint
    DECLARE @Day smallint
    SET @Year =YEAR(GetDate())
    SET @Month =MONTH(GetDate())
    SET @Day =DAY(GetDate())
    SET @Date =      utilities.dbo.udf_Date(@Year, @Month, @Day + @Shift)
       
    -- Return the result of the function
    RETURN @Date
    END

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I think the problem here is that you start looking at the case from an Access point of view. That's like building an Access application from an Excel point of view. You overcomplicate things.
    Rethink the whole process from a T-SQL point of view. You'll find that T-SQL is far more flexible and powerful than Access query language, but you need to learn the principles. Just as you need to understand the principles of relational database design when going from Excel to Access. There are a lot of T-SQL papers, books and user guides on line.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Convert SQL Server Syntax To Access Query Syntax
    By chalupabatman in forum Queries
    Replies: 1
    Last Post: 10-18-2017, 08:53 PM
  2. Correct Syntax
    By bidbud68 in forum Programming
    Replies: 6
    Last Post: 10-19-2012, 01:09 AM
  3. SQL Correct Syntax
    By tbassngal in forum Queries
    Replies: 11
    Last Post: 09-01-2011, 01:55 PM
  4. dcount syntax correct?
    By lbgtp in forum Reports
    Replies: 2
    Last Post: 12-29-2010, 10:01 AM
  5. What is the correct syntax for
    By giladweil in forum Access
    Replies: 1
    Last Post: 07-29-2010, 04:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums