RE: TSQL problem

2004-01-09 Thread Philip Arnold 
Why not use DateAdd()?

What's in the . - that could make a big difference

 -Original Message-
 From: Shahzad.Butt [mailto:[EMAIL PROTECTED]
 Sent: Friday, January 09, 2004 11:45 AM
 To: CF-Talk
 Subject: TSQL problem


 I know its coldfusion group but I wonder if someone can help
 me with this query. What the alternative of Evaluate() in TSQL?

 SELECT
 09/01/2004 + 'CAST(..)' AS myDateTime

 FROM myTest



 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: TSQL problem

2004-01-09 Thread Shahzad.Butt 
Following is the query

 
SELECT

DateAdd(Day, CAST(LEFT(CAST(PanelLocalDT AS Decimal(10,5)),
CHARINDEX('.', CAST(PanelLocalDT AS Decimal(10,5)))-1) AS INT),
'1899-12-30') 

 
 + ' ' 
 
 + 'CAST(((CaST(RIGHT(CONVERT(decimal(10,4),PanelLocalDT),
LEN(CONVERT(decimal(10,4),PanelLocalDT)) -
charindex(''.'',CONVERT(decimal(10,4),PanelLocalDT))) AS Float(8)) / 100
* 1440 / 100) / 60 ) AS int(4))

 
:
CAST(((CaST(RIGHT(CONVERT(decimal(10,4),((CaST(RIGHT(CONVERT(decimal(10,
4),PanelLocalDT), LEN(CONVERT(decimal(10,4),PanelLocalDT)) -
charindex(''.'',CONVERT(decimal(10,4),PanelLocalDT))) AS Float(8)) / 100
* 1440 / 100) / 60 )),
LEN(CONVERT(decimal(10,4),((CaST(RIGHT(CONVERT(decimal 
(10,4),PanelLocalDT), LEN(CONVERT(decimal(10,4),PanelLocalDT)) -
charindex(''.'',CONVERT(decimal(10,4),PanelLocalDT))) AS Float(8)) / 100
* 1440 / 100) / 60 ))) -
charindex(''.'',CONVERT(decimal(10,4),((CaST(RIGHT(CONVERT(decimal(10,4)
,PanelLocalDT), LEN(CONVERT(decimal(10,4),PanelLocalDT)) -
charindex(''.'',CONVERT(decimal(10,4),PanelLocalDT))) AS
Float(8)) / 100 * 1440 / 100) / 60  AS Float(8)) / 100 * 60 / 100)
) AS Int(4)) 

 
: 00' myDate

>From dbo.AFxEvents
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: TSQL problem

2004-01-09 Thread Philip Arnold 
Why is the second one in quotes?

You shouldn't need to do that as long as you convert things right

There's too much there for me to break out easily without seeing the
data, but if I'm right, you're trying to make a date/time calculated out
of a date/time field, right?

 -Original Message-
 From: Shahzad.Butt [mailto:[EMAIL PROTECTED]
 Sent: Friday, January 09, 2004 12:04 PM
 To: CF-Talk
 Subject: RE: TSQL problem


 Following is the query

 SELECT

 DateAdd(Day, CAST(LEFT(CAST(PanelLocalDT AS Decimal(10,5)),
 CHARINDEX('.', CAST(PanelLocalDT AS Decimal(10,5)))-1) AS INT),
 '1899-12-30')

+ ' '

+ 'CAST(((CaST(RIGHT(CONVERT(decimal(10,4),PanelLocalDT),
 LEN(CONVERT(decimal(10,4),PanelLocalDT)) -
 charindex(''.'',CONVERT(decimal(10,4),PanelLocalDT))) AS
 Float(8)) / 100
 * 1440 / 100) / 60 ) AS int(4))

:
 CAST(((CaST(RIGHT(CONVERT(decimal(10,4),((CaST(RIGHT(CONVERT(d
 ecimal(10,
 4),PanelLocalDT), LEN(CONVERT(decimal(10,4),PanelLocalDT)) -
 charindex(''.'',CONVERT(decimal(10,4),PanelLocalDT))) AS
 Float(8)) / 100
 * 1440 / 100) / 60 )),
 LEN(CONVERT(decimal(10,4),((CaST(RIGHT(CONVERT(decimal
(10,4),PanelLocalDT),
 LEN(CONVERT(decimal(10,4),PanelLocalDT)) -
 charindex(''.'',CONVERT(decimal(10,4),PanelLocalDT))) AS
 Float(8)) / 100
 * 1440 / 100) / 60 ))) -
 charindex(''.'',CONVERT(decimal(10,4),((CaST(RIGHT(CONVERT(dec
 imal(10,4)
 ,PanelLocalDT), LEN(CONVERT(decimal(10,4),PanelLocalDT)) -
charindex(''.'',CONVERT(decimal(10,4),PanelLocalDT))) AS
 Float(8)) / 100 * 1440 / 100) / 60  AS Float(8)) / 100 * 60 / 100)
 ) AS Int(4))

: 00' myDate

 From dbo.AFxEvents



 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: TSQL problem

2004-01-09 Thread Dave Watts 
 I know its coldfusion group but I wonder if someone can help me 
 with this query. What the alternative of Evaluate() in TSQL?

 SELECT 
 09/01/2004 + 'CAST(..)' AS myDateTime

 FROM myTest

You can use sp_executesql, EXECUTE, or EXEC, depending on which version of
SQL Server you're using. You should try to avoid this when possible, though,
as it's usually inefficient.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]