Calculate and return the previous Date at 18:00 Hours

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  March 4, 2014 at 8:05 am

  #288858

  How can I calculate and return the previous Date at 18:00 Hours?

  Here is a miserable attempt:

  DECLARE @RunDate SmallDateTime

  DECLARE @CurrentDate SmallDateTime

  DECLARE @RunDateWoTime SmallDateTime

  SET @CurrentDate = GETDATE()

  SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate

  SELECT @RunDate AS RunDate

  -- Desired Result is the following:

  -- 2014-03-03 18:00

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 4, 2014 at 8:17 am

  #1693917

  SELECT DATEDIFF(dd,0,GETDATE())-1

  SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)

  SELECT DATEADD(hh,18,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0))

  Use q3. q1 and q2 help explain the algorithm.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• djj

  SSCoach

  Points: 18831

  More actions

  March 4, 2014 at 8:21 am

  #1693919

  Welsh Corgi (3/4/2014)

  ---

  How can I calculate and return the previous Date at 18:00 Hours?

  Here is a miserable attempt:

  DECLARE @RunDate SmallDateTime

  DECLARE @CurrentDate SmallDateTime

  DECLARE @RunDateWoTime SmallDateTime

  SET @CurrentDate = GETDATE()

  SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate

  SELECT @RunDate AS RunDate

  -- Desired Result is the following:

  -- 2014-03-03 18:00

  Could do something like:

  DECLARE @RunDate SmallDateTime

  DECLARE @CurrentDate DATE

  DECLARE @RunDateWoTime SmallDateTime

  SET @CurrentDate = GETDATE()

  SET @RunDate = DATEADD(day,-1,@CurrentDate) + ' 18:00' -- AS CurrentDate

  SELECT @RunDate AS RunDate

• KS

  Hall of Fame

  Points: 3323

  More actions

  March 4, 2014 at 8:28 am

  #1693923

  ChrisM@Work (3/4/2014)

  ---

  SELECT DATEDIFF(dd,0,GETDATE())-1

  SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)

  SELECT DATEADD(hh,18,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0))

  Use q3. q1 and q2 help explain the algorithm.

  Chris solution is obviously better than the below but if you do not want the seconds part then try below

  SELECT CONVERT(VARCHAR(10),GETDATE()-1,120) + ' 18:00' AS RunDate

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  March 4, 2014 at 2:42 pm

  #1694108

  Thank you very much everyone!

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Dwain Camps

  SSC Guru

  Points: 86913

  More actions

  March 4, 2014 at 6:17 pm

  #1694143

  ChrisM@Work (3/4/2014)

  ---

  SELECT DATEDIFF(dd,0,GETDATE())-1

  SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)

  SELECT DATEADD(hh,18,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0))

  Use q3. q1 and q2 help explain the algorithm.

  I can do it with just two date function calls:

  SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,'18:00')

  I can also name that tune in 3 notes. 😛

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  March 5, 2014 at 6:43 pm

  #1694532

  Thank you very much for all of the help.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 6, 2014 at 2:11 am

  #1694571

  dwain.c (3/4/2014)

  ---

  ChrisM@Work (3/4/2014)

  ---

  SELECT DATEDIFF(dd,0,GETDATE())-1

  SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)

  SELECT DATEADD(hh,18,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0))

  Use q3. q1 and q2 help explain the algorithm.

  I can do it with just two date function calls:

  SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,'18:00')

  I can also name that tune in 3 notes. 😛

  - and an implicit type conversion, which hides a note!

  DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,CAST('18:00' AS DATETIME))

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Dwain Camps

  SSC Guru

  Points: 86913

  More actions

  March 6, 2014 at 4:09 am

  #1694602

  ChrisM@Work (3/6/2014)

  ---

  dwain.c (3/4/2014)

  ---

  ChrisM@Work (3/4/2014)

  ---

  SELECT DATEDIFF(dd,0,GETDATE())-1

  SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)

  SELECT DATEADD(hh,18,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0))

  Use q3. q1 and q2 help explain the algorithm.

  I can do it with just two date function calls:

  SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,'18:00')

  I can also name that tune in 3 notes. 😛

  - and an implicit type conversion, which hides a note!

  DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,CAST('18:00' AS DATETIME))

  Where's the fun in that? 😛

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  March 6, 2014 at 9:25 am

  #1694780

  awesome:cool:

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/