Date Function

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  July 8, 2008 at 12:31 pm

  #193531

  Guys,

  I have timestamp fields like '1994-08-01 00:13:00.000' from which I would like to populate only date and strip of any hh,mm,ss - for the output to be '1994-08-01 00:00:00.000'.

  Is there any way to do this.

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 8, 2008 at 12:38 pm

  #839728

  Try this.

  DECLARE @TheDate DATETIME

  SET @TheDate = '1994-08-01 00:13:00.000'

  select dateadd(dd, datediff(dd,0, @TheDate),0)

  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

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  July 8, 2008 at 12:58 pm

  #839738

  Thanks that works

• Atif-ullah Sheikh

  SSChampion

  Points: 12495

  More actions

  July 9, 2008 at 5:34 am

  #839996

  DECLARE @MyDate datetime

  SET @MyDate = '1994-08-01 00:13:00.000'

  -- Converting date to varchar gives you option to convert in the

  --required format and then convert back to the datetime for future

  --referance as datetime. format 101 is used in this case.

  Select convert(datetime,convert(varchar(10),@MyDate,101))

  Hope this will help you.

  Atif Sheikh

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------

  Sometimes, winning is not an issue but trying.

  You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  July 9, 2008 at 5:50 am

  #840007

  I prefer the method that Gail listed... it's all "math" and that makes if faster than character conversions. It also keeps it as a DateTime datatype so that other calculations may be done using the data without any conversions. I also avoid formatting things in SQL Server... that's the GUI's job, if there is one, so that local formatting and other settings may prevail. If you format the data at the SQL Server level, you may actually have to undo the format and reformat for a particular country's needs. 🙂

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

  SSC Guru

  Points: 1004329

  More actions

  July 9, 2008 at 5:53 am

  #840011

  Oh yeah... almost forgot... there is one faster method... convert to Float, distribute Floor, convert back to DateTime... but most people don't use that because it only brings a little more speed and it's a bit longer to write... including me. 😉

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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 9, 2008 at 6:26 am

  #840035

  Jeff Moden (7/9/2008)

  ---

  Oh yeah... almost forgot... there is one faster method... convert to Float, distribute Floor, convert back to DateTime... but most people don't use that because it only brings a little more speed and it's a bit longer to write... including me. 😉

  I normally use the Float/floor version in my own code, but I teach teh dateadd/datediff, because it's extendable to other beginnings (or week, of month, of quarter)

  Also there's no guarentee that in future versions date will cast to float and back properly.

  Hmmm, I need to test it out with DateTime2

  Atif: The conversion to varchar is slower. It isn't noticable on a single value. On a couple million rows, it is.

  CREATE TABLE #DateTest (

  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

  DateValue DATETIME

  )

  INSERT INTO #DateTest (DateValue)

  SELECT TOP 1000000

  DATEADD(mi,RAND(s1.number*5000 + s2.number)*5000,'2000/01/01' )

  FROM master..spt_values s1 CROSS JOIN master..spt_values s2

  WHERE s1.[name] IS NULL AND s2.NAME IS null

  GO

  SET STATISTICS TIME ON

  GO

  -- CPU time = 781 ms, elapsed time = 13696 ms

  SELECT dateadd(dd, datediff(dd,0, DateValue),0)

  FROM #DateTest

  GO

  -- CPU time = 3015 ms, elapsed time = 15001 ms

  SELECT convert(datetime,convert(varchar(10),DateValue,101))

  FROM #DateTest

  GO

  DROP TABLE #DateTest

  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

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  July 9, 2008 at 7:05 am

  #840081

  Nicely done, Gail... It shows that the Convert method takes about 4 times longer than the DateAdd/DateDiff method... While someone may not be working on a million rows, anytime you can increase the performance of simple functionality by a factor of 4, you know it's gonna help on large batch runs and GUI code under heavy load... and it's a very simple thing to do, to boot! 😀

  Heh... no wonder you're an MVP. 😉

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

• Andy sql

  SSCrazy Eights

  Points: 9496

  More actions

  July 10, 2008 at 9:30 am

  #840925

  Heh... no wonder you're an MVP.

  I think the saying goes, it takes one to know one....

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 10, 2008 at 9:37 am

  #840930

  Jeff Moden (7/9/2008)

  ---

  Nicely done, Gail... It shows that the Convert method takes about 4 times longer than the DateAdd/DateDiff method...

  If I hadn't done a test, you probably would have.

  And just for completeness, seeing as I'll probably blog on this sometime... This was run with the same test data as above.

  -- CPU time = 922 ms, elapsed time = 13332 ms.

  SELECT CAST(FLOOR(CAST(DateValue AS FLOAT)) AS DATETIME)

  FROM #DateTest

  GO

  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

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  July 10, 2008 at 7:58 pm

  #841198

  AndyD (7/10/2008)

  ---

  Heh... no wonder you're an MVP.

  I think the saying goes, it takes one to know one....

  Thanks Andy...

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

  SSC Guru

  Points: 1004329

  More actions

  July 10, 2008 at 8:00 pm

  #841199

  GilaMonster (7/10/2008)

  ---

  Jeff Moden (7/9/2008)

  ---

  Nicely done, Gail... It shows that the Convert method takes about 4 times longer than the DateAdd/DateDiff method...

  If I hadn't done a test, you probably would have.

  And just for completeness, seeing as I'll probably blog on this sometime... This was run with the same test data as above.

  -- CPU time = 922 ms, elapsed time = 13332 ms.

  SELECT CAST(FLOOR(CAST(DateValue AS FLOAT)) AS DATETIME)

  FROM #DateTest

  GO

  Nothing like being complete... thanks Gail. The problem is, now that I look at that, that's not quite the forumula I remember... I'll see if I can find the one I'm thinking about...

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

  SSC Guru

  Points: 1004329

  More actions

  July 10, 2008 at 8:28 pm

  #841208

  Aw crud... I've lost my mind... the method I was talking about doesn't have a thing to do with Float conversions... here's an (very) old set of tests I did. Note the code for "Rounding method 2"... THAT's what I was talking about...

  --===== Create a test table

  SELECT TOP 1000000

  IDENTITY(INT,1,1) AS RowNum,

  CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME) AS ADate

  INTO dbo.#JbmTest

  FROM Master.dbo.SysColumns sc1,

  Master.dbo.SysColumns sc2

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

  ALTER TABLE dbo.#JbmTest

  ADD PRIMARY KEY CLUSTERED (RowNum)

  --===== Declare a couple of operating variables for the test

  DECLARE @MyDate DATETIME --Holds the result of a conversion to bypass display times

  DECLARE @StartTime DATETIME --For measuring duration of each snippet

  PRINT '===== Rounding method 1 ======'

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  SET @StartTime = GETDATE()

  SELECT @MyDate=CAST(ROUND(CAST(ADate AS FLOAT),0,1) AS DATETIME) FROM dbo.#JbmTest

  PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

  PRINT ' '

  PRINT '===== Rounding method 2 ======'

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  SET @StartTime = GETDATE()

  SELECT @MyDate=CAST(CAST((ADate - 0.5 ) AS INTEGER) AS DATETIME) FROM dbo.#JbmTest

  PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

  PRINT ' '

  PRINT '===== DateDiff/DateAdd method ======'

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  SET @StartTime = GETDATE()

  SELECT @MyDate=DATEADD(dd,DATEDIFF(dd,0,ADate),0) FROM dbo.#JbmTest

  PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

  PRINT ' '

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

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  SET @StartTime = GETDATE()

  SELECT @MyDate=CAST(CONVERT(CHAR(8),ADate,112) AS DATETIME) FROM dbo.#JbmTest

  PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

  PRINT ' '

  PRINT '===== Johnathons''s Integer function (has flaw!) ====='

  -- Does NOT truncate... does a round be careful!!!!

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  SET @StartTime = GETDATE()

  SELECT @MyDate=CAST(CONVERT(int, CONVERT(FLOAT, Adate)) AS DATETIME) FROM dbo.#JbmTest

  PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

  PRINT ' '

  PRINT '===== Floor method ====='

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  SET @StartTime = GETDATE()

  SELECT @MyDate=CAST(FLOOR(CONVERT(FLOAT, Adate)) AS DATETIME) FROM dbo.#JbmTest

  PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

  PRINT ' '

  PRINT '===== Minus method (int) ====='

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  SET @StartTime = GETDATE()

  SELECT @MyDate=CAST(Adate - 0.50000004 as INT) FROM dbo.#JbmTest

  PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

  PRINT ' '

  --DROP TABLE #JbmTest

  ... sorry for the confusion...

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