Interval division for Datetime column function or Query

Question

Hi below is a sample table

create table #x( dt datetime,Interval varchar(10) )
insert into #x(dt) values(' 2012-05-23 14:20:03.000')--interval 1400
insert into #x(dt) values('2012-05-23 14:30:01.000')--1430
insert into #x(dt) values('2012-05-23 10:00:00.000')--1000
insert into #x(dt) values('2012-05-23 20:00:00.000')--2000
insert into #x(dt) values('2012-05-23 21:30:01.000')--2130
insert into #x(dt) values('2012-05-23 23:59:59.000')--2330
insert into #x(dt) values('2012-05-23 00:00:00.000')--0000

here i would like to divide the dt(date time) column to halfhour interval which it belongs to.

can some one suggest me a function or query which can divide this into varchar interval field.

Answer 1

Hey Reddy ,

Is it something like this u r looking for

Select *,Convert(Varchar(2),DatePart(Hour,dt))+ Convert(Varchar(2),Case When DatePart(Minute,dt)< 30 Then '00' Else '30' End) From #X

---

Please have look on the comment

Answer 2

I am not sure what your expected op is

 SELECT *,CASE WHEN DATEPART(MINUTE,dt) BETWEEN 1 AND 29 THEN CAST(DATEPART(hour,DATEADD(MINUTE,DATEPART(MINUTE,dt)*-1,dt)) AS varchar)+CAST(DATEPART(minute,DATEADD(MINUTE,DATEPART(MINUTE,dt)*-1,dt)) AS varchar)
			   WHEN  DATEPART(MINUTE,dt) BETWEEN 31 AND 59 THEN  CAST(DATEPART(HOUR, DATEADD(MINUTE,30-DATEPART(MINUTE,dt),dt))AS VARCHAR)+CAST(DATEPART(minute, DATEADD(MINUTE,30-DATEPART(MINUTE,dt),dt))AS VARCHAR) 
			   ELSE CAST( DATEPART(HOUR,dt) AS varchar)+CAST( DATEPART(minute,dt) AS varchar)
			   END   FROM	#x

or

SELECT *,REPLACE(CONVERT(VARCHAR(6) , CASE WHEN DATEPART(MINUTE,dt) <30 THEN DATEADD(HOUR, DATEDIFF(HOUR,0,dt),0)	
			WHEN DATEPART(MINUTE,dt) >30 then	DATEADD(MINUTE,30,  DATEADD(HOUR, DATEDIFF(HOUR,0,dt),0))
		  	ELSE dt
		  END,114),':' ,'')
		FROM #x

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

Try this

SELECT CONVERT(VARCHAR(2),dt,108) + Substring(CONVERT(VARCHAR(5),dt,108),4,5) from #X

---

Hope it Helps!!

Answer 4

here i would like to divide the dt(date time) column to halfhour interval which it belongs to.

can some one suggest me a function or query which can divide this into varchar interval field.

Assuming SQL Server 2008 and above:

UPDATE #x
SET Interval = REPLACE(CAST(CAST(DATEADD(minute, DATEDIFF(minute, '', dt)/30*30, '') AS time) AS char(5)), ':', '');

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 5

SELECT RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(HOUR,dt)),2) + CASE WHEN DATEPART(MINUTE,dt) < 30 THEN '00' ELSE '30' END
FROM #x