June 5, 2009 at 10:44 am
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
June 5, 2009 at 11:12 am
This one is simple:
declare @ThisDate datetime;
set @ThisDate = getdate();
select @ThisDate, dateadd(hh, datediff(hh, 0, @ThisDate), 0)
June 5, 2009 at 11:13 am
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
June 5, 2009 at 11:42 am
Thank you so much. It works perfectly. At times we tend to look for a complicated solution to a simple problem.
Thanks again,
Natasha
June 5, 2009 at 12:02 pm
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:
June 5, 2009 at 3:43 pm
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
June 9, 2009 at 8:03 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply