I have a problem converting datetime to integer (and than back to
datetime).
Depending whether the time is AM or PM, same date is converted to two
different integer representations, which holds as true on reversal
back to datetime.
AM Example:
declare @DI integer; declare @DD datetime
set @DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
set @DD = cast (@DI as datetime)
print @DI; print @DD
Result:
37690
Mar 12 2003 12:00AM
PM Example:
declare @DI integer; declare @DD datetime
set @DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
set @DD = cast (@DI as datetime)
print @DI; print @DD
Result:
37691
Mar 13 2003 12:00AM
Now, this is not a big problem if I knew that this is how it is
supposed to work. Is this how SQL Server is supposed to work?
Hi All,How do you convert int value to datetime datatype in sql servere.g 900mins to hh:mm:ssRegardsOla*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
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.
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.
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
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
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.
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.
We 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
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
I 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.
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?
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)
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.
As 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.
Edit: There seems to be some interaction between GROUP BY/NEWID() that can cause problems if these functions are used directly in an aggregate query. Please read the posts on this thread about this, and use caution.
This script creates three functions, F_RANDOM_INTEGER, F_RANDOM_SAMPLE, and F_RANDOM_DATETIME. The last parameter of each function must be function NEWID() to generate the random number.
Theses functions are designed for applications where it is necessary to generate random integers, random datetimes, or take random samples from sets of data. Typical applications would be software testing, inventory control, auditing, and product quality testing.
Function F_RANDOM_INTEGER returns a random integer in the range of the input parameters so that the return value is >= @START_INT and <= @END_INT. It is valid for any range of two integer values.
Function F_RANDOM_SAMPLE returns a 1 or a 0 to determine if a sample should be selected, based on the input sample rate. Input parameter @SAMPLE_RATE should be between 1 and 999,999. The sample rate determines how many samples should be selected out of each 1,000,000 samples. A sample rate below 1 will never select a sample, and a sample rate above 999,999 will always select a sample. A sample rate of 1,000 = 0.1%, 50,000 = 5%, 63,775 = 6.3775%, 100,000 = 10%, and 500,000 = 50%.
F_RANDOM_DATETIME returns a random datetime value >= @START_TIME and < @END_TIME. It is valid for any datetime range. Input parameters default, if null, to @START_TIME = '19000101' and @END_TIME = '19000102’. The datetime is random to the level of clock ticks (1/300 of as second). Note that the latest time is not included in the range of datatime values that can be returned. This is to allow selection of times within adjacent time periods, without having to specify times to the level of milliseconds. This means a range of 1990-12-01 01:00:00.000 through 1990-12-01 02:00:00.000 will never return a value of 1990-12-01 02:00:00.000.
The NEWID() function is the basis of the random numbers. These functions should not to be considered random for purposes of data encryption or other high security applications. However, they should be adequate for business applications of the types mentioned above. I conducted extensive testing with the functions where I generated millions of results, analyzed the results various ways to look for non-random patterns, and I saw no evidence of non-random results.
The script also includes a demo of each function, and sample output from the demos is also included.
The demo script uses the number table function, F_TABLE_NUMBER_RANGE, available on this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
if objectproperty(object_id('dbo.F_RANDOM_INTEGER'),'IsScalarFunction') = 1 begin drop function dbo.F_RANDOM_INTEGER end go create function dbo.F_RANDOM_INTEGER ( @START_INTint, @END_INTint, @NEWIDuniqueidentifier ) returns int as /* Function: F_RANDOM_INTEGER
This function returns a random integer value >= @START_INT and <= @END_INT.
Valid for any integer range.
Requires newid() to be input parameter @NEWID to generate the random number.
-- Return random integer between -100, 200000 select [Random Integer] = [dbo].[F_RANDOM_INTEGER](-100, 200000,newid()) */ begin
-- Set default values for input dates if they are null if @START_INT is null begin set @START_INT = 0 end if @END_INT is null begin set @END_INT =1000000 end
-- Set order of input parameters so that return value is always -- the same no matter what order the input values are passed. if @START_INT > @END_INT select @sn = @END_INT, @en = @START_INT else select @sn = @START_INT, @en = @END_INT
-- Return start int if start int = end int if @sn = @en return @sn
-- Get modulus select @mod = @en-@sn+1
-- Get random bigint from input parameter @NEWID select @rand_bigint = abs(convert(bigint,convert(varbinary(20),@NEWID)))
-- Get random integer select @rand_result = @sn+(@rand_bigint%@mod)
return @rand_result
end go grant execute on dbo.F_RANDOM_INTEGER to public go
if objectproperty(object_id('dbo.F_RANDOM_SAMPLE'),'IsScalarFunction') = 1 begin drop function dbo.F_RANDOM_SAMPLE end go create function dbo.F_RANDOM_SAMPLE ( @SAMPLE_RATEint, @NEWIDuniqueidentifier ) returns int as /* Function: F_RANDOM_SAMPLE
This function returns a 1 or a 0 to determine if a sample should be selected, based on the sample rate. It is designed to select random samples at a specific rate.
Input parameter @SAMPLE_RATE should be between 1 and 999,999. The sample rate determines how many samples should be selected out of each 1,000,000 samples. A sample rate below 1 will never select a sample, and a sample rate above 999,999 will always select a sample. 1,000 = 0.1%, 50,000 = 5%, 63,775 = 6.3775%, 100,000 = 10%, and 500,000 = 50%
Requires newid() to be input parameter @NEWID to generate the random number.
-- Select sample 200,000 times in 1,000,000 samples (20%) select [Random Sample] = [dbo].[F_RANDOM_SAMPLE](200000,newid()) */ begin
declare @rand_bigint bigint
-- Get random bigint from @NEWID select @rand_bigint = abs(convert(bigint,convert(varbinary(20),@NEWID)))
-- Select sample if the modulus of @rand_bigint is less than the sample rate return case when @rand_bigint%1000000 < @SAMPLE_RATE then 1 else 0 end
end go grant execute on dbo.F_RANDOM_SAMPLE to public go
if objectproperty(object_id('dbo.F_RANDOM_DATETIME'),'IsScalarFunction') = 1 begin drop function dbo.F_RANDOM_DATETIME end go create function dbo.F_RANDOM_DATETIME ( @START_TIMEdatetime, @END_TIMEdatetime, @NEWIDuniqueidentifier ) returns datetime as /* Function: F_RANDOM_DATETIME
This function returns a random datetime value >= @START_TIME and < @END_TIME.
Valid for any datetime range.
Input parameters default, if null, to: @START_TIME'19000101' @END_TIME'19000102'
Requires newid() to be input parameter @NEWID to generate the random number.
-- Return random time between 08:30 and 12:00 select [Random Time] = [dbo].[F_RANDOM_DATETIME]('08:30:00.000','12:00:00.000',newid()) */ begin
declare @st datetime declare @et datetime
declare @hours int declare @ms int declare @ticks bigint declare @rand_ticks bigint declare @rand_bigint bigint
declare @remaining_ticks int declare @return_hours int declare @return_ms int
-- Set default values for input dates if they are null if @START_TIME is null begin set @START_TIME = '19000101' end if @END_TIME is null begin set @END_TIME = '19000102' end
-- Set order of input parameters so that return value is always -- the same no matter what order the input values are passed. if @START_TIME > @END_TIME select @st = @END_TIME, @et = @START_TIME else select @st = @START_TIME, @et = @END_TIME
-- Return start time if start time = end time if @st = @et return @st
-- Get hours boundary difference. -- Subtract 1 from diff, before dividing by 2 and multiplying by 2 -- so the milliseconds remaining is always positive and -- hours is always >= zero. set @hours = ((datediff(hh,@st,@et)-1)/2)*2
-- Get remainder milliseconds set @ms = datediff(ms,0,@et-dateadd(hh,@hours,@st))
-- Convert remainder milliseconds to -- SQL Server 'clock ticks' of 1/300 of a second set @ticks = ((@ms/10)*3) + ((@ms%10)/3)
-- Add hours * tick per hour (3600*300) to give total -- ticks between @START_TIME and @END_TIME set @ticks = @ticks + (@hours * 0000001080000 )
-- Get random bigint from input parameter @NEWID select @rand_bigint = abs(convert(bigint,convert(varbinary(20),@NEWID)))
-- Get random number of ticks select @rand_ticks = @rand_bigint%@ticks
-- Get hours component of random ticks select @return_hours = @rand_ticks/1080000
-- Get left over ticks after removing hours. select @remaining_ticks = @rand_ticks%1080000
--Convert remaining clock ticks back to milliseconds select @return_ms = ((@remaining_ticks/3)*10) + floor(((@remaining_ticks%3)*3.5))
-- Return the random time between the start and end time return dateadd(ms,@return_ms,dateadd(hh,@return_hours,@st))
end go grant execute on dbo.F_RANDOM_DATETIME to public go
insert into @t select -- Get integert in range of 1 to 10,000,000 [Random Integer] = [dbo].[F_RANDOM_INTEGER](1,10000000,newid() ) from -- Function F_TABLE_NUMBER_RANGE -- available in Script Library forum F_TABLE_NUMBER_RANGE(1,100000)
select [Right Int] = [Random Integer]%10, [Count] = count(*) from @t a group by [Random Integer]%10 order by 1,2
select [Million Range] = [Random Integer]/1000000, [Count] = count(*) from @t a group by [Random Integer]/1000000 order by 1,2
insert into @t select -- Sample rate = 6.3775% [Sample Taken] = [dbo].[F_RANDOM_SAMPLE](63775,newid()) from -- Function F_TABLE_NUMBER_RANGE -- available in Script Library forum F_TABLE_NUMBER_RANGE(1,100000)
select [Sample Taken], [Result Count] = count(*) from @t a group by [Sample Taken] order by 1,2
go
print '-----------------------------------------------------------------' print ' Demo F_RANDOM_DATETIME function' print '-----------------------------------------------------------------' print '' select Random_Datetime = convert(varchar(23),[dbo].[F_RANDOM_DATETIME]( a.ST, a.ET,newid() ) ,121) , [Start] = convert(varchar(23),a.ST ,121) , [End] = convert(varchar(23),a.ET ,121) , a.Comment from ( select ST = getdate(), ET = getdate()+2 , Comment = 'Now thru 2 days from now' union all select '20060101', '20060102' , 'One day diff' union all select '20030101', '20030101' ,'Both times same' union all select '20030101', '20030108' ,'One week diff' union all select '20021228', '20030104' ,'One week diff' union all select '20010701', '20010713' ,'12 day diff' union all select '20010701', '20010714' ,'13 day diff' union all select '20010630', '20010713' ,'13 day diff' union all select '19901201 01:00:00.000', '19901201 02:00:00.000' ,'1 hour diff' union all select '19901201 01:00:33.003', '19901201 02:00:33.003' ,'1 hour diff' union all select '19901201 01:00:00.000', '19901201 01:30:00.000' ,'30 min diff' union all select '19901201 01:00:33.447', '19901201 01:30:33.447' ,'30 min diff' union all select '19901201 01:00:00.000', '19901201 01:05:00.000' ,'5 min diff' union all select '19901201 01:00:29.123', '19901201 01:05:29.123' ,'5 min diff' union all select '19901201 01:00:00.000', '19901201 01:01:00.000' ,'1 min diff' union all select '19901201 01:00:00.000', '19901201 01:00:01.000' ,'1 sec diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.100' ,'100 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.050' ,'50 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.023' ,'23 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.020' ,'20 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.013' ,'13 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.010' ,'10 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.007' ,'7 ms diff' union all select '19901201 01:00:00.000', '19901201 01:00:00.003' ,'3 ms diff' union all select '20030101', '20030201' ,'One month diff 31 days' union all select '20030101', '20040101' ,'One year diff' union all select '20050101', '20070101' ,'Two year diff' union all select '20060101', '20060301' ,'2 month diff' union all select null, '20060101' ,'Start time null' union all select '20060102', null ,'End time null' union all select null, null ,'Both null' union all select '17530101', '99991231 23:59:59.997' ,'Max datetime diff' union all select '99991231 23:59:59.997','17530101' ,'Max datetime diff reversed' ) a
Demo Results:
----------------------------------------------------------------- Demo F_RANDOM_INTEGER function -----------------------------------------------------------------
----------------------------------------------------------------- Demo F_RANDOM_SAMPLE function -----------------------------------------------------------------
(100000 row(s) affected)
Sample Taken Result Count ------------ ------------ 0 93669 1 6331
(2 row(s) affected)
----------------------------------------------------------------- Demo F_RANDOM_DATETIME function -----------------------------------------------------------------
Random_Datetime Start End Comment ----------------------- ----------------------- ----------------------- -------------------------- 2006-07-24 08:21:22.593 2006-07-23 17:54:47.283 2006-07-25 17:54:47.283 Now thru 2 days from now 2006-01-01 06:44:36.897 2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 One day diff 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 2003-01-01 00:00:00.000 Both times same 2003-01-05 01:57:02.183 2003-01-01 00:00:00.000 2003-01-08 00:00:00.000 One week diff 2003-01-01 20:02:05.550 2002-12-28 00:00:00.000 2003-01-04 00:00:00.000 One week diff 2001-07-02 11:35:11.147 2001-07-01 00:00:00.000 2001-07-13 00:00:00.000 12 day diff 2001-07-02 16:39:57.433 2001-07-01 00:00:00.000 2001-07-14 00:00:00.000 13 day diff 2001-07-06 12:33:53.087 2001-06-30 00:00:00.000 2001-07-13 00:00:00.000 13 day diff 1990-12-01 01:15:42.530 1990-12-01 01:00:00.000 1990-12-01 02:00:00.000 1 hour diff 1990-12-01 01:02:21.647 1990-12-01 01:00:33.003 1990-12-01 02:00:33.003 1 hour diff 1990-12-01 01:21:06.267 1990-12-01 01:00:00.000 1990-12-01 01:30:00.000 30 min diff 1990-12-01 01:26:17.983 1990-12-01 01:00:33.447 1990-12-01 01:30:33.447 30 min diff 1990-12-01 01:00:56.327 1990-12-01 01:00:00.000 1990-12-01 01:05:00.000 5 min diff 1990-12-01 01:03:20.423 1990-12-01 01:00:29.123 1990-12-01 01:05:29.123 5 min diff 1990-12-01 01:00:21.617 1990-12-01 01:00:00.000 1990-12-01 01:01:00.000 1 min diff 1990-12-01 01:00:00.443 1990-12-01 01:00:00.000 1990-12-01 01:00:01.000 1 sec diff 1990-12-01 01:00:00.050 1990-12-01 01:00:00.000 1990-12-01 01:00:00.100 100 ms diff 1990-12-01 01:00:00.000 1990-12-01 01:00:00.000 1990-12-01 01:00:00.050 50 ms diff 1990-12-01 01:00:00.010 1990-12-01 01:00:00.000 1990-12-01 01:00:00.023 23 ms diff 1990-12-01 01:00:00.017 1990-12-01 01:00:00.000 1990-12-01 01:00:00.020 20 ms diff 1990-12-01 01:00:00.007 1990-12-01 01:00:00.000 1990-12-01 01:00:00.013 13 ms diff 1990-12-01 01:00:00.000 1990-12-01 01:00:00.000 1990-12-01 01:00:00.010 10 ms diff 1990-12-01 01:00:00.003 1990-12-01 01:00:00.000 1990-12-01 01:00:00.007 7 ms diff 1990-12-01 01:00:00.000 1990-12-01 01:00:00.000 1990-12-01 01:00:00.003 3 ms diff 2003-01-14 09:00:09.520 2003-01-01 00:00:00.000 2003-02-01 00:00:00.000 One month diff 31 days 2003-08-27 11:47:04.100 2003-01-01 00:00:00.000 2004-01-01 00:00:00.000 One year diff 2006-11-23 03:57:21.737 2005-01-01 00:00:00.000 2007-01-01 00:00:00.000 Two year diff 2006-01-12 08:50:40.717 2006-01-01 00:00:00.000 2006-03-01 00:00:00.000 2 month diff 1933-11-15 13:39:10.050 NULL 2006-01-01 00:00:00.000 Start time null 1997-05-28 06:42:32.407 2006-01-02 00:00:00.000 NULL End time null 1900-01-01 01:50:42.743 NULL NULL Both null 2758-10-18 13:50:47.987 1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 Max datetime diff 8426-03-24 13:51:08.407 9999-12-31 23:59:59.997 1753-01-01 00:00:00.000 Max datetime diff reversed
I am trying use the decimal data type for a field in SQL Server. When I input the values below, they round off. 73.827 Rounds to 74 1925.1 Rounds to 1925 119.79 Rounds to 120 What am I missing? Access never gave me this issue. Do you see any reason this would happen? I am entering the values into the table directly!
I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
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)
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
Hi 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
I 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?
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:
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.
