Hi All,
How do you convert int value to datetime datatype in sql server
e.g 900mins to hh:mm:ss
Regards
Ola
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Hello Everyone,
I need help with conversion type. I'm using Visual Basic 6.0 as my frontend and SQL Server 2000 as my backend. There has been existing data in the database. I would like to know how to convert an integer to datetime format. For example:
This is the actual value from the database.
1087912290
1087912327
I'd like to know how to convert it datetime format.
Any help would be greatly appreciated.
Thanks,
Dennis
Hi all,I have a problem converting datetime to integer (and than back todatetime).Depending whether the time is AM or PM, same date is converted to twodifferent integer representations, which holds as true on reversalback to datetime.AM Example:declare @DI integer; declare @DD datetimeset @DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)set @DD = cast (@DI as datetime)print @DI; print @DDResult:37690Mar 12 2003 12:00AMPM Example:declare @DI integer; declare @DD datetimeset @DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)set @DD = cast (@DI as datetime)print @DI; print @DDResult:37691Mar 13 2003 12:00AMNow, this is not a big problem if I knew that this is how it issupposed to work. Is this how SQL Server is supposed to work?
View 3 Replies View RelatedWorking on a new database where the Date and Time are stored in a Date Time Field.
Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:
transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
  dbo.IntegerToDate(at.transDate) as transDate
  * That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
Hi, I need to convert the values entered into textboxes by the users before I can insert into db.have tried the following. Dim eventnum As string 'tried using integer makes no difference what i declare the variable aseventnum = Convert.ToInt32(txteventnum.Text)This value needs to be inserted into the field event_number wich is datatype int I get the following error message when I try to insert. Conversion failed when converting the varchar value 'eventnum' to data type int.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'eventnum' to data type int.
Source Error:
Line 62: Line 63: ' Execute(query)Line 64: myCommand.ExecuteNonQuery()Line 65: Line 66: 'Close the connection
Source File: C:InetpubloansMemberPagesRequest.aspx.vb Line: 64
Stack Trace:
[SqlException (0x80131904): Conversion failed when converting the varchar value 'eventnum' to data type int.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 Request.Button1_Click(Object sender, EventArgs e) in C:InetpubloansMemberPagesRequest.aspx.vb:64 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Hi, I'm tring to come up with a query to get the difference between two dates, and select all the records whose difference is greater then some number, but it keeps failing. Thanks! Query SELECT Convert(int, DATEDIFF(D, timestamp, getdate())) AS [Days Old]FROM tblProductsWHERE 'Days Old' > 7 Error Message: Conversion failed when converting the varchar value 'Days Old' to data type int.
View 1 Replies View RelatedHaiii..
I save number in database as varchar, but i want to call it back and
convert as double/int. Can it be done
I'm having an issue using the data conversion task and trying to convert from a negative integer that is bounded by brackets e.g, (1) for -1. I keep getting an error that the value cannot be converted although I've tried all signed integer types.
View 6 Replies View RelatedWe have inherited an appointments database that has a table tblAppointments
Within this table there are 2 fields, ApptFrom & ApptTo, these are the appointment start & finish times.
My problem is, the values in these fields are held as integers ranging from 0 - 288. I have worked out the scale for the integers, it is based on a 24hr clock with each segment representing 5mins
Eg: 0 = 0:00
12 = 1.00
24 = 2.00
36 = 3.00
€¦
...
288 = 24:00
What I need is a piece of T_SQL that will translate these into times that can be read by a normal user
So, say I have a record with an ApptFrom = 36 & ApptTo = 42, I need to be able to show these as ApptFrom = 3.00 & ApptTo = 3.30
Any help is greatly appreciated.
Cheers,
Craig
hello all,is there a quick way to convert a zipcode of type int, to a 5 characterchar value ?e.g.declare @zip intdeclare @czip char(5)select @zip = 2109select @czip = convert(char, @zip)select @czipbut with @czip = 02109 instead of 2109 ?thanks in advance
View 2 Replies View RelatedI am taking a five-digit zipcode from a database where it is stored as an int, and I would like to store it as a char(5).
I set my OLE DB source to pull from a proc as:
Code Snippet
SELECT
cust.CustomerID as CustomerID,
case
when cust.Zip > 99999 then Null
when cust.Zip < 0 then Null
else REPLICATE('0',5 - LEN(cast(cust.Zip as varchar(5))))
+ cast(cust.Zip as varchar(5))
end as CustomerZip
However, when I do that, SSIS sees the external column as a string of length 8000, and insists on giving me a truncation warning on validation.
I also tried bringing the Zip field in as an int and doing a data conversion in the SSIS data stream. This worked great until I added a Derived Column transform to do the REPLICATE function as above to add leading zeros to the zip code. Once I did that, SSIS decided that there was a chance of truncation again and started with the truncation warnings.
My derived column is:
Code Snippet
REPLICATE("0",5 - LEN(TRIM([CustomerZip]))) + TRIM([CustomerZip])
BTW - I don't think the TRIM() function is really necessary in the above, but I tried it out of desperation and it made no difference.
This is really starting to drive me nuts. Does anyone have any thoughts on how to convert an integer zipcode to a char(5) string with leading zeros without incurring these validation warnings?
TIA
- Steve
Trying to write the most effective UDF to convert INT to Datetime.
We have a column from a table on AS400 that is a INT type. Some are 4, 5, 6 ,7 digits. I have the 4 digits right. I need to fix it for 5 and 6 digits.
ALTER FUNCTION IntegerToDatetime (@int INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @IntegerToDatetime int
DECLARE @time DATETIME
SET @time = '2001-01-01'
SET @IntegerToDatetime =
CASE
WHEN LEN(@int) = 7
THEN '20' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),4,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),6,2) AS int)
WHEN LEN(@int) = 6
THEN '19' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),1,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),3,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),5,2) AS int)
WHEN LEN(@int) = 5
THEN '200' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),1,1) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),4,2) AS int)
WHEN LEN(@int) = 4 THEN cast(@time AS INT)
END
RETURN (@IntegerToDatetime )
END
GO
INPUT
-------------------------------
990831
981019
RESULT
-------------------------------
1900-01-02 00:00:00.000
1900-05-27 00:00:00.000
http://www.sqlserverstudy.com
Hi
I use this to get all users from active directory.
SELECT *
FROM openquery(
adsi
,'SELECT name, AccountExpires FROM
''LDAP://company/OU=users,dc=company,dc=com''')
WHERE AccountExpires IS NOT NULL AND AccountExpires not in('0','0x7FFFFFFFFFFFFFFF','9223372036854775807','')
AccountExpires returns values like 128514708000000000 (This value represents the number of 100 nanosecond intervals since January 1, 1601 )
How do I convert this value to Datetime?
select getdate() returns a value like 2008-04-18 10:00:00.00 and that's how I'd like my AccountExpires
I was just told that it is better to convert all datetime values to integers for performance reasons. Is this generally true? I am working with time series data so datetime values hold important information.
View 10 Replies View RelatedAs a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.
I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.
Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.
It leads to massive implicit conversions and I am trying to change this.
To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.
Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.
I am attempting to convert an integer value to a string using the Derived Column transformation with the following expression on the field:
(DT_STR,10,1252)prod_id
prod_id is an integer. I was able to do this before however, in the past couple of days, this has failed with the following error:
"An error occurred while attempting to perform a type cast."
To my knowledge, I have not changed anything about this particular data flow within the past couple of days. I have verified that the value is coming in as an integer.
Any help would be greatly appreciated.
I am trying to convert a string variable to integer in SSIS using the expression task.
@[User::Nummer] = (DT_I4) @[User::Name]
But I get an error that the conversion from (DT_WSTR) to (DT_I4) is not possible!!
I have this datetime:Â '2002-12-20 11:59:59'
I want to convert this to date (yyyy-mm-dd) to integer
This works fine for:Â SELECT CONVERT(INT, GETDATE())
But it doesn't work here:Â SELECT CONVERT(INT, '2002-12-20 11:59:59')
I want to convert date to integer without passing through any varchar conversion/result.
Iwant to convert the datetime to the integer value as shown within the brackets.
----2014-12-21 0:00:00 (1419091200000)
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 need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
this is in form VB
Private Sub orderidtxbx_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles orderidtxbx.Click
sqlconnection = New SqlConnection("Data Source= xxxx;Initial Catalog = xxxx; user ID = sa; Password = xxxxx;")
sqlcommand = New SqlCommand("select Customer.LastName, Customer.FirstName from customer where ticket.ticketid ='" & orderidtxbx.Text & "'", sqlconnection)
sqlconnection.Open()
sqldatareader = sqlcommand.ExecuteReader()
ListBox1.Items.Add(sqldatareader.Item("LastName") & " " & sqldatareader.Item("FirstName")
error msg The multi-part identifier "ticket.ticketid" could not be bound
query in sql 2005,I need to make store procedure.
SELECT Itinerary.FlightID, Itinerary.Class, Itinerary.Quantity, Ticket.Date, Flight.FlightNo, Flight.AirLine, Flight.DepartureDate, Flight.ArrivalDate,
Flight.DepartureTime, Flight.ArrivalTime, Flight.Price, Departure.DepartureLocation, Arrival.ArrivalLocation,GST=convert(int,Itinerary. Quantity*0.06*Flight.Price),PST=convert(int,Itiner ary.Quantity*0.07*Flight.Price),TotalPrice=convert (int,(Itinerary.Quantity*0.06*Flight.Price)+(Itine rary.Quantity*0.07*Flight.Price)+Flight.Price)
FROM Flight INNER JOIN
Itinerary ON Flight.FlightID = Itinerary.FlightID INNER JOIN
Ticket ON Itinerary.TicketID = Ticket.TicketID INNER JOIN
Departure ON Flight.DepartureLocationID = Departure.DepartureID INNER JOIN
Arrival ON Flight.ArrivalLocationID = Arrival.ArrivalID
WHERE (Itinerary.ticketid = '@Itinerary.ticketid')
if i change WHERE (Itinerary.ticketid = 1)
it will read the data in the table.
I need to refer that itinerary.ticketid into textbox in form that's why i make like this WHERE (Itinerary.ticketid = '@Itinerary.ticketid') but i don't know what is worng with this.
This error Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '@Itinerary.ticketid' to data type int(but my ticketid value already integer not varchar).
Hello Everyone and thanks for your help in advance. I have an application that inserts a variety of values into a SQL Server database. Among the columns are three DateTime values. I have code working properly on my test server, but when I port the identical code to my production database, I get the following error:
Arithmetic overflow error converting expression to data type datetime. The statement has been terminated
When I remove any type of insert involving date, the application works. I have tried the date in various formats, for instance "09/12/2001" and "20010912", but still get the same error. Obviously, there must be differences in the SQL Servers, but I have never run into this problem before and the current server is running many applications involving dates. I haven't got a clue as to how to solve this problem. Any help is greatly appreciated. Thanks.
Hi,When I open a tabe in the SQL enterprise manager I see the Timestamp Fieldin this Format :24/01/2005 16:45:00However when I'm using the Query analyzer or other SQL Client I see theTimestamp Field in this Format :2005-01-24 16:44:59.997Does Anybody know what to do in order to display the Timestamp Field infirst Format (24/01/2005 16:45:00) ?Please Advise,Yariv--Message posted via http://www.sqlmonster.com
View 3 Replies View RelatedHi Guys, Could some help me....I am trying to use the below specified two field from "SysJobHistory"table from "MSDB". Field name "run_date" and "run_time". Both thisfields are of INT data type. Data saved in the above two fields is inthis this format 20060501 and 204001 respectively. I want save the datafrom this two field in another table in date time format. I want towrite a SELECT statement which converts the data from this two fieldsinto date time format so that it could be saved in another table whichhas date time data type column respectively.RegardsDipesh Shah
View 1 Replies View RelatedI have tables with columns that stores datetime data in int format onSQL server 2000. For example, the datetime for '4/5/200400:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is1081180799. I need to generate reports that display datetime columnsin "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow ismy query statment.select iorg_name as org, ref_num as [ticketnum], c_first_name as[firstname], c_last_name as [lastname], sym as type, [description] assummary, status, dateadd(s,open_date,'12/31/1969 08:00:00pm') asopened, dateadd(s,last_mod_dt,'12/31/1969 08:00:00pm') as irt,dateadd(s,close_date,'12/31/1969 08:00:00pm') as closed fromAHD.dbo.HDreports reportview WHERE reportview.open_date >= 1080882000AND reportview.open_date <= 1081227599.The result shows correctly with those records that are in daylightsaving time. Those records in standard time show 1 hour behind.Does anyone know how to make this query correctly display the data inproperly?
View 1 Replies View RelatedHave imported a file from AS400 into SQL and get the Date numeric as yyyymmdd.
How do I get is as a Datetime.
Example:
Delivery Date 20011201 (numeric)
I've got have a populated table and I want to convert a datetime column so it lists the date only (without the time component)
I tried to run this as a script, but returns an error:
update <table>
where <column>=convert(datetime,convert(char10),<column>,101))
When I run only this part, it does strip the date of the time component but it becomes a string, and I need this field stored as a datetime field:
convert(char(10),hire_date,101)
I'd appreciate any suggestions :)
Everytime I run a simple convert statement, from char to datetime, it works but when I use the `Select Convert(datetime, BeginDate)` in an insert statement it complains with a syntax error on the conversion from char to datetime. E.g.
Insert Into AlaskaData2( CurrIssueDate, InactivationDate)
select Convert (Datetime, CurrIssueDate) CurrIssueDate,
Convert(Datetime, InactivationDate) InactivationDate
from Alaskadata1
go
i have some fields in SQL Server table as nvarchar(50) and the user actually enters date (example : 02/05/07) now they want those fields to be converted to datetime or small datetime field.
How do i achieve it without losing data?
Thanks
bhu
Hi,
I wanted to convert the varchar to date time and here is what i am doing
DECLARE @dt VARCHAR(20)
SET @dt = '20070111' -- YYYYMMDD format
select CONVERT(datetime, @dt, 120)
This works perfectly fine and the result would be- 2007-01-11 00:00:00.000
But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing
"Conversion failed when converting datetime from character string."
Can any one please let me know how do we achieve this?
~Mohan
I have a Database which is having a Counterdate time Column stored in the form of Char(24)
But i need it to be in form of datetime so that i can use the datetime functions on it..When i use the cast or convert inside the function where i am passing this character it gives me error
"Conversion failed when converting datetime from character string."
I am done all permutatiions and combinations for this used
Set @DE = convert ( datetime, @ts,121)
Set @de = cast( @ts as datetime)
BUT ALWZ give me same error.... also when i copy the whole of the data table into some other database the error doesnt come.. i converts the character into the datetime..
I DONT understand why the Server is behaving wiered..
Hoping to get an answer soon.
With regards
Sharad
Database Developer ,
UIC
Hi,
I'm facing a small issue with date conversions. It would have been great if someone could help me out. I have a field in my database (SQL Server 2000) called SavDateTime of type varchar(50). I store dates in this field in the format "dd/MM/yy hh:mms". A sample date would be "23/10/2007 10:15:30 AM". Now I need to have an order by for this field, say like,
select * from sample order by SavDateTime desc
When I execute this query I get an error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". I tried using the convert method also like CONVERT(DATETIME,SavDateTime,103), but the problem still exists. It would have been helpful if you could provide me with a solution to this problem.
Thanks & Regards,
Frens