While trying to solve a SQL challenge I found myself trying to understand what is happening when you CAST a INT to date time.
Trying to understand the results. Here are some random numbers and Castings. My question is why do they produce the datetimes they do?
SELECT CAST((1.1) AS DATETIME)
SELECT CAST((200) AS DATETIME)
SELECT CAST((15) AS DATETIME)
SELECT CAST((99.99999) AS DATETIME)
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
I am trying to get random numbers to have a unique value for different processes, then I can identify each process. What happens is that I use rand() function without seed, so I got my random numbers, but after shutting down SQLServer and try to get again another random number after booting up, the same series of random numbers is given again and again. So if anyone knows how I can get unique values,even though reseting the server, and using random function or any other method which automatically provides unique values,I'll really appreciate it if you let me know it.
This is the function: select rand()
Alberto.
Hi,
I have an int column that i want to have random numbers inserted into it.
How can I create random integer numbers to insert into my column?
Thanks
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
I have a DB using random GUID values as a PK. I know it should not be that way but it is third party and I am trying to persuade them to do something else, like a sequential GUID using NEWSEQUENTIALID. My question is this. Since I have GUID values already in the database, if we change the table definition from simply uniquidentifier to NEWSEQUENTIALID what is to keep NEWSEQUENTIALID from generating a GUID that already exists and creating a PK violation?
View 3 Replies View RelatedWe have a SQL Server 2008R2 system that has heavy usage to one specific table. I have tuned basically all I can as far as making sure SQL Statements are using good indexes. From time to time a group of folks will log into Mgt Studio and run SQL Statements like this, leave the query open and once in a while it will cause blocking to other SQL running our online system
The query is like this: select ID,* from tablename with (nolock) where ID like 'MSPRYy%'
The results come back within less than 1 second. However, they leave this window open which is what causes this to be a HEAD BLOCKER and blocks other SQL Statements from running.
Hi, how do I use the Rand Function to generate a Random integer value between 1 and 20. Any help will be appreciated.
Thanks
I have a table with a field called "host" and I want to replace the value of this column in each row with 'Host' followed by a random number. I have created the below function to do this:
DECLARE @Random varchar;
SELECT @Random = CONVERT(int, (20+1)*RAND())+ 1;
UPDATE AnalogLines Set [Host Name] = 'Host' + @Random
This sorta works... the problem is, the random number is determine and then the SAME random number is applied to each row. I need a different random number for every row. Any ideas?
Hi there... I've got an interesting one, that I can't seem to get my head around. Maybe some legend out there might be able to give me a hand...
I'm looking for a way to produce a weighted set of random numbers. I'm doing some work for a client at the moment, and they want to issue 3 random "reward cards" to their members at certain times. These are a bit like discount vouchers etc. The problem is some cards have need to have a higher frequency than the others. I guess a similar problem to baseball cards, you buy a pack of cards, you get mostly common cards, but every now and then, you get a rare card.
Here is the table setup:
CREATE TABLE [dbo].[Cards](
[CardID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Cards_CardID] DEFAULT (newid()),
[CardName] [nvarchar](50) NOT NULL,
[InsertRatio] [float] NULL,
CONSTRAINT [PK_Cards] PRIMARY KEY CLUSTERED
(
[CardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 1', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 2', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 3', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 4', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 5', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 6', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 7', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 8', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 9', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 1', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 2', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 3', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Very Rare 1', 0.005) /* 1:200 ratio */
So what I need to do, is have a Stored Proc that I can execute and it returns back 3 random rows. Now in that single run, a card can't be duplicated.
Notice the Insert Ratio column? This has the ratio of the probability, eg a 1:50 insert ratio is equal to 0.02. For the common cards, a NULL value indicates it is a common.
Eventually, this table would have about 1000 rows in it, and about 200 of those would have various ratios (eg 1:50, 1:200, 1:1000, 1:8000 etc)
Any ideas or comments?
Thanks in advance.
Leddo
Brisbane, Australia
Hi all!
I´m having some trouble with a SSIS package that I´ve been using five months or so now.
I have a package scheduled to run every 2 minutes to load data into my SQL Server instance
from our mobile computer systems. This data comes is read as flat file sources. My package runs 24/7.
My problem is that this package randomly returns "An error occurred while attempting to perform a type cast". This message is then returned during maybe 3 or 4 executions of the package. The error then vanishes into thin air, without me doing anything what so ever, and execution continues. The very same file that previously returned the errors is now processed without any problems.
I have no clue why this is, but I get the feeling that there has to be a reason for these random errors. To me, the type cast error seems to indicate problems with the data source (the flat file), but since the same file suddenly get processed without errors I have no clue where to start looking.
Anyone having the same issue?
Regards,
Daniel
s it possible to create random numbers within a range like : .28 to .57 ?
UPDATE Nop_ProductVariant
SET percent = RAND() .28 to .57
So the percent field would look like :
ProdID percent
1001 .38
1002 .29
1003 .40
etc . .
all rows having a different value
I have written a script to GENERATE Random Password. But I want to modify that to a some kind of password which is little easy to remember!
I have seen some of the online sites where they generate passwords which are easy to remember and having some way to remember them,
my script:-
declare @db_pswd nvarchar(4000);
declare @len1 int = 16,
@min tinyint = 40,
@range tinyint = 74,
@exclude varchar(50) = '11cdtyuXR#0:;<=>?@O[]`^/abfty#$%^&1234567890*',
@output varchar(500)
[Code] .....
I'm grappling with this issue which I thought was basic VB programming; I'm trying to insert a random number (between 100 and 999) into a SQL table column (=Status_ID). This is input as part of a user submitting helpdesk requests via a APS.Net Web Form. The 'Status_ID' field is obviously not visible to the user but will help reference this Helpdesk request on the database.Here is the code:Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click If Page.IsValid Then ' Define data objects Dim conn As SqlConnection Dim comm As SqlCommand ' Read the connection string from web.config Dim connectionString As String = _ ConfigurationManager.ConnectionStrings("ITNet_Students").ConnectionString ' Initialize connection conn = New SqlConnection(connectionString) ' Create command comm = New SqlCommand( _ "INSERT INTO HelpDesk (First_Name, Last_Name, StudentID, PersonalEmail," & _ "CategoryID, SubjectID, Description, StatusID) " & _ "VALUES (@First_Name, @Last_Name, @StudentID, @PersonalEmail, " & _ "@CategoryID, @SubjectID, @Description, @StatusID)", conn) ' Use randomize Randomize() Dim randomvalue As Integer ' Generate random value between 999 and 100. randomvalue = Int((900 * Rnd()) + 100) ' Add command parameters comm.Parameters.Add("@First_Name", System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@First_Name").Value = fnameTextBox.Text . . . comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int) comm.Parameters("@StatusID").Value = randomvalue 'Enclose database code in Try-Catch-Finally Try ' Open connection conn.Open() ' Execute the command comm.ExecuteNonQuery() ' Reload page if the query executed successfully Response.Redirect("HelpDesk.aspx") Catch ' Display error message dbErrorMessage.Text = _ "Error submitting the help desk request! Please try again later, and/or change the entered data!" Finally 'close connection conn.Close() End Try End If End Sub----------------------------------------------------------------------------------------------------------------------I keep getting the error message under 'Catch' and the page 'HelpDesk.aspx' is not reloading; the 'comm.ExecuteNonQuery()' is not executing.Can anyone spot any inconsistencies in the declaration of the 'randomvalue' variable?P.S: this code works fine if you replace 'randomvalue' with any integer in 'comm.Parameters("@StatusID").Value = randomvalue'
View 2 Replies View RelatedHello,
I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).
I'd like to take the top 5%, for instance, based upon a column containing random numbers.
Can anyone suggest a highly efficient method of populating a column with random numbers.
Thanks in advance.
Rod
I have a set of rows in a table like for example
Client ID Client Name Date Score
1 Smith 12/31/2014 25
1 Smith 10/15/2014 45
2 John 08/11/2014 55
2 John 06/18/2014 15
3 Rose 04/15/2014 12
4 Mike 07/23/2014 28
5 Mary 01/5/2014 56
6 Lisa 08/1/2014 54
6 Lisa 05/10/2014 34
Now I want to use Row Number function or any way where I can get the result as below
Client ID Client Name Date Score RowNo
1 Smith 12/31/2014 25 1
1 Smith 10/15/2014 45 2
2 John 08/11/2014 55 1
2 John 06/18/2014 15 2
3 Rose 04/15/2014 12 1
4 Mike 07/23/2014 28 1
5 Mary 01/5/2014 56 1
6 Lisa 08/1/2014 54 1
6 Lisa 05/10/2014 34 2
Anyny in-built sql function that gives us numeric values in a string?
I have to deal with some inconsistent US phone numbers stored in DB. They are stored as
(xxx)xxx-xxxx
xxx xxxx xxxx
(xxx) xxx-xxxx
xxx-xxx-xxxx
xxxxxxxxxx
I don't want to apply nested REPLACE function to eliminate all unnecessary characters to get 10 digit number from DB.
I am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5.Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."
DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2)
SELECT @Start=01.00, @End=20.00
;WITH NumberSequence( Number ) AS
(
SELECT @start as Number
UNION ALL
SELECT Number + 00.01
FROM NumberSequence
WHERE Number < @end
I am trying to split the annual cost into monthly numbers based on the contract Period.Since the contract period varies from company to company not sure how to implement the logic.
create table #Invoice
(
Company Varchar(50),
Startdate2015 DateTime,
EndDate2015 DateTime,
ContractPeriod2015 Int,
ContractAmount2015 Float,
[code]..
I want to assign consecutive numbers to a block of data where block of data is based on days consecutive to each other i.e., one day apart.
Date format is: YYYY-MM-DD
Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
1 2011-07-29 00:00:00.000
1 2011-07-30 00:00:00.000
1 2011-07-31 00:00:00.000
[Code] ....
My Attempt:
WITH cte AS
(
SELECTTestId,
TestDate,
ROW_NUMBER() OVER
(
PARTITION BYTestId
[Code] .....
Expected Output:
TestId TestDate OrderId
----------- ----------------------- --------------------
1 2011-07-21 00:00:00.000 1
1 2011-07-22 00:00:00.000 1
1 2011-07-27 00:00:00.000 2
1 2011-07-29 00:00:00.000 3
1 2011-07-30 00:00:00.000 3
[Code] ....
The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.
Hi all,
i need help for deleting the time from a datetime type
what i mean is that instead of
2007-08-15 01:30:00.000
i would get
2007-08-15 00:00:00.000
many thanks
Imran writes "Hi All !
I have required a simple cast that is i want to get just time from datetime field.
Example :
I have data '1/1/1900 10:30 PM' now i want to get just 10:30 pm from it if any one can send me a query please do it.
My email is Imran_mcs2002@yahoo.com"
How do you cast 0 as DateTime? Is this possible to do.I am using UNION to combine two tables. I get an error when doing so...
Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with date
SELECT TOP (100) PERCENT
Opportunity_Name
,Status
,Closed_Status
[code]....
I understand what is causing the error. In the top select statement both Closed_Status AND Expected_Close_Date are datetime values. But in the bottom statement they are int. Â
I understand what is causing the error. In the top select statement both Closed_Status AND Expected_Close_Date are datetime values. In the bottom statement they are int.  So I tried CASTing them as datetime, that didn’t work and I get this error.<o:p></o:p> Conversion failed when converting the varchar value 'Open' to data type int.Is there a way to CAST 0 as a DateTime
Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.
Code:
Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type
From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID
Where D.SchoolID = @DojoID
and C.Days like'%' + @Days + '%'
Union
Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type
From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID
Where D.SchoolID = @DojoID and @Date
Between E.EventStartDate and E.EventEndDate
how can i cast it correctly?
CAST a variable into a datetime object
I need to do a CAST(@variable_name as datetime)
this won't work because @variable_name has the following format
'dd/mm/yy hh:mi:ss:mmmAM'
like how do i specify a style for it.
please help..
James : (
I am trying to automate a load of insert cmd but some of them contain date times so i am trying to insert the following but keep getting
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
What is wrong with the cmd??
INSERT INTO client VALUES (100, NULL, NULL, NULL, NULL, 1, NULL, 0, 0, 0, 0, 0, NULL,Cast( '2013-03-11 10:54:46.529' as datetime))
Hi guys,
I knew that the Function Cast can do this but I tried a lot and I dont want to use the MONTH, YEAR, DAY function.
I have a smalldatetime field with a value of this 12/18/2004 4:02:00 PM
I just like to see it like this 12/18/2004
Hope you can help me up. Thanks.
-vince
I'm reviewing the CAST function using Microsoft SQL server. I generally understand the functionality, but my confusion lies in the results of a particular script which casts a DateTime value to a fixed-point number.
It is as follows:
DECLARE @From DATETIME
DECLARE @To NUMERIC(10,5)
SET @From = '2009-10-11T11:00:00'
SET @To = cast(@From AS NUMERIC(10,5))
PRINT @To
This results in: 40095.45833
I am completely lost as to how 40095.45833 = 2009-10-11T11:00:00. I just do not understand the how that fixed point number equates to that source DateTime data.
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
i am trying to take a field that has part of a date in it, so I have to parse it out as follows:
SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
CAST((SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)) as datetime)
I also tried CONVERT(datetime, (SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)), and got the same message.
how I can parse that field, then convert it to a datetime format for running a DATEDIFF statement?
Hi, I need help please.
I have text source & i need to write it to table field being an integer.
I complains about the column, so i added a derived column but I am clueless in the expression:
Source from text: 02/02/2008
Derived column expression to output: 20080202 in a int datatype
I need the expression to do it.
Please Assist!
Regards
I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
View 0 Replies View RelatedI have problem to execute query with interval date.
If try this query I don't have problem and the output is correct:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '01/01/2015', 121)
AND CONVERT (datetime, '09/01/2015', 121);Instead if try this I have error:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '25/01/2015', 121)
AND CONVERT (datetime, '28/01/2015', 121);
[Err] 22007 - [SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?