Date with Time

  • Hello all ,

    I am updating table with getdate() and I get a result set like

    2011-12-20 10:21:03.677

    but here I want a Time like

    2011-12-20 00:00:00.000

    How I can change the time means how can I update the table?

    Thanks

    Bhavesh

  • Use convert:

    SELECT CONVERT( Varchar(12), GetDate(),1)

  • I wouldn't explicitly covert a date to a string and then implicitly back to a date. Depending on your version of SQL you can use:SELECT

    CAST(CURRENT_TIMESTAMP AS DATE),

    DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)

  • Faster:

    SELECT CAST(DATEDIFF(d,0,GETDATE()) AS DATETIME)

    Can't find the article offhand that proves this, but I will continue to look. Could it be one of Jeff's?

    Jared
    CE - Microsoft

  • SQLKnowItAll (12/20/2011)


    Faster:

    SELECT CAST(DATEDIFF(d,0,GETDATE()) AS DATETIME)

    Can't find the article offhand that proves this, but I will continue to look. Could it be one of Jeff's?

    I can't take the credit for it. I've used that method in quite a few posts and so have a lot of other folks. I did, however, write a test for it and posted it a couple of times.

    Here's the test code...

    --===== Setup a million row test table in a nice safe place that everyone has

    USE TempDB;

    GO

    SELECT TOP 1000000

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

    INTO dbo.JBMTest

    FROM sys.all_columns t1,

    sys.all_columns t2; --Lack of join criteria makes this a CROSS-JOIN

    DECLARE @Bitbucket DATETIME; --Takes display time out of the picture

    PRINT '--===== DATEADD/DATEDIFF =============================================';

    SET STATISTICS TIME ON;

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

    FROM dbo.JBMTest

    OPTION (MAXDOP 1); --Takes parallelism out of the picture

    SET STATISTICS TIME OFF;

    PRINT '--===== DOUBLE CONVERT ===============================================';

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = CONVERT(DATETIME, CONVERT(VARCHAR, SomeDate, 103), 103)

    FROM dbo.JBMTest

    OPTION (MAXDOP 1); --Takes parallelism out of the picture

    SET STATISTICS TIME OFF;

    PRINT '--===== CONVERT/DATEDIFF =============================================';

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = CONVERT(DATETIME, DATEDIFF(dd, 0, SomeDate))

    FROM dbo.JBMTest

    OPTION (MAXDOP 1); --Takes parallelism out of the picture

    SET STATISTICS TIME OFF;

    PRINT '--===== CAST/DATEDIFF ================================================';

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = CAST(DATEDIFF(dd, 0, SomeDate) AS DATETIME)

    FROM dbo.JBMTest

    OPTION (MAXDOP 1); --Takes parallelism out of the picture

    SET STATISTICS TIME OFF;

    --===== Housekeeping

    DROP TABLE dbo.JBMTest;

    GO

    Here're the results on my humble 9 year old, single cpu desktop box (the last 2 take turns winning)...

    (1000000 row(s) affected)

    --===== DATEADD/DATEDIFF =============================================

    SQL Server Execution Times:

    CPU time = 828 ms, elapsed time = 821 ms.

    --===== DOUBLE CONVERT ===============================================

    SQL Server Execution Times:

    CPU time = 2500 ms, elapsed time = 2516 ms.

    --===== CONVERT/DATEDIFF =============================================

    SQL Server Execution Times:

    CPU time = 687 ms, elapsed time = 689 ms.

    --===== CAST/DATEDIFF ================================================

    SQL Server Execution Times:

    CPU time = 688 ms, elapsed time = 680 ms.

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

  • Well then, it was not an article, but your test. Thanks for posting again! Would you mind if I mentioned this test in my blog and cited you for it? I think it could help many people, not just with this issue, but show them how to run their own tests for similar things.

    Jared
    CE - Microsoft

  • select cast(CAST(getdate() as DATE) as datetime)

    results of running Jeff Moden's script on my 2008R2 with additional cast/cast convertion

    (1000000 row(s) affected)

    --===== DATEADD/DATEDIFF =============================================

    SQL Server Execution Times:

    CPU time = 234 ms, elapsed time = 239 ms.

    --===== DOUBLE CONVERT ===============================================

    SQL Server Execution Times:

    CPU time = 811 ms, elapsed time = 820 ms.

    --===== CONVERT/DATEDIFF =============================================

    SQL Server Execution Times:

    CPU time = 219 ms, elapsed time = 220 ms.

    --===== CAST/DATEDIFF ================================================

    SQL Server Execution Times:

    CPU time = 218 ms, elapsed time = 218 ms.

    --===== CAST/cast ================================================

    SQL Server Execution Times:

    CPU time = 203 ms, elapsed time = 205 ms.

  • VIG (12/21/2011)


    select cast(CAST(getdate() as DATE) as datetime)

    results of running Jeff Moden's script on my 2008R2 with additional cast/cast convertion

    (1000000 row(s) affected)

    --===== DATEADD/DATEDIFF =============================================

    SQL Server Execution Times:

    CPU time = 234 ms, elapsed time = 239 ms.

    --===== DOUBLE CONVERT ===============================================

    SQL Server Execution Times:

    CPU time = 811 ms, elapsed time = 820 ms.

    --===== CONVERT/DATEDIFF =============================================

    SQL Server Execution Times:

    CPU time = 219 ms, elapsed time = 220 ms.

    --===== CAST/DATEDIFF ================================================

    SQL Server Execution Times:

    CPU time = 218 ms, elapsed time = 218 ms.

    --===== CAST/cast ================================================

    SQL Server Execution Times:

    CPU time = 203 ms, elapsed time = 205 ms.

    Interesting! I never thought of using the new DATE datatype for this. Brilliant!

    Jared
    CE - Microsoft

Viewing 8 posts - 1 through 7 (of 7 total)

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