Midnight Date Function

  • Hi All

    Is there any predefine methods in SQL server to get datetime on midnight format (2008-10-10 00:00:00.000), if i use GetDate(), it gives me the current time, i am using a UDF to create the above format, wondering is there any way on SQL 2005 to do this???

    Cheers

    🙂

  • There is no built-in function in SQL Server 2000/2005. (in 2008, you can use the DATE data type).

    You can strip off the TIME part from a datetime value by running the following code.

    SELECT DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()))

    .

  • Thanks Jacob

    thats what i was looking for

    🙂

  • You are welcome 🙂

    .

  • You can also use this...

    SELECT convert(datetime,convert(varchar,GetDate(),10))

    Just replace the GetDate() function with your passed in value.

    It would be interesting to see what performed better. 😉

    Gary Johnson
    Sr Database Engineer

  • Gary Johnson (10/10/2008)


    It would be interesting to see what performed better. 😉

    Actually the DateAdd/DateDiff method seems to perform slightly better (like 6 milliseconds better over 25,000 rows) than convert to varchar or char then back to date. In a close second (like 1 millisecond worse than DateAdd/DateDiff over 25,000 rows) was convert to float then floor it and convert back to date. OK, so I was REALLY bored today. :hehe:

  • More than slightly better...

    http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Chris Harshman (10/10/2008)


    Gary Johnson (10/10/2008)


    It would be interesting to see what performed better. 😉

    Actually the DateAdd/DateDiff method seems to perform slightly better (like 6 milliseconds better over 25,000 rows) than convert to varchar or char then back to date. In a close second (like 1 millisecond worse than DateAdd/DateDiff over 25,000 rows) was convert to float then floor it and convert back to date. OK, so I was REALLY bored today. :hehe:

    It would be really cool if you posted your test code... 😉

    --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)

  • Guess I'll throw the testing I've done in the past into the ring... 🙂

    The testing below takes the "display" out of the picture using a throw-away variable of the DATETIME datatype as the destination for the results. You'll be interested in how fast an implicit conversion can really be as well as how the order of the DATEADD/DATEDIFF parameters affect things...

    First, here's my usual general purpose million row test table... it doesn't take long to run... try it...

    DROP TABLE JBMTest

    GO

    --===== Create and populate a 1,000,000 row test table.

    -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

    -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

    -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

    -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

    -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

    -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

    -- for all rows.

    -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

    -- Jeff Moden

    SELECT TOP 1000000

    RowNum = IDENTITY(INT,1,1),

    SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

    SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65),

    SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

    SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

    SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

    SomeHex12 = RIGHT(NEWID(),12)

    INTO dbo.JBMTest

    FROM Master.dbo.SysColumns t1

    CROSS JOIN Master.dbo.SysColumns t2

    --===== A table is not properly formed unless a Primary Key has been assigned

    ALTER TABLE dbo.JBMTest

    ADD PRIMARY KEY CLUSTERED (RowNum)

    ... and, here's the tests... these work on both 2k and 2k5. According to Gail's fine blog, some of these tests will not work on 2k8 (which totally pisses me off... they keep taking away "features" that break good folks code)...

    DECLARE @BitBucketDATETIME DATETIME

    PRINT '========== BASELINE =========='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = SomeDate

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '========== DATEADD/DATEDIFF 1 =========='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = DATEADD(d, 0, DATEDIFF(d, 0, SomeDate))

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '========== DATEADD/DATEDIFF 2 =========='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = DATEADD(dd,DATEDIFF(dd,0,SomeDate),0)

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '========== DATEDIFF Implicit =========='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = DATEDIFF(d, 0, SomeDate)

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '========== CONVERT =========='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = CONVERT(DATETIME,CONVERT(VARCHAR,SomeDate,100))

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '========== CONVERT Implicit=========='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = CONVERT(VARCHAR,SomeDate,100)

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '===== FLOOR ====='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = CAST(FLOOR(CONVERT(FLOAT, SomeDate)) AS DATETIME)

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '===== FLOOR Implicit ====='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = FLOOR(CONVERT(FLOAT, SomeDate))

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '===== ROUNDING 1 ====='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = CAST(CAST(SomeDate - 0.50000004 AS INT) AS DATETIME)

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '===== ROUNDING 1 Implicit ====='

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = CAST(SomeDate - 0.50000004 AS INT)

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '===== ROUNDING 2 ======'

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = CAST(ROUND(CAST(SomeDate AS FLOAT),0,1) AS DATETIME)

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    PRINT '===== ROUNDING 2 Implicit ======'

    SET STATISTICS TIME ON

    SELECT @BitBucketDATETIME = ROUND(CAST(SomeDate AS FLOAT),0,1)

    FROM dbo.JBMTest WITH (NOLOCK)

    SET STATISTICS TIME OFF

    PRINT REPLICATE('=',80)

    Here are the results I got on my humble 6 year old P5 1.8Ghz with 1 GB Ram and IDE hard drives on 2k5...

    [font="Courier New"]========== BASELINE ==========

    SQL Server Execution Times:

    CPU time = 781 ms, elapsed time = 882 ms.

    ================================================================================

    ========== DATEADD/DATEDIFF 1 ==========

    SQL Server Execution Times:

    CPU time = 1265 ms, elapsed time = 1318 ms.

    ================================================================================

    ========== DATEADD/DATEDIFF 2 ==========

    SQL Server Execution Times:

    CPU time = 969 ms, elapsed time = 1001 ms.

    ================================================================================

    ========== DATEDIFF Implicit ==========

    SQL Server Execution Times:

    CPU time = 860 ms, elapsed time = 888 ms.

    ================================================================================

    ========== CONVERT ==========

    SQL Server Execution Times:

    CPU time = 6140 ms, elapsed time = 6414 ms.

    ================================================================================

    ========== CONVERT Implicit==========

    SQL Server Execution Times:

    CPU time = 6453 ms, elapsed time = 6689 ms.

    ================================================================================

    ===== FLOOR =====

    SQL Server Execution Times:

    CPU time = 1282 ms, elapsed time = 1292 ms.

    ================================================================================

    ===== FLOOR Implicit =====

    SQL Server Execution Times:

    CPU time = 1218 ms, elapsed time = 1243 ms.

    ================================================================================

    ===== ROUNDING 1 =====

    SQL Server Execution Times:

    CPU time = 891 ms, elapsed time = 904 ms.

    ================================================================================

    ===== ROUNDING 1 Implicit =====

    SQL Server Execution Times:

    CPU time = 922 ms, elapsed time = 952 ms.

    ================================================================================

    ===== ROUNDING 2 ======

    SQL Server Execution Times:

    CPU time = 1703 ms, elapsed time = 1767 ms.

    ================================================================================

    ===== ROUNDING 2 Implicit ======

    SQL Server Execution Times:

    CPU time = 1516 ms, elapsed time = 1581 ms.

    ================================================================================

    [/font]

    ... and here's the same test on 2k...

    [font="Courier New"]========== BASELINE ==========

    SQL Server Execution Times:

    CPU time = 5 ms, elapsed time = 5 ms.

    SQL Server Execution Times:

    CPU time = 594 ms, elapsed time = 1088 ms.

    ================================================================================

    ========== DATEADD/DATEDIFF 1 ==========

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 969 ms, elapsed time = 994 ms.

    ================================================================================

    ========== DATEADD/DATEDIFF 2 ==========

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 875 ms, elapsed time = 894 ms.

    ================================================================================

    ========== DATEDIFF Implicit ==========

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 734 ms, elapsed time = 748 ms.

    ================================================================================

    ========== CONVERT ==========

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 10156 ms, elapsed time = 10230 ms.

    ================================================================================

    ========== CONVERT Implicit==========

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 10188 ms, elapsed time = 10298 ms.

    ================================================================================

    ===== FLOOR =====

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 1906 ms, elapsed time = 1968 ms.

    ================================================================================

    ===== FLOOR Implicit =====

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 1885 ms, elapsed time = 1885 ms.

    ================================================================================

    ===== ROUNDING 1 =====

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 730 ms, elapsed time = 730 ms.

    ================================================================================

    ===== ROUNDING 1 Implicit =====

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 703 ms, elapsed time = 713 ms.

    ================================================================================

    ===== ROUNDING 2 ======

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 3063 ms, elapsed time = 3066 ms.

    ================================================================================

    ===== ROUNDING 2 Implicit ======

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 3031 ms, elapsed time = 3082 ms.

    ================================================================================

    [/font]

    I'm thinking that folks should avoid the CONVERT method for truncating dates. 😉

    --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)

  • Jeff Moden (10/11/2008)


    ... and, here's the tests... these work on both 2k and 2k5. According to Gail's fine blog, some of these tests will not work on 2k8 (which totally pisses me off... they keep taking away "features" that break good folks code)...

    Where did I say that?

    All your tests work fine on 2008. One of the ones I did (on the page I linked above) won't work on 2005 or lower because I use the DATE datatype. Some won't work when using the new datetime2 datatype as Datetime2 cannot be converted (explicitly or implicitly) to int or float.

    They work fine on the old datetime though

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/11/2008)


    Jeff Moden (10/11/2008)


    ... and, here's the tests... these work on both 2k and 2k5. According to Gail's fine blog, some of these tests will not work on 2k8 (which totally pisses me off... they keep taking away "features" that break good folks code)...

    Where did I say that?

    All your tests work fine on 2008. One of the ones I did (on the page I linked above) won't work on 2005 or lower because I use the DATE datatype. Some won't work when using the new datetime2 datatype as Datetime2 cannot be converted (explicitly or implicitly) to int or float.

    They work fine on the old datetime though

    Oh, sorry... my bad. It was Peso that said he didn't think that float (one that I've included above) would work in 2k8.

    And, heh... guess I'm still angry at the fact that the DateTime2, Date, and Time won't convert.

    --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)

  • And, heh... guess I'm still angry at the fact that the DateTime2, Date, and Time won't convert.

    Why? Other than conversions I haven't found a good reason to convert a date to a float or int.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/12/2008)


    And, heh... guess I'm still angry at the fact that the DateTime2, Date, and Time won't convert.

    Why? Other than conversions I haven't found a good reason to convert a date to a float or int.

    Heh... and you won't because it's not possible. 🙂

    --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)

  • Other than conversions I haven't found a good reason to convert a datetime to a float or int.

    I'm sure you could figure out what I meant.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

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