Look at "convert" function in books on line.
Brian Freeman
Carnegie Technologies/Bluewave Computing
(770) 916-0595 ext. 415
www.carnegie.com
www.bluewave-computing.com
-----Original Message-----
From: Fadeyi Kunle
[mailto:mssqldba-ezmlmshield-x18983259.[Email address protected]
Sent: Monday, October 25, 2004 10:45 AM
To: LazyDBA Discussion
Subject: RE: Suspect Spam RE: Stored Procedure - Returning rows from a
table variable.
Hi Folks,
How do I convert a character - 00:00:00.000 to date in SQL
Server? Thanks.
Kunle
-----Original Message-----
From: Brian Freeman [mailto:mssqldba-ezmlmshield-x95668272.[Email
address protected]
Sent: Tuesday, October 19, 2004 2:00 PM
To: LazyDBA Discussion
Subject: Suspect Spam RE: Stored Procedure - Returning rows from a table
variable.
Use EXEC:
CREATE PROCEDURE kunle_GetTotalOrderLine
(
@TableName varchar(50),
@OrderNumber varchar(50)
)
AS
Declare @strSQL VARCHAR(1000)
--This second select is not needed
--SELECT @TableName= (SELECT name FROM sysobjects WHERE name =
@TableName)
Set @strSQL='SELECT qofNo, PoNo, AccNo, Punchout, DC, Charge, Total,
TaxTotal, qofDate, SenderID, Region, sp_Comments,
Comments, Status, DateApproved, DateDisApproved, Accepted,
DateAccepted, Shipping, [ID]
FROM ' + @TableName + ' WHERE qofNo = ' + @OrderNumber + ' ORDER BY
[ID] ASC'
Exec (@strSQL)
GO
Brian Freeman
Carnegie Technologies/Bluewave Computing
(770) 916-0595 ext. 415
www.carnegie.com
www.bluewave-computing.com
-----Original Message-----
From: Fadeyi Kunle [mailto:mssqldba-ezmlmshield-x78379409.[Email address
protected]
Sent: Tuesday, October 19, 2004 1:50 PM
To: LazyDBA Discussion
Subject: RE: Stored Procedure - Returning rows from a table variable.
Hi Folks,
Please, how can I get this to work:
CREATE PROCEDURE kunle_GetTotalOrderLine
(
@TableName varchar(50),
@OrderNumber varchar(50)
)
AS
SELECT @TableName= (SELECT name FROM sysobjects WHERE name =
@TableName)
SELECT qofNo, PoNo, AccNo, Punchout, DC, Charge, Total, TaxTotal,
qofDate, SenderID, Region, sp_Comments,
Comments, Status, DateApproved, DateDisApproved, Accepted,
DateAccepted, Shipping, [ID]
FROM @TableName
WHERE qofNo = @OrderNumber
ORDER BY [ID] ASC
GO
-----Original Message-----
From: Patrick Trongo [mailto:mssqldba-ezmlmshield-x82508544.[Email
address protected]
Sent: Monday, October 18, 2004 1:18 PM
To: LazyDBA Discussion
Subject: Suspect Spam RE: Average time in SQL Server
OR: select convert(datetime,avg(convert(real,YOURDATEFIELD))) from
YOURTABLE
OR: select convert(datetime,avg(convert(Float,YOURDATEFIELD))) from
YOURTABLE
Interestingly,
Depending on what type I used to test this I got answers off by a few
seconds....
-----Original Message-----
From: Peter Nolan
[mailto:mssqldba-ezmlmshield-x42392992.[Email address protected]
Sent: Monday, October 18, 2004 12:02 PM
To: LazyDBA Discussion
Subject: RE: Average time in SQL Server
Importance: High
Try something like...
SELECT cast(avg(cast(DATEFIELD as float)) as datetime) from TABLE
-----Original Message-----
From: Murali_Ramaswamy [mailto:mssqldba-ezmlmshield-x13941487.[Email
address protected]
Sent: 18 October 2004 16:34
To: LazyDBA Discussion
Subject: Re: Average time in SQL Server
Can any one answer this one.
Thanks,
Murali Ramaswamy
Murali Ramaswamy
10/18/04 09:42 AM
To: [Email address protected]
cc:
Subject: Average time in SQL Server
Hi All,
I have the time format in Mi:ss:ms as said below and i have calculated
the below using excel. What is the function which calculates the average
time in sql server or equivalent logic. In oracle we could have used
DateSerial and calculate the average and then again convert it to time
format Mi:ss:ms to get it in time format.
13:19.6
13:41.7
13:51.9
14:08.1
14:45.3
14:52.5
15:50.0
The Average time:14:21:3
I used the AVG function, It works only on number.
Also i tried using the below logic But works only for seconds and
milliseconds. Since float data type takes only two values scale and
precision (eg: 5.2) whereas i need something like 1:5.2 , Basically I
need it to work for minutes:seconds:milliseconds.
Logic for Seconds:Milliseconds calculation declare @var1 datetime
declare @var2 datetime declare @var3_hour int declare @var4_min int
declare @var5_secs int declare @var6_millsecs bigint declare @Result
float
Set @var1='1900-01-01 02:01:02.750'
Set @var2='1900-01-01 02:01:02.750'
select @var1 + @var2
select @var3_hour=datepart(hh,(@var1 + @var2)) select
@var4_min=datepart(mi,(@var1 + @var2)) select
@var5_secs=datepart(ss,(@var1
+ @var2)) select @var6_millsecs=datepart(ms,(@var1 + @var2))
select @Result= Cast(@var5_secs as varchar(10)) + '.' +
Cast(@var6_millsecs as varchar(10))
select @Result=avg(@Result)/2
select @Result
Thanks,
Murali Ramaswamy
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
**********************************************************************
CONFIDENTIALITY.This e-mail and any attachments are confidential and may
also be privileged. If you are not the named recipient, please notify
the sender immediately and do not disclose the contents to another
person, use it for any purpose, or store or copy the information in any
medium. Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be the views
of Pinnacle Insurance plc.
If you have received this email in error please immediately notify the
Pinnacle Helpdesk on +44 (0) 20 8207 9555.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page