Can someone tell me if this is a SQL Server bug? I tried this in both
version 7 and 2000, the results are the same.
DECLARE @timeA DATETIME
DECLARE @timeB DATETIME
DECLARE @msDiff INT
SET @timeA = GETDATE()
SET @msDiff = 0
WHILE @msDiff <= 10
BEGIN
SET @timeB = DATEADD(ms,@msDiff,@timeA)
PRINT 'If adding ' + CONVERT(VARCHAR,@msDiff) + ' milliseconds to Time
B, then Time B is ' + CONVERT(VARCHAR,DATEDIFF(ms,@timeA,@timeB)) + '
millisecond greater than Time A'
SET @msDiff = @msDiff + 1
END
This seems like a serious bug if an application depends heavily on
milliseconds comparison.
Hi, I am writing a stored proc that will so a very simple search for users in the database. In my database I am storing a birthday as a datetime column called 'bday'. Users can search for people between a certain age range; say 23 - 30.
Here is my search query:
alter procedure sp_wm_quickSearch @lookingFor int, @mySex int, @country varchar(500), @ageTo int, @ageFrom int, @state varchar(10), @userid int
as
select * from wm_user a inner join wm_user_details b on a.userid=b.userid where a.lookingfor = 0 and a.sex = 1 and a.country = 'United States of America' and a.state = 'Washington'
I found a simple function that looks like it can do what i need it to do:
CREATE FUNCTION dbo.fnYearsApart ( @FromDate DATETIME, @ToDate DATETIME ) RETURNS INT AS BEGIN RETURN CASE WHEN @FromDate > @ToDate THEN NULL WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1 ELSE DATEDIFF(month, @FromDate, @ToDate) END / 12 END
MY QUESTIONS IS THIS:
How do I call this function in my query and pass it the age, and use the age as a search param. I imagine it woudl look something like this:
select * from wm_user a inner join wm_user_details b on a.userid=b.userid where a.lookingfor = 0 and a.sex = 1 and a.country = 'United States of America' and a.state = 'Washington' and fnYearsApart(a.bday, Some DateTime.Now function) < 24 and > 30
but this line doesn't work: (obviously i need to replace datetime.now
fnYearsApart(a.bday, Some DateTime.Now function) < 24 and > 30
Edit: There seems to be some interaction between GROUP BY/NEWID() that can cause problems if these functions are used directly in an aggregate query. Please read the posts on this thread about this, and use caution.
This script creates three functions, F_RANDOM_INTEGER, F_RANDOM_SAMPLE, and F_RANDOM_DATETIME. The last parameter of each function must be function NEWID() to generate the random number.
Theses functions are designed for applications where it is necessary to generate random integers, random datetimes, or take random samples from sets of data. Typical applications would be software testing, inventory control, auditing, and product quality testing.
Function F_RANDOM_INTEGER returns a random integer in the range of the input parameters so that the return value is >= @START_INT and <= @END_INT. It is valid for any range of two integer values.
Function F_RANDOM_SAMPLE returns a 1 or a 0 to determine if a sample should be selected, based on the input sample rate. Input parameter @SAMPLE_RATE should be between 1 and 999,999. The sample rate determines how many samples should be selected out of each 1,000,000 samples. A sample rate below 1 will never select a sample, and a sample rate above 999,999 will always select a sample. A sample rate of 1,000 = 0.1%, 50,000 = 5%, 63,775 = 6.3775%, 100,000 = 10%, and 500,000 = 50%.
F_RANDOM_DATETIME returns a random datetime value >= @START_TIME and < @END_TIME. It is valid for any datetime range. Input parameters default, if null, to @START_TIME = '19000101' and @END_TIME = '19000102’. The datetime is random to the level of clock ticks (1/300 of as second). Note that the latest time is not included in the range of datatime values that can be returned. This is to allow selection of times within adjacent time periods, without having to specify times to the level of milliseconds. This means a range of 1990-12-01 01:00:00.000 through 1990-12-01 02:00:00.000 will never return a value of 1990-12-01 02:00:00.000.
The NEWID() function is the basis of the random numbers. These functions should not to be considered random for purposes of data encryption or other high security applications. However, they should be adequate for business applications of the types mentioned above. I conducted extensive testing with the functions where I generated millions of results, analyzed the results various ways to look for non-random patterns, and I saw no evidence of non-random results.
The script also includes a demo of each function, and sample output from the demos is also included.
The demo script uses the number table function, F_TABLE_NUMBER_RANGE, available on this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
if objectproperty(object_id('dbo.F_RANDOM_INTEGER'),'IsScalarFunction') = 1 begin drop function dbo.F_RANDOM_INTEGER end go create function dbo.F_RANDOM_INTEGER ( @START_INTint, @END_INTint, @NEWIDuniqueidentifier ) returns int as /* Function: F_RANDOM_INTEGER
This function returns a random integer value >= @START_INT and <= @END_INT.
Valid for any integer range.
Requires newid() to be input parameter @NEWID to generate the random number.
-- Return random integer between -100, 200000 select [Random Integer] = [dbo].[F_RANDOM_INTEGER](-100, 200000,newid()) */ begin
-- Set default values for input dates if they are null if @START_INT is null begin set @START_INT = 0 end if @END_INT is null begin set @END_INT =1000000 end
-- Set order of input parameters so that return value is always -- the same no matter what order the input values are passed. if @START_INT > @END_INT select @sn = @END_INT, @en = @START_INT else select @sn = @START_INT, @en = @END_INT
-- Return start int if start int = end int if @sn = @en return @sn
-- Get modulus select @mod = @en-@sn+1
-- Get random bigint from input parameter @NEWID select @rand_bigint = abs(convert(bigint,convert(varbinary(20),@NEWID)))
-- Get random integer select @rand_result = @sn+(@rand_bigint%@mod)
return @rand_result
end go grant execute on dbo.F_RANDOM_INTEGER to public go
if objectproperty(object_id('dbo.F_RANDOM_SAMPLE'),'IsScalarFunction') = 1 begin drop function dbo.F_RANDOM_SAMPLE end go create function dbo.F_RANDOM_SAMPLE ( @SAMPLE_RATEint, @NEWIDuniqueidentifier ) returns int as /* Function: F_RANDOM_SAMPLE
This function returns a 1 or a 0 to determine if a sample should be selected, based on the sample rate. It is designed to select random samples at a specific rate.
Input parameter @SAMPLE_RATE should be between 1 and 999,999. The sample rate determines how many samples should be selected out of each 1,000,000 samples. A sample rate below 1 will never select a sample, and a sample rate above 999,999 will always select a sample. 1,000 = 0.1%, 50,000 = 5%, 63,775 = 6.3775%, 100,000 = 10%, and 500,000 = 50%
Requires newid() to be input parameter @NEWID to generate the random number.
-- Select sample 200,000 times in 1,000,000 samples (20%) select [Random Sample] = [dbo].[F_RANDOM_SAMPLE](200000,newid()) */ begin
declare @rand_bigint bigint
-- Get random bigint from @NEWID select @rand_bigint = abs(convert(bigint,convert(varbinary(20),@NEWID)))
-- Select sample if the modulus of @rand_bigint is less than the sample rate return case when @rand_bigint%1000000 < @SAMPLE_RATE then 1 else 0 end
end go grant execute on dbo.F_RANDOM_SAMPLE to public go
if objectproperty(object_id('dbo.F_RANDOM_DATETIME'),'IsScalarFunction') = 1 begin drop function dbo.F_RANDOM_DATETIME end go create function dbo.F_RANDOM_DATETIME ( @START_TIMEdatetime, @END_TIMEdatetime, @NEWIDuniqueidentifier ) returns datetime as /* Function: F_RANDOM_DATETIME
This function returns a random datetime value >= @START_TIME and < @END_TIME.
Valid for any datetime range.
Input parameters default, if null, to: @START_TIME'19000101' @END_TIME'19000102'
Requires newid() to be input parameter @NEWID to generate the random number.
-- Return random time between 08:30 and 12:00 select [Random Time] = [dbo].[F_RANDOM_DATETIME]('08:30:00.000','12:00:00.000',newid()) */ begin
declare @st datetime declare @et datetime
declare @hours int declare @ms int declare @ticks bigint declare @rand_ticks bigint declare @rand_bigint bigint
declare @remaining_ticks int declare @return_hours int declare @return_ms int
-- Set default values for input dates if they are null if @START_TIME is null begin set @START_TIME = '19000101' end if @END_TIME is null begin set @END_TIME = '19000102' end
-- Set order of input parameters so that return value is always -- the same no matter what order the input values are passed. if @START_TIME > @END_TIME select @st = @END_TIME, @et = @START_TIME else select @st = @START_TIME, @et = @END_TIME
-- Return start time if start time = end time if @st = @et return @st
-- Get hours boundary difference. -- Subtract 1 from diff, before dividing by 2 and multiplying by 2 -- so the milliseconds remaining is always positive and -- hours is always >= zero. set @hours = ((datediff(hh,@st,@et)-1)/2)*2
-- Get remainder milliseconds set @ms = datediff(ms,0,@et-dateadd(hh,@hours,@st))
-- Convert remainder milliseconds to -- SQL Server 'clock ticks' of 1/300 of a second set @ticks = ((@ms/10)*3) + ((@ms%10)/3)
-- Add hours * tick per hour (3600*300) to give total -- ticks between @START_TIME and @END_TIME set @ticks = @ticks + (@hours * 0000001080000 )
-- Get random bigint from input parameter @NEWID select @rand_bigint = abs(convert(bigint,convert(varbinary(20),@NEWID)))
-- Get random number of ticks select @rand_ticks = @rand_bigint%@ticks
-- Get hours component of random ticks select @return_hours = @rand_ticks/1080000
-- Get left over ticks after removing hours. select @remaining_ticks = @rand_ticks%1080000
--Convert remaining clock ticks back to milliseconds select @return_ms = ((@remaining_ticks/3)*10) + floor(((@remaining_ticks%3)*3.5))
-- Return the random time between the start and end time return dateadd(ms,@return_ms,dateadd(hh,@return_hours,@st))
end go grant execute on dbo.F_RANDOM_DATETIME to public go
insert into @t select -- Get integert in range of 1 to 10,000,000 [Random Integer] = [dbo].[F_RANDOM_INTEGER](1,10000000,newid() ) from -- Function F_TABLE_NUMBER_RANGE -- available in Script Library forum F_TABLE_NUMBER_RANGE(1,100000)
select [Right Int] = [Random Integer]%10, [Count] = count(*) from @t a group by [Random Integer]%10 order by 1,2
select [Million Range] = [Random Integer]/1000000, [Count] = count(*) from @t a group by [Random Integer]/1000000 order by 1,2
insert into @t select -- Sample rate = 6.3775% [Sample Taken] = [dbo].[F_RANDOM_SAMPLE](63775,newid()) from -- Function F_TABLE_NUMBER_RANGE -- available in Script Library forum F_TABLE_NUMBER_RANGE(1,100000)
select [Sample Taken], [Result Count] = count(*) from @t a group by [Sample Taken] order by 1,2
go
print '-----------------------------------------------------------------' print ' Demo F_RANDOM_DATETIME function' print '-----------------------------------------------------------------' print '' select Random_Datetime = convert(varchar(23),[dbo].[F_RANDOM_DATETIME]( a.ST, a.ET,newid() ) ,121) , [Start] = convert(varchar(23),a.ST ,121) , [End] = convert(varchar(23),a.ET ,121) , a.Comment from ( select ST = getdate(), ET = getdate()+2 , Comment = 'Now thru 2 days from now' union all select '20060101', '20060102' , 'One day diff' union all select '20030101', '20030101' ,'Both times same' union all select '20030101', '20030108' ,'One week diff' union all select '20021228', '20030104' ,'One week diff' union all select '20010701', '20010713' ,'12 day diff' union all select '20010701', '20010714' ,'13 day diff' union all select '20010630', '20010713' ,'13 day diff' union all select '19901201 01:00:00.000', '19901201 02:00:00.000' ,'1 hour diff' union all select '19901201 01:00:33.003', '19901201 02:00:33.003' ,'1 hour diff' union all select '19901201 01:00:00.000', '19901201 01:30:00.000' ,'30 min diff' union all select '19901201 01:00:33.447', '19901201 01:30:33.447' ,'30 min diff' union all select '19901201 01:00:00.000', '19901201 01:05:00.000' ,'5 min diff' union all select '19901201 01:00:29.123', '19901201 01:05:29.123' ,'5 min diff' union all select '19901201 01:00:00.000', '19901201 01:01:00.000' ,'1 min diff' union all select '19901201 01:00:00.000', '19901201 01:00:01.000' ,'1 sec diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.100' ,'100 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.050' ,'50 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.023' ,'23 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.020' ,'20 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.013' ,'13 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.010' ,'10 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.007' ,'7 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.003' ,'3 ms diff' union all select '20030101', '20030201' ,'One month diff 31 days' union all select '20030101', '20040101' ,'One year diff' union all select '20050101', '20070101' ,'Two year diff' union all select '20060101', '20060301' ,'2 month diff' union all select null, '20060101' ,'Start time null' union all select '20060102', null ,'End time null' union all select null, null ,'Both null' union all select '17530101', '99991231 23:59:59.997' ,'Max datetime diff' union all select '99991231 23:59:59.997','17530101' ,'Max datetime diff reversed' ) a
Demo Results:
----------------------------------------------------------------- Demo F_RANDOM_INTEGER function -----------------------------------------------------------------
----------------------------------------------------------------- Demo F_RANDOM_SAMPLE function -----------------------------------------------------------------
(100000 row(s) affected)
Sample Taken Result Count ------------ ------------ 0 93669 1 6331
(2 row(s) affected)
----------------------------------------------------------------- Demo F_RANDOM_DATETIME function -----------------------------------------------------------------
Random_Datetime Start End Comment ----------------------- ----------------------- ----------------------- -------------------------- 2006-07-24 08:21:22.593 2006-07-23 17:54:47.283 2006-07-25 17:54:47.283 Now thru 2 days from now 2006-01-01 06:44:36.897 2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 One day diff 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 Both times same 2003-01-05 01:57:02.183 2003-01-01 00:00:00.000 2003-01-08 00:00:00.000 One week diff 2003-01-01 20:02:05.550 2002-12-28 00:00:00.000 2003-01-04 00:00:00.000 One week diff 2001-07-02 11:35:11.147 2001-07-01 00:00:00.000 2001-07-13 00:00:00.000 12 day diff 2001-07-02 16:39:57.433 2001-07-01 00:00:00.000 2001-07-14 00:00:00.000 13 day diff 2001-07-06 12:33:53.087 2001-06-30 00:00:00.000 2001-07-13 00:00:00.000 13 day diff 1990-12-01 01:15:42.530 1990-12-01 01:00:00.000 1990-12-01 02:00:00.000 1 hour diff 1990-12-01 01:02:21.647 1990-12-01 01:00:33.003 1990-12-01 02:00:33.003 1 hour diff 1990-12-01 01:21:06.267 1990-12-01 01:00:00.000 1990-12-01 01:30:00.000 30 min diff 1990-12-01 01:26:17.983 1990-12-01 01:00:33.447 1990-12-01 01:30:33.447 30 min diff 1990-12-01 01:00:56.327 1990-12-01 01:00:00.000 1990-12-01 01:05:00.000 5 min diff 1990-12-01 01:03:20.423 1990-12-01 01:00:29.123 1990-12-01 01:05:29.123 5 min diff 1990-12-01 01:00:21.617 1990-12-01 01:00:00.000 1990-12-01 01:01:00.000 1 min diff 1990-12-01 01:00:00.443 1990-12-01 01:00:00.000 1990-12-01 01:00:01.000 1 sec diff 1990-12-01 01:00:00.050 1990-12-01 01:00:00.000 1990-12-01 01:00:00.100 100 ms diff 1990-12-01 01:00:00.000 1990-12-01 01:00:00.000 1990-12-01 01:00:00.050 50 ms diff 1990-12-01 01:00:00.010 1990-12-01 01:00:00.000 1990-12-01 01:00:00.023 23 ms diff 1990-12-01 01:00:00.017 1990-12-01 01:00:00.000 1990-12-01 01:00:00.020 20 ms diff 1990-12-01 01:00:00.007 1990-12-01 01:00:00.000 1990-12-01 01:00:00.013 13 ms diff 1990-12-01 01:00:00.000 1990-12-01 01:00:00.000 1990-12-01 01:00:00.010 10 ms diff 1990-12-01 01:00:00.003 1990-12-01 01:00:00.000 1990-12-01 01:00:00.007 7 ms diff 1990-12-01 01:00:00.000 1990-12-01 01:00:00.000 1990-12-01 01:00:00.003 3 ms diff 2003-01-14 09:00:09.520 2003-01-01 00:00:00.000 2003-02-01 00:00:00.000 One month diff 31 days 2003-08-27 11:47:04.100 2003-01-01 00:00:00.000 2004-01-01 00:00:00.000 One year diff 2006-11-23 03:57:21.737 2005-01-01 00:00:00.000 2007-01-01 00:00:00.000 Two year diff 2006-01-12 08:50:40.717 2006-01-01 00:00:00.000 2006-03-01 00:00:00.000 2 month diff 1933-11-15 13:39:10.050 NULL 2006-01-01 00:00:00.000 Start time null 1997-05-28 06:42:32.407 2006-01-02 00:00:00.000 NULL End time null 1900-01-01 01:50:42.743 NULL NULL Both null 2758-10-18 13:50:47.987 1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 Max datetime diff 8426-03-24 13:51:08.407 9999-12-31 23:59:59.997 1753-01-01 00:00:00.000 Max datetime diff reversed
I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
Hi, I'm inserting a datetime values into sql server 2000 from c#
SQL server table details Table nameate_test columnname datatype No int date_t DateTime
C# coding SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI"); connectionToDatabase.Open(); DataTable dt1 = new DataTable(); dt1.Columns.Add("no",typeof(System.Int16)); dt1.Columns.Add("date_t", typeof(System.DateTime)); DataRow dr = dt1.NewRow(); dr["no"] = 1; dr["date_t"] = DateTime.Now; dt1.Rows.Add(dr); for(int i=0;i<dt1.Rows.Count;i++) { string str=dt1.Rows["no"].ToString(); DateTime dt=(DateTime)dt1.Rows["date_t"]; string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')"; SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase); cmd.ExecuteNonQuery(); MessageBox.Show("saved"); } When I run the above code, data is inserted into the table The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column. Is there any conversion needed for millisecond values?
Recently I changed over a ASP script from our old Access 97 database to our new SQL database. When I changed it over, some of my SQL pulls on my Active Server Page started to give me erros. One of them is the function date(). When I used it pulling from Access like this :
strSQLQ = "SELECT * FROM cocoitem WHERE CustNum = '" & strcustnum & "' AND stat = 'C' AND [due-date] > DateAdd('yyyy', -1, Date()) Order By [cust-po], [due-date] ASC ;"
Then it worked fine. When I redirected the ASP to the new SQL server I recieved an error like this:
Microsoft OLE DB Provider for SQL Server error '80040e14'
'Date' is not a recognized function name.
/scripts/order/shippingstatsclose.asp, line 45
So my question is, what is the SQL server equivalent of the function Date()?
Hi I have a problem which I’m not sure how to resolve! I have a aspx with two drop down list; 1st one has (annual salary, daily salary, hourly rate) 2nd one has ( 0-4999, 5000-9999......)
The second one is generated by the value selected in the first one. I have stored the values in a table (as nvarchar) and used sqldatasource to run a query, which matches the entry in the first box and fill the second drop down list accordingly.
How ever I have a problem, when I want some one to search for example; an average salary of 5000-9999, it should output entry's that have a similar daily rate, and hourly rate... But I’m not sure how I can accomplish this, does any one have any ideas! Many thanks
Hi,the Soundex search words that sounds similar.Does MS SQL Server has some function to make some intuitive search?For example, for search term database, it should return rows that contains: "database" word, but also rows that contains "Oracle", "MySQL", "MS SQL" etc. terms.
I am sorry to continue bothering this forum with the continuation of this question but here it is. And thank you to Craig for giving me the equivalent of the function Date() in SQL. Now when I pull from the SQl Server with the old ASP pull with this statement using GETDATE()
strSQLQuery1 = "SELECT * FROM cocoitem WHERE CustNum = '" & strcustnum & "' AND (stat = 'O' OR stat = 'F') AND [due-date] > DateAdd('yyyy', -1, GETDATE()) Order By [cust-item], [due-date] ASC;"
I get this: Microsoft OLE DB Provider for SQL Server error '80040e14'
Invalid parameter 1 specified for dateadd.
/scripts/order/shippingstatsopen.asp, line 28
So I guess I need to also know the equivalent of DateAdd . Also, does anyone know of a Access Function to Sql 7 function comparison chart so I can write for the new database comprehendingly?
I am new to this, SQL Server. I hv worked in Oracle. Now I am learning 'SQL Server'. In Oracle, it has features like Packages and functions (PL/SQL), like that in SQL Server, is there any facility available?.
Hi, I am still learning the bells and whistles of SQL Server and was wondering if I can find out the query that caused my trigger to fire, so that I can log this in another audit table. I have an If Update ( My_Column ) trigger set up, where once an update happens to My_Column much information from the updated row along with , Host_Name and App_Name is sent. I also want to send the exact query used to update it, any ideas? Any comments, suggestions will be greatly appreciated. Thanks, Kartik
I'm moving some queries out of an Access front end and creating views out ofthem in SQL Server 2005 express. In some of the numeric fields, I use nzquite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.Is there anything equivalent to this in SQL Server? Right now I'm usingCASE WHEN ... but it seems like an awful lot of script to write just toreplace null with a zero.Any help would be greatly appreciated.Thanks!
I would like to know how to write a function that will go through datain a column and change it. For example, I have a column of ISBN's forbooks, and the ISBN's have a period in them randomly distributed. I'dlike to pull the period out.Any help I can get will be appriciated.Thanks,Bill
Hi,Do any versions of SQL Server support the following functions, asthey appear in the Oracle Database:-1) XMLElement2) XMLAttributes3) XMLForestThanks in Advance for your replyByeAmardeep Verma
Does anyone have financial functions to be run in SQL Server 2000? For example, future value, interest rate, payments, and so on. Or where can I find them on Internet?
I'm upsizing MS-Access to SQL Server 2005. I need to convert the following functions: TRANSFORM PIVOT FORMAT MID
Are there any similar functions in SQL Server?
Also I have a query as follows:
SELECT Mid$([AccountNumber],3,8) AS [Account#], Format([checkamount]*100,"000000000") AS Amount, IIf(IsNull([statusdate])," ",Format([statusdate],"yyyymmdd")) AS [Date] FROM tblResult;
I have 3 fields in my table say (F1, F2, F3). I want to get the max value out of the three fields for each row. I can create a user-defined function which accepts 3 arguments and then return the max value if i am using SQL Server 2000. But now i am using only SQL Server 7.0 (it does not support user-defined functions :confused: )
So any one could kindly let me know how could i do it in SQL Server 7.0
Hi, I saw in some websites that there are functions freeze and thaw in SQL server.I want to freeze the SQL server for some time and then use the thaw to unfreeze.I want to know how it could be done in SQL server 2005
Hi all!!Does anybody know how I can create a function in SQL 7.0?? I have tocreate functions that return a value that can be used in a selectstatement. I think SQL Server version 7.0 doesn't support CREATEFUNCTION, does it?Ex:Select MyFunction(Parameter)From MyTableThanks a lot,
Hi , I have a question about calling functions in SQl Server 2005.
Let's say that I have created as a dbo a function called Calculations.
If I want to call it from T-SQL I will write Select dbo.Calculations (arguments if any) etc.
My question is If I can skip the "dbo" part. Call the function without using the dbo . Can I do that ? Should I create the function as supervisor ? Does Sql Server has a property or something which will allow me to call the function without using the "dbo." ?
I've below value in a column with data type - TEXT
QU 221025U2V/AN G-DT DL A 5 1A- 11,5,SF,230,30162,LZ,2,118,0,0,10170,25,06
This text value has some special characters in it. and I could not paste the exact value as this text box is not allowing me to do so. So, for reference I've attached a screenshot (Capture.png) of the value.
I want to fetch last two values from this text i.e. 25 and 06. (It can be anything like 56R,06T but will be the last two values separated by comma)...
How do I use the CAST or CONVERT function in the code below, I require a third column (named Diff) which Minus the StartTime from the EndTime and the result is outputted in the third column (named Diff).
Calculation: @Diff = (@EndTime - @StartTime)
I still want the variables (@StartTime and @EndTime) to remain as nvarchar.
The code:
DECLARE @StartTime nvarchar(10) = '12:10'; DECLARE @EndTime nvarchar(10) = '12:30'; DECLARE @Diff time(1) = '00:00'; SELECT @StartTime AS '@StartTime', @EndTime AS '@EndTimes', @Diff AS '@Diff';
Hi all,What are the fundamentals (fundamental functions) that most small tomedium sized organizations that use MS SQL Server 7 or 2000 valuemost?What's your insight?OK, here's my biased definition of small to medium sized organization,annual revenue from 20m to 300m.Thanks.DL
--Exec Database.Employees --Use Database --Go --Create PROCEDURE AEM.TempTable --AS --BEGIN --Select * into #emptemp From Database.Employees --End --Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
I have successfully developed a Transact-SQL stored procedure which calls a CLR function, which calls a web service. I am using a Visual Studio 2005 SQL Server Project to perform the necessary magic.
Is there a neat way to deploy this stuff to a production server?
It is bad enough deploying the sp and function, but the web server proxy also needs to be changed to refer to the production web service.
I have a SQLDataSource that gets it's data from a SQL 2000 database table. I have configured it to generate the Update/Delete commands, which look correct. I then have a GridView that is using this SqlDataSource to show the data with "Edit" & "Delete" buttons (the default ones from the GridView). My problem is that while all commands (Edit, Delete) work on my local server, they do NOT work on my live server. In my connection string, I specifiy a username and password like this:
<add name="Project_Management.My.MySettings.WebReportsConnectionString" connectionString="Data Source=Karlweb;Initial Catalog=WebReport;Persist Security Info=True;User ID=VbUser;Password=VbUser" providerName="System.Data.SqlClient" /> I have access to change the permissions on my production server, so I gave this "VbUser" every allow permission I could find and still I could not Edit or Delete records. What I mean by they "do not work" is that when I click the Edit button, the GridView switches to edit mode, but when I click Update, the changes are not written. When I click the Delete button, the page just refreshes and the record is not deleted. As this is working on my local server, I think the problem lies in some permission or configuration of the server. Does anyone have any suggestions of where I could look or what to change? Thank you!
User-Defined string Functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net)
Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net):
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character. RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string. OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps). OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps). PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side. PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side. PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides. CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression. STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters. STRFILTER(): Removes all characters from a string except those specified. GETWORDCOUNT(): Counts the words in a string. GETWORDNUM(): Returns a specified word from a string. GETALLWORDS(): Inserts the words from a string into the table. PROPER(): Returns from a character expression a string capitalized as appropriate for proper names. RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search. ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999). ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
Plus, there are CHM files in English, French, Spanish, German and Russian.
Plus, there are versions for MS SQL SERVER, SYBASE ASA, DB2, Oracle.
More than 8000 people have already downloaded my functions. I hope you will find them useful as well.
For more information about string UDFs MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net) please visit the http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,29527
Please, download the file http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,29527
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
