August 5, 2009 at 5:29 am
I would like to read the Time part from below data.
1. 2009-06-05 00:00:00.000 2009-06-05 00:59:00.000
2. 2009-06-05 01:00:00.000 2009-06-05 07:00:00.000
3. 2009-06-05 17:00:00.000 2009-06-05 23:59:00.000
Expected result is
1. 12 12.59(this should b displayed as 1)
2. 1 7
3. 17 23.59(thi should b displyed as 24)
please help me.
Abhijit - http://abhijitmore.wordpress.com
August 5, 2009 at 6:04 am
Do you mean the output should be:
1. 0 0.59
2. 1 7
3. 17 23.59
If the first one is 12, then you would have midnight and noon as 12.
Abhijit More (8/5/2009)
I would like to read the Time part from below data.1. 2009-06-05 00:00:00.000 2009-06-05 00:59:00.000
2. 2009-06-05 01:00:00.000 2009-06-05 07:00:00.000
3. 2009-06-05 17:00:00.000 2009-06-05 23:59:00.000
Expected result is
1. 12 12.59(this should b displayed as 1)
2. 1 7
3. 17 23.59(thi should b displyed as 24)
please help me.
August 5, 2009 at 6:08 am
Assuming your date columns are consistent in size:
CREATE TABLE #t (dates nvarchar(50))
INSERT #t
SELECT '2009-06-05 00:00:00.000 2009-06-05 00:59:00.000'
UNION ALL SELECT '2009-06-05 01:00:00.000 2009-06-05 07:00:00.000'
UNION ALL SELECT '2009-06-05 17:00:00.000 2009-06-05 23:59:00.000'
SELECT
CAST(DATEPART(hh,LEFT(dates,23)) as varchar(2)) +
CASE WHEN DATEPART(mi, LEFT(dates,23)) 0 THEN '.' + CAST(DATEPART(mi, LEFT(dates,23)) as varchar(2)) ELSE '' END DATE1,
CAST(DATEPART(hh,RIGHT(dates,23)) as varchar(2)) +
CASE WHEN DATEPART(mi, RIGHT(dates,23)) 0 THEN '.' + CAST(DATEPART(mi, RIGHT(dates,23)) as varchar(2)) ELSE '' END DATE2
FROM #t
DROP TABLE #T
--fixed dumb mistake 🙂
August 5, 2009 at 6:16 am
hey thxs for th eprompt reply.
As per the database records I have to convert the record to know whether it is midnight / nooon.
And the expected results is
12/24 instead of 0. anf if it is 11.59 then this should be converted to 12 & if it is 23.59 the should be converted to 24
Abhijit - http://abhijitmore.wordpress.com
August 5, 2009 at 6:33 am
Abhijit More (8/5/2009)
hey thxs for th eprompt reply.As per the database records I have to convert the record to know whether it is midnight / nooon.
And the expected results is
12/24 instead of 0. anf if it is 11.59 then this should be converted to 12 & if it is 23.59 the should be converted to 24
So you want the minutes always rounded up? or just for the 59 minute?
What should these values be 23.29 23.30 23.31
August 5, 2009 at 9:03 am
Abhijit More (8/5/2009)
hey thxs for th eprompt reply.As per the database records I have to convert the record to know whether it is midnight / nooon.
And the expected results is
12/24 instead of 0. anf if it is 11.59 then this should be converted to 12 & if it is 23.59 the should be converted to 24
1. 2009-06-05 00:00:00.000 2009-06-05 00:59:00.000
Expected result is
1. 12 12.59(this should b displayed as 1)
If you are wanting to call midnight 24 instead of 0 then you don't want to convert 00:00:00 to 12 and 00:59:00 to 12:59. That is calling midnight noon. Instead you would want to convert it to 24 and 24:59 (or 1 for your final conversion). Otherwise from what you are saying you are basically calling anything that happens in the midnight hour as happening in the noon hour and anything that happens in the noon hour as happening in the noon hour which doesn't seem logically sound.
-Ben
August 5, 2009 at 9:18 am
Yea- that's what I thought he was looking for.
So changing the select part to this will round up the hour based on minutes >=30 and change 0 to 24.
SELECT
CASE
(DATEPART(hh,LEFT(dates,23))+
CASE WHEN DATEPART(mi, LEFT(dates,23)) < 30
THEN 0 ELSE 1 END)
WHEN 0 THEN 24
ELSE (DATEPART(hh,LEFT(dates,23))+
CASE WHEN DATEPART(mi, LEFT(dates,23)) < 30
THEN 0 ELSE 1 END) END [Date1],
CASE
(DATEPART(hh,RIGHT(dates,23))+
CASE WHEN DATEPART(mi, RIGHT(dates,23)) < 30
THEN 0 ELSE 1 END)
WHEN 0 THEN 24
ELSE (DATEPART(hh,RIGHT(dates,23))+
CASE WHEN DATEPART(mi, RIGHT(dates,23)) < 30
THEN 0 ELSE 1 END) END [Date2]
FROM #t
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply