Post reply

DateSerial() Equivalent?

  • August 18, 2015 at 12:07 pm

    #320737

    What is the best formula to do this in SQL?

    This is from Access

    SELECT Year([EndDate]) AS SeasonYear, PersonMembership.EndDate

    FROM PersonMembership

    GROUP BY Year([EndDate]), PersonMembership.EndDate

    HAVING (((PersonMembership.EndDate)>=DateSerial(Year(Date()),6,30)));

  • August 18, 2015 at 12:11 pm

    #1820850

    i found this user defined function that does the same thing:

    http://www.experts-exchange.com/articles/670/DateSerial-Function-for-Microsoft-SQL-Server.html

    SELECT dbo.DateSerial(Year(getdate()),6,30)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 18, 2015 at 12:42 pm

    #1820860

    Here's one with the same functionality but as an inline table valued function.

    CREATE FUNCTION dbo.iDateSerial

    (

    @year int ,

    @month int ,

    @day int

    )

    RETURNS TABLE

    AS

    RETURN

    SELECT DATEADD(dd, @day - 1, DATEADD(mm, @month - 1, CAST( @year as CHAR(4)))) AS DateSerial WHERE @year BETWEEN 1900 AND 9999

    UNION ALL

    SELECT DATEADD(dd, @day - 1, DATEADD(mm, @month - 1, 0)) WHERE @year < 1900

    UNION ALL

    SELECT DATEADD(dd, @day - 1, DATEADD(mm, DATEDIFF( mm, 0, @month), - 1)) WHERE @year > 9999;

    In 2012 there's also an available function called DATEFROMPARTS() which works in a similar way.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 18, 2015 at 12:55 pm

    #1820865

    Thank you! it works!

    Yay!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply