SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Time in Analog
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 03/14/2004 :  10:18:35  Show Profile  Visit ditch's Homepage  Reply with Quote
I was bored, so I tried it.

It's not perfect (The results panes fonts mess with it a bit).

Best viewed if your QA's font is set to one of the Courier fonts and the results are viewed as text rather than grid.

I still want to get it right without using any cursors at all.
I will attempt this later.
Maybe someone else can improve on this though.

Here it is:

--**********************************************************

CREATE PROCEDURE AnalogTime AS

SET NOCOUNT ON
CREATE TABLE #ClockTimeDeg(Radius INT, ClockTimeHRS DECIMAL(18, 3), ClockTimeMin INT, Deg DECIMAL(18, 8), XCoOrd DECIMAL(18, 8), YCoOrd DECIMAL(18, 8), DISPLAY VARCHAR(2));

DECLARE @Mins INT
DECLARE @Hrs DECIMAL(18, 3)
DECLARE @Deg DECIMAL(18,8)
DECLARE @XCoOrd INT
DECLARE @YCoOrd INT
DECLARE @CurXCoOrd INT
DECLARE @CurYCoOrd INT
DECLARE @PrevYCoOrd INT
DECLARE @Radius DECIMAL(18, 8)
DECLARE @InsString VARCHAR(1000)
DECLARE @Display VARCHAR(2)
DECLARE @CurrKey INT
DECLARE @PrevKey INT
DECLARE @CurrTime DATETIME
DECLARE @HrsVal DECIMAL(18, 8)
DECLARE @MinsVal INT

SET @CurrTime = GETDATE()
SET @MinsVal = DATEPART(MINUTE, @CurrTime)
SET @HrsVal = CAST(DATEPART(HOUR, @CurrTime) AS DECIMAL(18, 8)) + (CAST((@MinsVal / 12) AS DECIMAL(18, 2)) * 0.2)

IF @HrsVal >= 13
BEGIN
SET @HrsVal = @HrsVal - 12
END

SET @Radius = 1
WHILE @Radius <= 24
BEGIN
--******Store the x, y co-ords of the clocks hrs and minutes using trig functions
--******Clock Radius = 24
--****** The length of the Y, coords is halved because it seems like the results panel provides more height to each character than width.
SET @Deg = 90
SET @Hrs = 12
SET @Mins = 0
WHILE @Mins < 60
BEGIN
INSERT INTO #ClockTimeDeg
SELECT @Radius, @Hrs, @Mins, @Deg,
CAST(24 + (@Radius * (CASE WHEN TAN(RADIANS(@Deg)) = 0 THEN -24 ELSE ((SIN(RADIANS(@Deg))) / TAN(RADIANS(@Deg))) END)) + 0.499999999 AS INT),
(48 - CAST(24 + @Radius * (SIN(RADIANS(@Deg))) + 0.499999999 AS INT) / 2),
CASE WHEN (@HrsVal = @Hrs AND @Radius < 11) OR (@MinsVal = @Mins AND @Radius < 21) THEN '*'
ELSE CASE WHEN @Radius = 23 AND @Hrs = CAST(@Hrs as INT) THEN LTRIM(STR(@HRS, 2)) ELSE ' ' END END
SET @Hrs = @Hrs + 0.2
IF @Hrs = 13
BEGIN
SET @HRS = 1
END
SET @Mins = @Mins + 1
IF @Deg > 353
BEGIN
SET @Deg = 0
END
ELSE
BEGIN
SET @Deg = @Deg + 6
END
END
SET @Radius = @Radius + 1
END

DECLARE DispCur
CURSOR FOR
SELECT CASE WHEN XCoOrd <= 0 THEN 48 ELSE XCoOrd END, YCoOrd, Display
FROM #ClockTimeDeg
WHERE Display<> ' '
ORDER BY 2, 1 DESC


CREATE TABLE #Results(RowNo INT, AnalogClock VARCHAR(1000))

OPEN DispCur
FETCH NEXT FROM DispCur INTO @CurXCoOrd, @CurYCoOrd, @Display
SET @PrevYCoOrd = 0
SET @PrevKey = 0
SET @XCoOrd = 1
SET @InsString = ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CurYCoOrd <> @PrevYCoOrd
BEGIN
INSERT INTO #Results
SELECT @CurYCoOrd, @InsString
SET @InsString = ''
SET @PrevYCoOrd = @CurYCoOrd
SET @XCoOrd = 50
END
WHILE @XCoOrd > @CurXCoOrd
BEGIN
SET @InsString = @InsString + ' '
SET @XCoOrd = @XCoOrd - 1
END
SET @InsString = @InsString + @Display
SET @XCoOrd = @XCoOrd - 1
FETCH NEXT FROM DispCur INTO @CurXCoOrd, @CurYCoOrd, @Display
END

CLOSE DispCur
DEALLOCATE DispCur

INSERT INTO #Results
SELECT @CurYCoOrd, @InsString

SELECT AnalogClock
FROM #Results
ORDER BY RowNo

DROP TABLE #ClockTimeDeg
DROP TABLE #Results

GO


exec analogtime
--**************************************************

Any suggestions and improvements welcome.


Duane.

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 03/14/2004 :  10:33:40  Show Profile  Visit Merkin's Homepage  Reply with Quote
ROFLMAO

That's classic.
Good one



Damian
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 03/14/2004 :  12:54:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
BRILLIANT!!!
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 03/15/2004 :  05:26:58  Show Profile  Visit ditch's Homepage  Reply with Quote
quote:
Originally posted by Merkin

ROFLMAO




Thanks Guys.

What does ROFLMAO Mean?


Duane.
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/15/2004 :  06:14:19  Show Profile  Visit Frank Kalis's Homepage  Reply with Quote
http://www.acronymfinder.com/af-query.asp?String=exact&Acronym=ROFLMAO&Find=Find

--Frank
http://www.insidesql.de
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 03/15/2004 :  06:20:17  Show Profile  Visit ditch's Homepage  Reply with Quote
Thanks Frank


Duane.
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/15/2004 :  06:23:34  Show Profile  Visit Frank Kalis's Homepage  Reply with Quote
No problem!
What about offering this to Microsoft as future enhancement by putting it on the wish list?




--Frank
http://www.insidesql.de
Go to Top of Page

harshal_in
Aged Yak Warrior

India
633 Posts

Posted - 03/15/2004 :  06:26:41  Show Profile  Visit harshal_in's Homepage  Send harshal_in a Yahoo! Message  Reply with Quote
Simply GREAT!!

He is a fool for five minutes who asks , but who does not ask remains a fool for life!
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

United Arab Emirates
1456 Posts

Posted - 03/15/2004 :  06:55:52  Show Profile  Visit mohdowais's Homepage  Reply with Quote
quote:
Originally posted by Frank Kalis

No problem!
What about offering this to Microsoft as future enhancement by putting it on the wish list?




--Frank
http://www.insidesql.de



huh? The world's already dividing over the inclusion of an analog clock in Longhorn
http://journals.tuxreports.com/lch/archives/000991.html
http://weblogs.asp.net/oldnewthing/archive/2003/10/30/55498.aspx

OS
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 03/15/2004 :  07:04:08  Show Profile  Visit Frank Kalis's Homepage  Reply with Quote
Yes, and I heard that M$ once again teamed up with leading hardware vendors to finally supply a secure PC. This is a top secret document from the test labs in Denmark
http://www.uoe.dk/csworld/security-.html

--Frank
http://www.insidesql.de
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 03/15/2004 :  16:46:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://weblogs.sqlteam.com/tarad/archive/2004/03/15/1060.aspx

Tara
Go to Top of Page

SamC
White Water Yakist

USA
3460 Posts

Posted - 03/15/2004 :  22:25:31  Show Profile  Reply with Quote
The frontiers of SQL are best explored by those with time on their hands.

(What I mean is... I'm not sure myself. Go figure.)
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 03/15/2004 :  23:48:10  Show Profile  Visit ditch's Homepage  Reply with Quote
quote:
Originally posted by tduggan

http://weblogs.sqlteam.com/tarad/archive/2004/03/15/1060.aspx




Thanks for that Tara.

Duane.
Go to Top of Page

anolis
Starting Member

Norway
14 Posts

Posted - 05/14/2004 :  14:09:08  Show Profile  Reply with Quote
Great!!

I've got a function that stores the current time in a table. When you create a job, it can do that every second. Anyone interested?

Anolis,
Developer,
MCAD for .NET
Go to Top of Page

Ratboy
Starting Member

2 Posts

Posted - 06/08/2004 :  08:34:01  Show Profile  Reply with Quote
I just came across your post and was amazed to see that someone else out that was as bored as I was to create an analogue clock with the current time! If you're interested, here was my attempt:


/** Init variables **/
DECLARE @curX decimal(20, 10)
DECLARE @curY decimal(20, 10)
DECLARE @strLine varchar(100)
DECLARE @intDistance int
DECLARE @curA decimal(20, 10)
DECLARE @curB decimal(20, 10)
DECLARE @curC decimal(20, 10)
DECLARE @curHourHandLen decimal(9, 4)
DECLARE @curHourHandX1 decimal(20, 10)
DECLARE @curHourHandY1 decimal(20, 10)
DECLARE @curHourHandX2 decimal(20, 10)
DECLARE @curHourHandY2 decimal(20, 10)
DECLARE @curMinuteHandLen decimal(9, 4)
DECLARE @curMinuteHandX1 decimal(20, 10)
DECLARE @curMinuteHandY1 decimal(20, 10)
DECLARE @curMinuteHandX2 decimal(20, 10)
DECLARE @curMinuteHandY2 decimal(20, 10)
DECLARE @curAngle numeric(11, 10)
DECLARE @intQuarter smallint
DECLARE @curHour decimal(20, 10)
DECLARE @curDistanceFromCenter decimal(20, 10)
DECLARE @curCenterX decimal(20, 10)
DECLARE @curCenterY decimal(20, 10)

/** Setup variables **/
SET @curHourHandLen = 20
SET @curMinuteHandLen = 42
SET @curCenterX = 50
SET @curCenterY = 50

/** Work out the hand positions **/
-- They all start from the center
SET @curHourHandX1 = @curCenterX
SET @curHourHandY1 = @curCenterY
SET @curMinuteHandX1 = @curCenterX
SET @curMinuteHandY1 = @curCenterY

-- Work out how far round the clock the hour is and set the position from that
SET @curHour = DATEPART(hour, GETDATE())
IF @curHour > 12
SET @curHour = @curHour - 12
SET @curHour = @curHour + (CONVERT(numeric(11, 9), DATEPART(minute, GETDATE())) / 60)
SET @curAngle = (PI() * 2) * (CONVERT(numeric(11, 9), @curHour) / 12)

IF @curAngle >= 0 AND @curAngle <= PI() / 2
BEGIN
SET @intQuarter = 1
SET @curHourHandX2 = @curHourHandX1 + (@curHourHandLen * SIN(@curAngle))
SET @curHourHandY2 = @curHourHandY1 - (@curHourHandLen * COS(@curAngle))
END
ELSE IF @curAngle > PI() / 2 AND @curAngle <= PI()
BEGIN
SET @intQuarter = 2
SET @curAngle = PI() - @curAngle
SET @curHourHandX2 = @curHourHandX1 + (@curHourHandLen * SIN(@curAngle))
SET @curHourHandY2 = @curHourHandY1 + (@curHourHandLen * COS(@curAngle))
END
ELSE IF @curAngle > PI() AND @curAngle <= (PI() * 1.5)
BEGIN
SET @intQuarter = 3
SET @curAngle = @curAngle - PI()
SET @curHourHandX2 = @curHourHandX1 - (@curHourHandLen * SIN(@curAngle))
SET @curHourHandY2 = @curHourHandY1 + (@curHourHandLen * COS(@curAngle))
END
ELSE IF @curAngle > (PI() * 1.5) AND @curAngle <= (PI() * 2)
BEGIN
SET @intQuarter = 4
SET @curAngle = (PI() * 2) - @curAngle
SET @curHourHandX2 = @curHourHandX1 - (@curHourHandLen * SIN(@curAngle))
SET @curHourHandY2 = @curHourHandY1 - (@curHourHandLen * COS(@curAngle))
END
ELSE
BEGIN
PRINT 'Could not work out the hour hand''s quarter segment'
PRINT @curAngle
RETURN
END

-- Work out how far round the clock the minute is and set the position from that
SET @curAngle = (PI() * 2) * (CONVERT(numeric(11, 9), DATEPART(minute, GETDATE())) / 60)
IF @curAngle >= 0 AND @curAngle <= PI() / 2
BEGIN
SET @intQuarter = 1
SET @curMinuteHandX2 = @curMinuteHandX1 + (@curMinuteHandLen * SIN(@curAngle))
SET @curMinuteHandY2 = @curMinuteHandY1 - (@curMinuteHandLen * COS(@curAngle))
END
ELSE IF @curAngle > PI() / 2 AND @curAngle <= PI()
BEGIN
SET @intQuarter = 2
SET @curAngle = PI() - @curAngle
SET @curMinuteHandX2 = @curMinuteHandX1 + (@curMinuteHandLen * SIN(@curAngle))
SET @curMinuteHandY2 = @curMinuteHandY1 + (@curMinuteHandLen * COS(@curAngle))
END
ELSE IF @curAngle > PI() AND @curAngle <= (PI() * 1.5)
BEGIN
SET @intQuarter = 3
SET @curAngle = @curAngle - PI()
SET @curMinuteHandX2 = @curMinuteHandX1 - (@curMinuteHandLen * SIN(@curAngle))
SET @curMinuteHandY2 = @curMinuteHandY1 + (@curMinuteHandLen * COS(@curAngle))
END
ELSE IF @curAngle > (PI() * 1.5) AND @curAngle <= (PI() * 2)
BEGIN
SET @intQuarter = 4
SET @curAngle = (PI() * 2) - @curAngle
SET @curMinuteHandX2 = @curMinuteHandX1 - (@curMinuteHandLen * SIN(@curAngle))
SET @curMinuteHandY2 = @curMinuteHandY1 - (@curMinuteHandLen * COS(@curAngle))
END
ELSE
BEGIN
PRINT 'Could not work out the minute hand''s quarter segment'
PRINT @curAngle
RETURN
END

/** Loop through Y axis **/
SET @curY = 0
WHILE @curY <= 100
BEGIN

/** Loop through X axis **/
SET @curX = 0
SET @strLine = ''
WHILE @curX <= 100
BEGIN

/** Need to work out how far away the coordinate is from the hour hand **/
-- Find lengths A, B and C for triangle made by hour hand's line and the coordinate
-- C is the hour hand, coordinate is inbetween A and B
SET @curA = SQRT(POWER(ABS(@curX - @curHourHandX1), 2) + POWER(ABS(@curY - @curHourHandY1), 2))
SET @curB = SQRT(POWER(ABS(@curHourHandX2 - @curX), 2) + POWER(ABS(@curHourHandY2 - @curY), 2))
SET @curC = SQRT(POWER(ABS(@curHourHandX2 - @curHourHandX1), 2) + POWER(ABS(@curHourHandY2 - @curHourHandY1), 2))
IF @curB = 0 OR @curC = 0
SET @curAngle = 0
ELSE
SET @curAngle = (POWER(@curB, 2) + POWER(@curC, 2) - POWER(@curA, 2)) / (2 * @curB * @curC)
SET @curAngle = ACOS(@curAngle)

-- If coordinate is perpendicular to line, get the length of the perpendicular
-- otherwise the length from the coordinate to the end of the line
IF @curA * COS(@curAngle) < 0 OR @curA * COS(@curAngle) > @curHourHandLen
SET @intDistance = @curA
ELSE IF @curB * COS(@curAngle) < 0 OR @curB * COS(@curAngle) > @curHourHandLen
SET @intDistance = @curB
ELSE
SET @intDistance = CONVERT(int, @curB * SIN(@curAngle))


/** Need to work out how far away the coordinate is from the minute hand **/
-- Find lengths A, B and C for triangle made by minute hand's line and the coordinate
-- C is the minute hand, coordinate is inbetween A and B
SET @curA = SQRT(POWER(ABS(@curX - @curMinuteHandX1), 2) + POWER(ABS(@curY - @curMinuteHandY1), 2))
SET @curB = SQRT(POWER(ABS(@curMinuteHandX2 - @curX), 2) + POWER(ABS(@curMinuteHandY2 - @curY), 2))
SET @curC = SQRT(POWER(ABS(@curMinuteHandX2 - @curMinuteHandX1), 2) + POWER(ABS(@curMinuteHandY2 - @curMinuteHandY1), 2))
IF @curB = 0 OR @curC = 0
SET @curAngle = 0
ELSE
SET @curAngle = (POWER(@curB, 2) + POWER(@curC, 2) - POWER(@curA, 2)) / (2 * @curB * @curC)
SET @curAngle = ACOS(@curAngle)

-- If coordinate is perpendicular to line, get the length of the perpendicular
-- otherwise the length from the coordinate to the end of the line
IF @curA * COS(@curAngle) < 0 OR @curA * COS(@curAngle) > @curMinuteHandLen
BEGIN
IF @curA < @intDistance
SET @intDistance = CONVERT(int, @curA)
END
ELSE IF @curB * COS(@curAngle) < 0 OR @curB * COS(@curAngle) > @curMinuteHandLen
BEGIN
IF @curB < @intDistance
SET @intDistance = CONVERT(int, @curB)
END
ELSE
IF CONVERT(int, @curB * SIN(@curAngle)) < @intDistance
SET @intDistance = CONVERT(int, @curB * SIN(@curAngle))


/** Finally add a circle round the whole thing **/
SET @curDistanceFromCenter = SQRT(POWER(ABS(@curX - @curCenterX), 2) + POWER(ABS(@curY - @curCenterY), 2))
IF ABS(CONVERT(int, @curDistanceFromCenter - 46)) < @intDistance
SET @intDistance = CONVERT(int, @curDistanceFromCenter - 46)


/** Find out what's at this coordinate **/
IF @curX = 50 AND @curY = 50 SET @strLine = @strLine + 'O' else
SET @strLine = @strLine +
CASE @intDistance
WHEN 0 THEN '#'
WHEN 1 THEN ':'
WHEN 2 THEN '·'
ELSE ' '
END
SET @curX = @curX + 1
--if @intdistance = 0 then set @curtemp = @curAngle
END
PRINT @strLine
SET @curY = @curY + 1
END
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/08/2004 :  09:08:53  Show Profile  Visit ditch's Homepage  Reply with Quote
Nice One

Great minds think alike hey

Duane.
Go to Top of Page

Ratboy
Starting Member

2 Posts

Posted - 06/08/2004 :  09:29:50  Show Profile  Reply with Quote
True - I reckon if we work together we could get a radio alarm going too...
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 06/15/2007 :  04:58:58  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
just found this link.. this is really kewl.. how could someone bored came out witnh this.. ahhaha

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 07/22/2013 :  15:20:31  Show Profile  Visit ditch's Homepage  Reply with Quote
OK - so I was bored again and decided to upgrade this to use the built-in CLR GEOMETRY functionality.

To see the results.
Click on the spatial results tab when completed.



DECLARE	@ClockRadius		INT
DECLARE	@PivotX				INT
DECLARE	@PivotY				INT
DECLARE	@Angle				DECIMAL(5, 2)
DECLARE @GeomString			VARCHAR(MAX) 
DECLARE @GeomStringHH		VARCHAR(MAX)
DECLARE @GeomStringMin		VARCHAR(MAX)
DECLARE @Hours				INT
DECLARE @Minutes			INT 

SET @ClockRadius = 200
SET @PivotX = @ClockRadius + 10
SET @PivotY = @ClockRadius + 10

CREATE TABLE #AngleMaster
(
	theAngle				DECIMAL(5,2),
	theHour					DECIMAL(5, 2),
	theMinute				DECIMAL(5, 2)
)

CREATE TABLE #Draw
(
	OrderBy		INT IDENTITY(1, 1),
	X			INT,
	Y			INT
)


SET @Angle = 0
WHILE	@Angle <= 360
BEGIN
	INSERT INTO #AngleMaster
	(
		theAngle,
		theHour,
		theMinute 
	) 
	VALUES
	(
		@Angle,
		CASE 
			WHEN @Angle > 270 THEN @Angle / 30.00 - 9
			ELSE @Angle / 30.00 + 3
		END,
		CASE 
			WHEN @Angle > 270 THEN @Angle / 6.00 - 45
			ELSE @Angle / 6.00 + 15
		END
	) 
	SET @Angle = @Angle + 0.5
END

INSERT INTO #Draw
SELECT 
	@PivotX +  COS(RADIANS(theAngle)) * @ClockRadius AS X,
	@PivotY +  SIN(RADIANS(theAngle)) * @ClockRadius AS Y
FROM 
	#AngleMaster
ORDER BY theAngle 

INSERT INTO #Draw
SELECT 
	@PivotX +  COS(RADIANS(theAngle)) * (@ClockRadius - 1) AS X,
	@PivotY +  SIN(RADIANS(theAngle)) * (@ClockRadius - 1) AS Y
FROM 
	#AngleMaster
ORDER BY theAngle 

INSERT INTO #Draw
SELECT 
	@PivotX +  COS(RADIANS(theAngle)) * (@ClockRadius - 2) AS X,
	@PivotY +  SIN(RADIANS(theAngle)) * (@ClockRadius - 2) AS Y
FROM 
	#AngleMaster
ORDER BY theAngle 

INSERT INTO #Draw
SELECT 
	@PivotX +  COS(RADIANS(theAngle)) * (@ClockRadius - 3) AS X,
	@PivotY +  SIN(RADIANS(theAngle)) * (@ClockRadius - 3) AS Y
FROM 
	#AngleMaster
ORDER BY theAngle 

INSERT INTO #Draw
SELECT 
	@PivotX +  COS(RADIANS(theAngle)) * (@ClockRadius - 4) AS X,
	@PivotY +  SIN(RADIANS(theAngle)) * (@ClockRadius - 4) AS Y
FROM 
	#AngleMaster
ORDER BY theAngle 


SET @GeomString = ''
SELECT
	@GeomString += STR(X, 3) + ' ' + STR(Y, 3) + ', '
FROM 
	#Draw 
ORDER BY 
	OrderBy 

--****Now get time and draw hands.
SELECT 
	@Hours = DATEPART(hh, GETDATE()),
	@Minutes = DATEPART(MINUTE, GETDATE())

 
SELECT 
	@GeomStringHH = STR(@PivotX, 3) + ' ' + STR(@PivotY, 3) + ', ' +  STR(@PivotX + 30, 3) + ' ' + STR(@PivotY + 30, 3) + ', ' + STR(@PivotX + 30 + COS(RADIANS(theAngle)) * (@ClockRadius * 0.6),3) + ' ' + STR(@PivotY + 30 - SIN(RADIANS(theAngle)) * (@ClockRadius * 0.6),3) + ', ' + STR(@PivotX +  COS(RADIANS(theAngle)) * (@ClockRadius * 0.6),3) + ' ' + STR(@PivotY - SIN(RADIANS(theAngle)) * (@ClockRadius * 0.6),3) + ', ' + STR(@PivotX, 3) + ' ' + STR(@PivotY, 3) + ',  '
FROM 
	#AngleMaster 
WHERE
	theHour = CASE WHEN @Hours > 12 THEN @Hours - 12 ELSE @Hours END


SELECT 
	@GeomStringMin = STR(@PivotX, 3) + ' ' + STR(@PivotY, 3) + ', ' +  STR(@PivotX + 10, 3) + ' ' + STR(@PivotY + 10, 3) + ', ' + STR(@PivotX + 10 + COS(RADIANS(theAngle)) * (@ClockRadius * 0.9),3) + ' ' + STR(@PivotY + 10 - SIN(RADIANS(theAngle)) * (@ClockRadius * 0.9),3) + ', ' + STR(@PivotX +  COS(RADIANS(theAngle)) * (@ClockRadius * 0.9),3) + ' ' + STR(@PivotY - SIN(RADIANS(theAngle)) * (@ClockRadius * 0.9),3) + ', ' + STR(@PivotX, 3) + ' ' + STR(@PivotY, 3) + ',  '
FROM 
	#AngleMaster 
WHERE
	theMinute = @Minutes 


SELECT @GeomString = LEFT(@GeomString, LEN(@GeomString) - 1) --Get rid of last ', '
SELECT @GeomStringHH = LEFT(@GeomStringHH, LEN(@GeomStringHH) - 1) --Get rid of last ', '
SELECT @GeomStringMin = LEFT(@GeomStringMin, LEN(@GeomStringMin) - 1) --Get rid of last ', ' 

select @GeomStringMin  

SELECT
	geometry::STGeomFromText('LINESTRING (' + @GeomString + ')', 0)
UNION ALL
SELECT
	geometry::STGeomFromText('POLYGON ((' + @GeomStringHH + '))', 0)
UNION ALL
SELECT
	geometry::STGeomFromText('POLYGON ((' + @GeomStringMin + '))', 0) 


I have also posted it on my blog here: http://ditchiecubeblog.wordpress.com/2013/07/22/time-in-analog-using-the-built-in-geometry-clr-functionality-in-sql-server-2/
Duane.
http://ditchiecubeblog.wordpress.com/

Edited by - ditch on 07/22/2013 16:55:43
Go to Top of Page

xiaomingzc
Starting Member

USA
3 Posts

Posted - 08/03/2013 :  03:18:10  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000