equivalent of DateSerial() in T-SQL? (SQL2000)

  • Hi, I need to calculate the last day of the month in a stored proc--any month. In VB, I do this with the DateSerial function, but I see no equivalent in T-SQL. I see ways to split apart a date (DAY(), MONTH(), YEAR()), but no way to create a date from separate day, month, and year values. Am I missing something?

  • select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))

    more date formats are discussed in this article where I found this solution

    http://www.databasejournal.com/features/mssql/article.php/3076421

     

     

  • Wow! Thank you.

  • Or if you prefer:

    declare @date datetime,

               @days int,

               @month int,

               @year int

    select @month = 4, @days = 1, @year = 2005

    select @date = cast(@month as varchar) + '/' + cast(@days as varchar) + '/' 

    + cast(@year as varchar)

    select @date

    ----------------------------------------------

    2005-04-01 00:00:00.000

    (1 row(s) affected)

    ron

  • Nice solution, but I don't quite understand why you subtract 3 ms (and why you refer to month as m and mm in the same query). The following might be easier to read, although it is essentially the same:

    select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))

  • select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))

    won't catch any row on that day with a time portion > 00:00:00.000. If your data contains a time portion, such a query is likely to return incomplete results.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • 3ms is the smallest unit of time a datetime field can capture. So by subtracting 3ms from midnight, you get the latest possible time that could be captured on the earlier day, which will catch all time values for the previous day, not just those at midnight of the prior day.

  • these are the functions that I wrote for datederia, timeserial and datetimeserial

    I hope useful

    CREATE FUNCTION dbo.ufnDateTimeSerial (@Year int=null, @Month int=null, @Day int=null, @Hour int=null, @Minute int=null)

    /*to get the date pass year, month and day*/

    /*to get the time pass hour and minute*/

    RETURNS smalldatetime AS

    BEGIN

    DECLARE @strDate varchar(8)

    DECLARE @strTime varchar(5)

    IF @Year IS NULL OR @Month IS NULL OR @Day IS NULL

    SELECT @strDate = ''

    ELSE

    SELECT @strDate = dbo.ufnStrRAlign(CAST(@Year AS int), 4, '0') + dbo.ufnStrRAlign(CAST(@Month AS int), 2, '0') + dbo.ufnStrRAlign(CAST(@Day

    AS int), 2, '0')

    IF @Hour IS NULL OR @Minute IS NULL

    SELECT @strTime = ''

    ELSE

    SELECT @strTime = dbo.ufnStrRAlign(CAST(@Hour AS int), 2, '0') + ':' + dbo.ufnStrRAlign(CAST(@Minute AS int), 2, '0')

    RETURN CAST(dbo.ufnStrCat (' ', @strDate, @strTime, 0) AS smalldatetime)

    END

    CREATE FUNCTION dbo.ufnDateSerial (@Year int, @Month int, @Day int)

    RETURNS smalldatetime AS

    BEGIN

    RETURN dbo.ufnDateTimeSerial (@Year, @Month, @Day, null, null)

    END

    CREATE FUNCTION dbo.ufnTimeSerial (@Hour int, @Minute int)

    RETURNS smalldatetime AS

    BEGIN

    RETURN dbo.ufnDateTimeSerial (null, null, null,@Hour, @Minute)

    END

    CREATE FUNCTION dbo.ufnStrRAlign

    (@In varchar(8000), @OutLen integer, @strFiller varchar(1)=' ')

    RETURNS varchar(8000) AS

    BEGIN

    DECLARE @Result varchar(8000)

    DECLARE @FillLen integer

    SELECT @FillLen =@OutLen-LEN(ISNULL(@In, ''))

    IF @FillLen > 0

    SELECT @Result = REPLICATE(@strFiller, @FillLen) + ISNULL(@In, '')

    ELSE

    SELECT @Result = ISNULL(@In, '')

    RETURN @Result

    END

    CREATE FUNCTION dbo.ufnStrCat

    (@Separator varchar(8000), @Str1 varchar(8000), @str2 varchar(8000), @Distinct smallint)

    RETURNS varchar(8000) AS

    BEGIN

    DECLARE @Result varchar(8000)

    IF ( @Distinct<>0 AND ISNULL(@Str1, '') = ISNULL(@Str2, ''))

    SELECT @Result=ISNULL(@Str1, '')

    ELSE

    BEGIN

    IF @Str1 IS NOT NULL SELECT @Result=@Str1

    IF (LEN (LTRIM(ISNULL(@Result, '')))>0 AND LEN (LTRIM(ISNULL(@Str2, '')))>0) SELECT @Result=@Result + @Separator

    IF @Str2 IS NOT NULL SELECT @Result=ISNULL(@Result, '') + @Str2

    END

    RETURN @Result

    END

  • here's my 2p

    print convert(datetime,cast(2009*10000+3*100 + 5 as varchar(8)),112)

    just wondered exactly the same question...in fact here is my last day of the month bit

    set @DealDateTo=dateadd(dd,-1,dateadd(mm,1,convert(datetime,cast(@DealYear*10000+@DealMonth*100 + 1 as varchar(8)),112)))

  • Conversions varchar to datetime and back are the worst cpoosible conversions from performance point of view.

    They may be very comfortable for a human eye, so keep them where they belong - in UI and reporting interfaces.

    The correct solution was posted at the beginning:

    select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm, 0, getdate() )+1, 0))

    Every other one is a "second best".

    _____________
    Code for TallyGenerator

  • Ian Yates (12/9/2005)


    3ms is the smallest unit of time a datetime field can capture. So by subtracting 3ms from midnight, you get the latest possible time that could be captured on the earlier day, which will catch all time values for the previous day, not just those at midnight of the prior day.

    This is true... for now. With 2008 and datetime2, you can go past this level of accuracy, which will introduce a possible gap (although admittedly pretty tiny) if you change to a new data type. The best solution is usually just to use a < first day of the next month instead of <= the last milliseconds of the last day of the current month.

    That said, if you have to do it the other way, -3ms is the best you can do.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Garadin (3/29/2010)


    Ian Yates (12/9/2005)


    3ms is the smallest unit of time a datetime field can capture. So by subtracting 3ms from midnight, you get the latest possible time that could be captured on the earlier day, which will catch all time values for the previous day, not just those at midnight of the prior day.

    This is true... for now. With 2008 and datetime2, you can go past this level of accuracy, which will introduce a possible gap (although admittedly pretty tiny) if you change to a new data type. The best solution is usually just to use a < first day of the next month instead of <= the last milliseconds of the last day of the current month.

    That said, if you have to do it the other way, -3ms is the best you can do.

    Things do change. I was never comfortable with the whole 3ms thing - others had suggested it in the posts but the original poster wonder "why subtract 3ms".

    Frankly in hindsight the best answer would be to find out more about what the person was trying to do and explain that rather than

    WHERE [theDate] between [MidnightOfADate] and [LastPossibleTimestampOfADate]

    he/she is better off doing

    WHERE [theDate] >= [MidnightOfADate] and [theDate] < [MidnightOfADate+1]

    which is pretty what you're suggesting. It's definitely the better way to go. Much easier to maintain and understand 🙂

    Jesper's solution gives a correct answer if all you want is the date - it wouldn't be useful for ranged queries necessarily but would be useful for report display, etc.

Viewing 12 posts - 1 through 11 (of 11 total)

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