Serious SQL Server Datetime Functions Bug...
Jan 7, 2002
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.
Thanks,
Aiden
View 3 Replies
ADVERTISEMENT
Dec 16, 2003
Hi all,
I have a problem with date functions...
In my program I use weeks, and with this variable I need to know which is the first day of the selected week...
For example... I put week 52 in my program... how I can obtain the first day of this week? -> (22/12/2003).
I have proved with this functions...
SET DATEFIRST 1 (to configure Monday as first day of week)
SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)
With this I have the day of the current week... but I can't put my week in this function... aarrgggg.
Please help me...
Thanks a lot.
View 1 Replies
View Related
Apr 11, 2007
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
Thanks!
View 2 Replies
View Related
Jul 23, 2006
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
declare @sn bigint
declare @en bigint
declare @mod bigint
declare @rand_bigint bigint
declare @rand_result int
-- 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
print '-----------------------------------------------------------------'
print ' Demo F_RANDOM_INTEGER function'
print '-----------------------------------------------------------------'
print ''
declare @t table ([Random Integer] int not null )
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
go
print '-----------------------------------------------------------------'
print ' Demo F_RANDOM_SAMPLE function'
print '-----------------------------------------------------------------'
print ''
declare @t table ([Sample Taken] int not null )
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
-----------------------------------------------------------------
(100000 row(s) affected)
Right Int Count
----------- -----------
0 9929
1 10055
2 10009
3 10082
4 9919
5 10022
6 9914
7 9985
8 10098
9 9987
(10 row(s) affected)
Million Range Count
------------- -----------
0 10009
1 9985
2 10142
3 10047
4 9967
5 9907
6 10078
7 10071
8 9790
9 10004
(10 row(s) affected)
-----------------------------------------------------------------
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
(33 row(s) affected)
CODO ERGO SUM
View 6 Replies
View Related
May 26, 2006
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
View 9 Replies
View Related
Jul 9, 2007
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?
thanks,
Mani
View 3 Replies
View Related
Sep 6, 2001
Hello all,
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()?
View 2 Replies
View Related
Feb 4, 2007
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
View 5 Replies
View Related
Jun 22, 2005
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.
View 1 Replies
View Related
Sep 6, 2001
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?
Thank you very much for your help in advance.
View 1 Replies
View Related
Jul 26, 2000
Hi,
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?.
thanks in advance.
srini
View 1 Replies
View Related
Apr 10, 2002
Do we have any builtin functions in SQL Server for certain financial calculations. For eg., like the PV, FV functions in VB
If not, how else do we achieve this thru' a SQL Server stored procedure?
Thanks in advance,
Siva
View 1 Replies
View Related
Apr 12, 2007
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
View 2 Replies
View Related
Apr 20, 2006
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!
View 16 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Jul 20, 2005
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
View 4 Replies
View Related
Aug 17, 2006
Hi everybody,
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?
Thanks in advance
Jaime
View 3 Replies
View Related
Nov 15, 2006
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;
How do I convert this in SQL Server query?
View 6 Replies
View Related
Nov 4, 2004
hai,
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
Thnks in advance
View 3 Replies
View Related
Aug 20, 2007
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
Thanks in Advance
View 5 Replies
View Related
Jul 20, 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,
View 2 Replies
View Related
Oct 4, 2006
Hi
I am trying to build a tree similar to the one in SQL Server Management Studio for the system functions in the SQL language.
I would like to group them by type (e.g. string functions) and display information about the parameters and return types, etc.
Is there a way to get these programmatically? I would like to avoid typing them out by hand.
Thanks
Chris
View 6 Replies
View Related
Apr 23, 2008
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." ?
Thank you
zkar
View 4 Replies
View Related
May 26, 2015
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)...
View 5 Replies
View Related
May 31, 2015
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';
View 7 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Mar 17, 2008
Hi , how to create user defined functions in sql server 2005 to retrive data from multiple tables in asp.net....... Thank U!
View 4 Replies
View Related
Jul 14, 2015
here's an example of what I am trying to do.
--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.
View 7 Replies
View Related
Aug 30, 2006
Dear fellow developers,
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.
This all looks very nasty and complicated.
Am I missing something?
View 4 Replies
View Related
Feb 16, 2007
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!
View 3 Replies
View Related
Feb 7, 2006
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
With the best regards.
View 1 Replies
View Related
Mar 17, 2015
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.
View 7 Replies
View Related
