February 22, 2010 at 9:54 pm
DECLARE @date1 DATETIME,@date2 DATETIME,@date3 DATETIME;
SET @date1=CONVERT(varchar,GETDATE()+1,108)
SET @date2=CONVERT(varchar,GETDATE()+2,108)
SET @date3=CONVERT(varchar,GETDATE()+3,108)
SELECT * INTO #temp
FROM(
SELECT @date1 AS date
UNION
SELECT @date2 AS date
UNION
SELECT @date3 AS date) AS p
Can any one send how to calculate the average of datetime in sql server 2005 and avg function is not working and i have no permission to create cursor or functions in a database i only able to create temp tables.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
February 23, 2010 at 2:07 am
Convert the date column to FLOAT, then back to DATETIME:
DECLARE @test-2 TABLE (
dtColumn datetime
)
INSERT INTO @test-2 VALUES (GETDATE() + RAND())
INSERT INTO @test-2 VALUES (GETDATE() + RAND())
INSERT INTO @test-2 VALUES (GETDATE() + RAND())
INSERT INTO @test-2 VALUES (GETDATE() + RAND())
SELECT CAST(AVG(CAST(dtColumn AS FLOAT)) AS datetime)
FROM @test-2
Hope this helps
Gianluca
-- Gianluca Sartori
February 23, 2010 at 5:15 am
Hi thanks for reply,
this above code is fine but it will take the average time not full datetime.
I want to calculate time not average of date.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
February 23, 2010 at 6:11 am
Just the time part? OK, take the fractional part only:
DECLARE @test-2 TABLE (
dtColumn datetime
)
INSERT INTO @test-2 VALUES (GETDATE() + RAND())
INSERT INTO @test-2 VALUES (GETDATE() + RAND())
INSERT INTO @test-2 VALUES (GETDATE() + RAND())
INSERT INTO @test-2 VALUES (GETDATE() + RAND())
SELECT CONVERT(char(8),CAST(AVG(CAST(dtColumn AS FLOAT) - FLOOR(CAST(dtColumn AS FLOAT))) AS datetime),108)
FROM @test-2
-- Gianluca Sartori
February 23, 2010 at 6:44 am
ok.Its working fine
Malleswarareddy
I.T.Analyst
MCITP(70-451)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply