I want to get one day before date from my table

• ashuthinks

  SSCommitted

  Points: 1698

  More actions

  December 30, 2013 at 3:12 am

  #280837

  Hi ,

  i have one table called `MYTABLE` and column called `MYDATE`

  now I want a previous date of `MYDATE`

  so did query like

  `SELECT dateadd(day,datediff(day,1,b.MYDATE),0), DeliveryDate from MYTABLE b`

  It is working perfect but not giving me time 🙁

  NEW MYDATE

  2013-06-04 00:00:00.0002013-06-05 18:30:00.000

  I want time also please correct me in above sql.

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  December 30, 2013 at 3:53 am

  #1676334

  SELECT DATEADD(day, -1, b.MYDATE), DeliveryDate from MYTABLE b

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

  "Ya can't make an omelette without breaking just a few eggs" 😉

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  December 30, 2013 at 5:44 am

  #1676358

  HI,

  I am posting a solution with some dummy data, hope it will help you

  DECLARE @table table (ID int identity(1,1), WorkDate datetime)

  INSERT INTO @table

  values

  ('2013-12-26 20:10:05.207'),('2013-12-27 18:10:05.207'),('2013-12-30 16:10:05.207')

  SELECT DATEADD(D,-1,WorkDate) FROM @table

  output:

  WorkDate

  2013-12-25 20:10:05.207

  2013-12-26 18:10:05.207

  2013-12-29 16:10:05.207

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/