• mar.ko (9/4/2015)


    Sean Lange (9/4/2015)


    mar.ko (9/4/2015)


    I tried this:

    SELECT Convert(smalldatetime,Round(Convert(float,(dbo.fnDateOnly(getDate())))+16.0000000/24.0000000,6))

    Result: 2015-09-04 16:00:00

    It works (FnDateOnly strips the time).

    Is there a more efficient way ?

    What are you trying to do? And what is fnDateOnly?

    RTFM: (FnDateOnly strips the time) ex: fnDateOnly('2015-09-04 16:00:00') = 2015-09-04 00:00:00

    I am trying to create a datetime dimension table with a key of hour. So I need to go thru the next 10 years and create a row for every hour.

    DateTimeKey is smalldatetime format. My technique would not work with datetime format....

    Telling someone to "RTFM" isn't professional nor is it the way to make friends on this or any other forum.

    You should also continue to forget about FORMAT in SQL Server because it's been tested to be 44 times slower than any other method that you'll find to do the same thing. Lowell and Sean have both given you solutions that will blow the doors off of the use of FORMAT. In the same vein as your post, perhaps if you did a little RTFS and some testing, you'd know that. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)