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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728164

  More actions

  December 20, 2011 at 9:55 am

  #1423783

  Use convert:

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

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com

• Lamprey13

  SSCertifiable

  Points: 6190

  More actions

  December 20, 2011 at 11:13 am

  #1423832

  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

  December 20, 2011 at 3:00 pm

  #1423937

  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: 1000539

  More actions

  December 20, 2011 at 8:15 pm

  #1423986

  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)
  Intro to Tally Tables and Functions

• Jared

  SSC Guru

  Points: 61793

  More actions

  December 20, 2011 at 8:33 pm

  #1423988

  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

  December 21, 2011 at 3:24 am

  #1424112

  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

  December 21, 2011 at 5:28 am

  #1424153

  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