Date with Time

Question

Post reply

Date with Time

bhaveshp.dba

SSC Eights!

Points: 834
More actions
December 20, 2011 at 9:38 am

#247961

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

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

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

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736162

December 20, 2011 at 9:55 am

#1423783

Use convert:

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

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 2

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)

Jared SSC Guru Points: 61793 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

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)

Jared SSC Guru Points: 61793 More actions · Answer 5

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

VIG SSC-Addicted Points: 488 More actions · Answer 6

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.

Jared SSC Guru Points: 61793 More actions · Answer 7

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