Converting Integer Values To Datetimes
May 23, 2006
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
Any help is greatly appreciated.
Cheers,
Craig
View 6 Replies
ADVERTISEMENT
Jul 15, 2014
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.
View 4 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Jul 20, 2005
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!
View 3 Replies
View Related
Nov 19, 2006
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
View 4 Replies
View Related
Mar 6, 2008
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 Related
May 2, 2006
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
View 14 Replies
View Related
Apr 3, 2008
Haiii..
I save number in database as varchar, but i want to call it back and
convert as double/int. Can it be done
View 13 Replies
View Related
Jul 20, 2005
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 Related
Dec 18, 2006
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 Related
May 23, 2006
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 Related
Sep 17, 2007
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.
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
View 4 Replies
View Related
Oct 30, 2007
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.
View 7 Replies
View Related
May 20, 2015
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!!
View 2 Replies
View Related
Jun 12, 2007
Following on from some problems I have been having with flat files, I seem to be stumbling from one issue to another.
I have coded my text file with some very contrived delimiters to ensure that my real data isn't tripping up the package. I am stripping all line break chars, tabs etc.
Now I seem to get all rows imported, but numerous integer columns within the file are being set to zero.
When I toggle the Keep Null options in the data flow task it will either import the row, but with the zeros, or not import the row at all. I have opened the file in text editors, and Excel and it all seems fine - in fact the same source file works OK with DTS/2000.
At a guess it seems as though these columns are seen as null by the package and so with the option switched on it defaults to zero - but they are most certainly not null in the file!! The table is a staging tabe and so is very basic in structure (no defaults or constraints)
SSIS seems a bit buggy or at the very least over sensitive to me - I am at the point of abandonment of it!!
Please has anyone seen similar issues?
View 6 Replies
View Related
Aug 28, 2007
I have a simple flat file which I am trying to import which has 50k rows and about 50 columns. File is delimited (using a rather obscure multiple char delimiter) with text delimiting also.
When importing if I check the "Retain null values from the source" it will import the columns correctly but drop approx 7k of the rows.
If I uncheck this box it imports all rows, but on the second integer column in that target table all values are inserted as zeroes when there is at least 35k rows with a positive value.
I have put a data viewer on this import and it's also showing zeroes.
I'm tearing my hair out!!
View 3 Replies
View Related
Aug 3, 2006
I am new to SQL Server and am trying to figure out why it behaves the way it is regarding integers with null values.
I have a table that contains integer datatypes which can be null. If i insert the record with a blank field i receive an INCORRECT SYNTAX NEAR ',' error. If i surround the form value with '', it inserts a 0. Why does SQL Server behave like this? And whats the proper way to handle inserts such as this?
Thanks!
View 8 Replies
View Related
May 11, 2007
Just to be clear i'm using a cube here.
Okay if i understand correct if you want to sort your parameter list you have to write an MDX query.
Thats all good and well i've been able to sort my parameter list when its a string
WITH
MEMBER [Measures].[ParameterCaption] AS '[Time].[Week].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Time].[Week].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Time].[Week].CURRENTMEMBER.LEVEL.ORDINAL'
MEMBER [Measures].[DefaultValue] AS '[Time].[Week].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[DefaultBeginValue] AS ' ( "[Time].[Week].&[" + LEFT([Time].[Week].CURRENTMEMBER.MEMBER_CAPTION,4) + "-01]" ) '
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel],
[Measures].[DefaultValue],
[Measures].[DefaultBeginValue]
}
ON COLUMNS
, {
Filter ((ORDER({(Filter ([Time].[Week].MEMBERS,( [Time].[Week].CURRENTMEMBER.MEMBER_CAPTION ) <> 'Unknown'))},
([Time].[Week].CURRENTMEMBER.UNIQUENAME) , DESC)),
( [Time].[Week].CURRENTMEMBER.LEVEL.ORDINAL ) = 1)
}
ON ROWS FROM [Europe]
Now my problem is that this value of this string is actually an integer. The reason this is a data type string is because its a dimension and these are always string only measures are integer...
Can someone help me make this MDX query sort on integer value instead of string.
[Time].[Week].MEMBERS contains values like 8,11,20 but is declared are string because its a dimension please help me out because i'm getting the feeling this is impossible with this microsoft tool...
View 1 Replies
View Related
Mar 13, 2007
I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00.
example of data:
0001028.99 - needs to be shown as 1028.99
222.00 - needs to be shown as 222.00
0000190.89 - needs to be shown as 190.89
2708.99 - needs to be shown as 2708.99
00000-50.99 - needs to be shown as -50.99
-109.79 - needs to be shown as -109.70
as you can see some of the values have leading zeros and some don't.
i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.
Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.
Thanks
View 4 Replies
View Related
Sep 22, 2014
I am facing problem with re numbering of field records in a table.I am having one table with below records.
sid(identity/primarykey) stickyId(Integer) UserId(integer)
102 0 171
103 1 171
104 2 171
105 3 171
here how to renumbering stickyId values when deleted particular stickyId from UI. Here stickyId field is Integer type only. not primarykey/identity field.
View 3 Replies
View Related
Dec 29, 2014
I'm looking for a script that identifies max values used in a table for all smallints, ints and bigints to determine if they are being used correctly.
View 3 Replies
View Related
Jun 3, 2015
I have SSAS cube with Fact that include values in kg (e.g. 25.3, 32.5, 18,3...).What kind of attribute or other solution should I create If I want to filter those kg's in browser with integer values e.g.:weight between 10 and 25
View 3 Replies
View Related
Dec 26, 2014
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?
View 2 Replies
View Related
Nov 25, 2007
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).
View 4 Replies
View Related
Mar 6, 2015
how best to approach a problem involving two tables across two different servers.
Table 1: Contains IP Address along with assessment findings. Lets say the fields are IPADDRESSSTR, FINDING
Table 2: Contains Subnet information stored in integer format. The fields are SITE_ID, LOW, and HIGH
What I'd like to do is load the IP range information into memory and then return the findings from table 1 where the IPADDRESSSTR is between the LOW and HIGH integer value.
1) Is there a way to load all of the ranges from table 2 into an array and then compare all the IP addresses (IPADDRESSSTR) from table 1?
2) How do I convert IPADDRESSSTR (a string) to an integer to perform the comparison.
View 0 Replies
View Related
Sep 14, 2006
I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?
TIA,
Barkingdog
View 1 Replies
View Related
Oct 5, 2015
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies
View Related
Jul 20, 2015
Working 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.
View 3 Replies
View Related
Apr 29, 2006
Is there any function in SQL Server to convert INT Values to HEX
Ex IF Int Value = 33948700 ,then I shud get HEX Value = 206041C
Thx All for any help
Venu
View 7 Replies
View Related
Aug 10, 2007
I have to convert values like 0x01A4 to integer and I just can not get it right!
Select Convert(int, 0x01A4) works OK but is not useful.
But
Declare @hex varchar(20)
Select Convert(int, @b) yields:
Server: Msg 245, Level 16, State 1, Line 13
Syntax error converting the varchar value '0x01A4' to a column of data type int.
What's the trick??
View 8 Replies
View Related
Aug 5, 2004
I have a column that is frequently updated with an append query. I need to turn all of the incoming positive values into negative values (as well as convert all of the existing). I tried to multiply the column by -1 in the formula option in table design view but couldn't find the proper formula format.
Can anyone suggest an approach.
View 1 Replies
View Related
Apr 15, 2015
I have a columns with datatype varchar(50) which has date values in it.
I am trying to convert the values in the column to a date value,but i am getting conversion error.
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
select last_update from importtest where convert(datetime,last_update)<convert(datetime,'24 Dec 2014')
View 3 Replies
View Related
Oct 31, 2005
Not sure how I can convert varchar to numeric values in SQL Server? Trying to do so in Design and get error:
Unable to modify table.
ODBC error: [Microsoft][ODBC Server Driver][SQL Server] Error converting data type varchar to numeric.
Any suggestions?
View 5 Replies
View Related
