Datetime function help

• ssismaddy

  SSCarpal Tunnel

  Points: 4850

  More actions

  May 20, 2013 at 2:22 pm

  #394812

  Hi, I m trying to get a datetime function for the start day of current week (Sunday). I m using the below function

  select DATEADD(wk,DATEDIFF(wk,0,GETDATE()),-1)

  However, i need to start from 7/5/4 AM every sunday depends on the value of the variable supplied.

  like :

  declare @minute int

  set @minute = -420

  select DATEADD(wk,DATEDIFF(wk,0, dateadd(mi,-1*@minute,GETDATE())),-1)

  I m expecting it to return : 2013-05-19 07:00:00.000

  but it returns : 2013-05-19 00:00:00.000

  Can someone help me on this?

  THank you

• Sean Lange

  SSC Guru

  Points: 286601

  More actions

  May 20, 2013 at 2:29 pm

  #1616785

  Take a look at this article.

  http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 20, 2013 at 2:42 pm

  #1616787

  Have a look at this:

  declare @ThisDate datetime = getdate();

  select @ThisDate, dateadd(hour, 7, dateadd(week, datediff(week,0,@ThisDate), -1));

  set @ThisDate = '20130519';

  select @ThisDate, dateadd(hour, 7, dateadd(week, datediff(week,0,@ThisDate), -1));

  set @ThisDate = '20130516';

  select @ThisDate, dateadd(hour, 7, dateadd(week, datediff(week,0,@ThisDate), -1));

• ssismaddy

  SSCarpal Tunnel

  Points: 4850

  More actions

  May 20, 2013 at 3:03 pm

  #1616799

  Great, It works! Thanks a lot

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  May 22, 2013 at 12:09 am

  #1617269

  ssismaddy (5/20/2013)

  ---

  However, i need to start from 7/5/4 AM every sunday depends on the value of the variable supplied.

  If you run the query on 2013-05-19 06:00:00.000 - what output would you expect?

  2013-05-19 07:00:00.000

  or

  2013-05-12 07:00:00.000

  ?

  _____________
  Code for TallyGenerator