Datetime

• Natasha-671644

  Mr or Mrs. 500

  Points: 533

  More actions

  June 5, 2009 at 10:44 am

  #73216

  I am trying to get rid of minutes, seconds and nanoseconds by zeroing them so I can group dates on an hourly basis from datefield. It seem like I am missing something in this code and can't figure it out. Guess having a brain freeze:(

  Here is a piece of code:

  Declare

  @Testdate datetime

  select @Testdate = '2009-03-13 16:09:16.100'

  select convert(datetime,convert(varchar,substring(convert(varchar(20),@Testdate),1,charindex(':',@Testdate)-1 )+':00:00.000' )) as DateHour

  Technically this should result into :

  2009-03-13 16:00:00.000

  However it is resulting into :

  2009-03-13 04:00:00.000

  any ideas??

  TIA,

  Natasha

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 5, 2009 at 11:12 am

  #1005443

  This one is simple:

  declare @ThisDate datetime;

  set @ThisDate = getdate();

  select @ThisDate, dateadd(hh, datediff(hh, 0, @ThisDate), 0)

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 5, 2009 at 11:13 am

  #1005446

  declare @test-2 datetime;

  --

  select @test-2 = '6/5/09 1:12 pm';

  --

  select dateadd(hour, datediff(hour, 0, @test-2), 0);

  You can use that dateadd(datediff()) method for taking any part of a datetime off. Works very well, and is quite efficient and fast.

  Edit:

  Lynn: Jinx!

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Natasha-671644

  Mr or Mrs. 500

  Points: 533

  More actions

  June 5, 2009 at 11:42 am

  #1005462

  Thank you so much. It works perfectly. At times we tend to look for a complicated solution to a simple problem.

  Thanks again,

  Natasha

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 5, 2009 at 12:02 pm

  #1005476

  GSquared (6/5/2009)

  ---

  declare @test-2 datetime;

  --

  select @test-2 = '6/5/09 1:12 pm';

  --

  select dateadd(hour, datediff(hour, 0, @test-2), 0);

  You can use that dateadd(datediff()) method for taking any part of a datetime off. Works very well, and is quite efficient and fast.

  Edit:

  Lynn: Jinx!

  Yea, Right! You know I beat you to it, even if only by a minute! :w00t:

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  June 5, 2009 at 3:43 pm

  #1005570

  GSquared (6/5/2009)

  ---

  You can use that dateadd(datediff()) method for taking any part of a datetime off. Works very well, and is quite efficient and fast.

  Not any part.

  It will fail for ms. There are too many milliseconds since "zero date", more than INT can handle.

  For milliseconds you need to use some "closer" date, say, an day within the current year.

  _____________
  Code for TallyGenerator

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 9, 2009 at 8:03 am

  #1006701

  Sergiy (6/5/2009)

  ---

  GSquared (6/5/2009)

  ---

  You can use that dateadd(datediff()) method for taking any part of a datetime off. Works very well, and is quite efficient and fast.

  Not any part.

  It will fail for ms. There are too many milliseconds since "zero date", more than INT can handle.

  For milliseconds you need to use some "closer" date, say, an day within the current year.

  You caught me! I hadn't tried it on that. For that, I'd do a nested calculation to the beginning of the day, then use that in place of the zeros.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon