Int To Char Conversion
Feb 2, 2006
I have data tables that include ZIP code, as char(5). The values look
like integers, but they are padded with leading zeroes to fill out 5
characters, '00234'.
There are SPs to look up data, with @Zip char(5) among the parameters.
Some users call these with integer values, @Zip = 234, and SQL makes
the conversion. Is it necesary to add the leading zeroes in the SP --
@Zip=RIGHT('00000'+@Zip,5) -- or would SQL find this match? (234 =
'00234'). It looks like the conversion is to '234' and the match
fails.
Thanks,
Jim Geissman
Countrywide
View 4 Replies
ADVERTISEMENT
May 8, 2006
Hi,
I'm scrambling to complete a project and ran into an int to char problem. I'm sure, in my haste, I'm doing something stupid. Here is an example of what I'm trying to do but it doesn't convert as expected. I want the SEQ_NBR2 value to convert from 4 to 0000004.
select right('0000000' + convert(char(7), isnull(a.SEQ_NBR2, 0.0)), 7)
fromTXN_HEAD_837 a
where a.file_auth_nbr = '1084472388468'
anda.seq_nbr2 = 4
and a.sgmt_id2 = 'BHT'
Thanks, Jeff
View 5 Replies
View Related
Sep 5, 2002
Hi
Is it possible to have SQL Server recognize the following character field as a date using convert or cast (or anything else):
2001-03-25-02.27.55.365252
so that I can do date comparisons such as
select * from table where [char in above format] < getdate()
View 1 Replies
View Related
Oct 10, 2006
I'm trying to run the following statement. If the first row of invoie data has a number the invoice for the rest of the rows that contain text come through as null. If the first row of data is text i'm fine. How do I convert the invoice to text as it is being read from the below statement?
Set @tsql = 'insert into #Draw_File_Data (Folder, Vendor, Invoice, Dist_Seq, Draw)
select Folder, Vendor, Invoice, [Dist_Seq], [Draw] from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=' + @path + ';'',''select Folder, Vendor, cast(Invoice as varchar(50)), [Dist_Seq], [Draw] from [' + @draw_file + ']'')'
Exec (@tsql)
View 1 Replies
View Related
Apr 16, 2004
Hello,
I am trying to convert a single code page MS Server database into a unicode database, using the unicode data types,NCHAR, NVARCHAR, NTEXT. The problem is that in the original database, indexes and constraints have been defined on the tables whose configurations need to be changed. As a result, the ALTER TABLE command fails. Are there any other alternative solutions?
Also, data from the old database needs to be preserved. The objective is to create a unicode database which keeps the old data intact as well as accepts the new data in unicode.
It would be great if you could help!
Thanks,
Sheetal.
View 11 Replies
View Related
Feb 6, 2007
Hi all,
I need to convert a char (a) to datetime in the following query:
select *
from table1
where convert (a, datetime) > '01/31/2007'
this query does not work :(
View 1 Replies
View Related
Nov 23, 2006
i have another problem.and it's now on converting a char(4) to datetimehere is the situationJ_TIM < F_TIMJ_TIM is datetime while F_TIM is char of 4exampleJ_TIM = 20:30F_TIM = 2030how can i convert F_TIM to datetime so that i can compare them.???thanks
View 3 Replies
View Related
Oct 25, 2000
here is my problem:
i have a variable @sid_x as binary(16) = 0x4CF254AB0BA5D411AA3E00508BC5C413
and i want to use it as argument in sp_addlogin statement.
select @sqlcmd = 'sp_addlogin "test", @sid = ' + @sid_t
/* this doesn't work, because @sid_t is binary... */
select @sqlcmd = 'sp_addlogin "test", @sid = ' + convert (char (20), @sid_x)
/* this doesn't work either, because it doesn't convert to binary text */
my question, is there any way i can get @sid_x in follow text format
0x4CF254AB0BA5D411AA3E00508BC5C413 ?
Thanks a lot!
View 1 Replies
View Related
Dec 3, 2000
Hi All,
I am trying to convert date from a character string field 12 to another database which has smalldatetime. Is there a way to do this. Any help would be greatly appriciated.
cheers
dave
View 4 Replies
View Related
Feb 14, 2006
on a column DateNew = DateTimei am trying :INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')and i get an error :conversion of char data type to datetime data type resulted in an out of range datetime valueI had never this error before , do you know why ?i must enter a yyyy/mm/dd format because this database will be used for Fr and Us langagesthank you for helping
View 14 Replies
View Related
Sep 11, 2006
im trying to convert char (18) data type to decimal (18,6) but it wont let me do it. It gives an arithematic error. what would be other way to solve this problem. Can i do it with float if yes how so? or any other suggested way. Thanks
View 6 Replies
View Related
Jul 12, 2000
I would like to know how to translate a numeric value = 3 to a text or char value = 03.
Any input would be appreciated.
Thanks,
Joel
View 1 Replies
View Related
Aug 31, 2004
I have to import data from Excel file to an SQL Server Database.
One of the Excel Worksheet columns it's number (with max value of 4550204008914630000), I will import the column to a char 21 database field. Using a DTS to do the work, when I import that column it will convert the data in something like 4.5502041E+18.
Can you give me some help for the DTS.
Thanks,
Paulo
View 3 Replies
View Related
Nov 12, 2007
I got this erorr when trying to create my stored proc,
What do i need to fix, and how do i fix it?!!
Msg 457, Level 16, State 1, Procedure PROC_DAILY_ACTIVITY, Line 13
Implicit conversion of char value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
Code Block
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Zaccheus,Tenchy>
-- Create date: <NOVEMEBER,12,2007>
-- Description: <Reporting stored procedure,DAILY ACTIVITY,>
-- =============================================
CREATE PROCEDURE [dbo].[PROC_DAILY_ACTIVITY]
(@Region_Key int=null)
AS
BEGIN
SELECT
Region_Key,
Null as Customer_Code,
Non_Customer_Activities.Question_code,
Non_Customer_Activities.Description,
Region AS Region,
Name AS Territory_Name,
Non_Customer_Activities.Que_Desc AS Store_Name,
Non_Customer_Activities.Logged_Time AS TheDate,
Non_Customer_Activities.response AS Response,
Null as is_Visit_Fg
FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group
INNER JOIN
(Select QH.[question_code]
,Question_Header.Description
,CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) Logged_Time
,SUBSTRING([entity_code],1,5) SR_Code
,[response]
,Territory_Code SR_Territory_Code
,'Not Customer Related' Que_Desc
From question_history QH
INNER JOIN Period P
ON p.period_code = qh.period_code
INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible
ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=SUBSTRING([entity_code],1,5) COLLATE Latin1_General_CI_AS
INNER JOIN dbo.questions Question_Header
ON Question_Header.question_code = QH.question_code
WHERE [entity_code] like '%.USER%'
AND Question_Header.Question_Code IN('AME01','ASE01','ACO01','ALU01','AOS01','APH01','ATR01','ATE01','ACR06','ACR05','ACR02','ACR03','ACR08','ACR07')
AND CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) = '11/9/2007'
) Non_Customer_Activities
ON Sales_Group.Code = Non_Customer_Activities.SR_Territory_Code
UNION ALL
SELECT
Customer_Activities.Customer_Code,
NULL,
NULL,
Region AS Region,
Name AS Territory_Name,
Customer_Activities.Customer_Name AS Store_Name,
Customer_Activities.Logged_Time AS TheDate,
NULL AS Response,
is_Visit_Fg
FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group
INNER JOIN
(Select distinct time_log Logged_Time
,[entity_code] Customer_Code
,[name] Customer_Name
,Territory_Code Cust_Territory_Code
,MAX(is_Visit_Fg) Is_Visit_Fg
From question_history QH
INNER JOIN Period P
ON p.period_code = qh.period_code
INNER JOIN dbo.questions Question_Header
ON Question_Header.question_code = QH.question_code
INNER JOIN [FSSRC].[dbo].[customer]
ON Entity_Code = [customer_code]
INNER JOIN [FSSRC].[dbo].[visit] V
ON V.[customer_code] = QH.[entity_code]
AND V.[period_code] = QH.[period_code]
AND V.[cycle_day] = QH.[cycle_day]
INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible
ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=[sales_person_code] COLLATE Latin1_General_CI_AS
WHERE [entity_code] NOT like '%.USER%'
AND Convert(datetime,convert(Varchar,time_log,110)) = '11/9/2007'
GROUP BY
time_log
,[entity_code]
,[name]
,Territory_Code
) Customer_Activities
ON Sales_Group.Code = Customer_Activities.Cust_Territory_Code
WHERE @Region_Key=Region_Key
order by 4
END
View 2 Replies
View Related
Sep 18, 2007
Hi guys/ladies I'm still having some trouble formatting a select statement correctly.
I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database.
Here is my select statement cut down to the most basic elements.
SELECT commentFROM informix.ipr_stucomWHERE (comment > 70)
The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error
"Character to numeric conversion error"
How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values?
I have tried case with cast and isnumeric... I don't think that I have the formating correct.
I have also used:
WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F'))
This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
View 2 Replies
View Related
Jul 23, 2005
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
May 13, 2008
hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
in my SQL database I have the date feild as datetime and the version as nvarchar(max)
this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient
Imports system.web.configuration
Partial Class Admin_emag_insert
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Record_DateTextBox.Text = DateTime.Now
End Sub
Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click
Me.VersionTextBox.Text = ""
End Sub
Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click
Dim objConnection As SqlConnection
Dim objDataCommand As SqlCommand
Dim ConnectionString As String
Dim record_date As Date
Dim version As String
Dim emagSQL As String
'save form values in variables
record_date = Record_DateTextBox.Text
version = VersionTextBox.Text
ConnectionString = WebConfigurationManager.ConnectionStrings("HRDBConnectionString").ConnectionString
'Create and open the connection
objConnection = New SqlConnection(ConnectionString)
objConnection.Open()
emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _
"values('" & record_date & "','" & version & "')"
'Create and execute the command
objDataCommand = New SqlCommand(emagSQL, objConnection)
objDataCommand.ExecuteNonQuery()
objConnection.Close()
AddMessage.Text = "A new emagazine was added successfully"
Me.VersionTextBox.Text = ""
End Sub
End Class
View 10 Replies
View Related
Jan 28, 2008
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
View 5 Replies
View Related
Jun 19, 2008
Hi,
alter PROCEDURE [dbo].[PPUpdateIWDetails]
(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate datetime
)
AS
BEGIN
By default iam passing 12 char itemid as parameter...
Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN
DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END
Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same)
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)
BEGIN
DECLARE @Qty INT
select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END
View 11 Replies
View Related
Dec 14, 2005
After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.
System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.
Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.
At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.
anyone having this problem before?? hopefully you guys can help.
Thanks,
View 4 Replies
View Related
Apr 19, 2008
Advance thanks ....... My table is TimeSheet:----------------------------------- CREATE TABLE [dbo].[TimeSheet]( [autoid] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [summary] [nvarchar](50) NOT NULL, [description] [nvarchar](50) NULL, [dtOfEntry] [datetime] NOT NULL, [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution
View 5 Replies
View Related
Aug 3, 2005
Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards
View 6 Replies
View Related
Mar 30, 2007
I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.
I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.
The code I'm using is:
Dim sqlcommand As New SqlCommand _
("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)
Try
conn.Open()
sqlcommand.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.
The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.
Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?
View 1 Replies
View Related
Aug 21, 2007
I create two tables:
create table table1
(
col1 char(1)
)
go
create table table2
(
col2 char(2)
)
go
I add some records to two tables after createing operation completed.
Then i use dbcc page command to oversee the structures of data page in two tables.
I found some interest things:
The rows in two tabes take up same space:9 bytes
You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE
or calculate from the offset array
Any suggestions?
View 14 Replies
View Related
Dec 8, 2005
Sql Server uses bit field for boolean while Access uses Y/N. What are the pros and cons of either way? Thanks.
View 14 Replies
View Related
May 4, 2000
What does it mean char(13) and char(10) when I use this in my sql code
ali
View 1 Replies
View Related
Jul 23, 2005
I am importing a table where I need to convert a char(1) with thevalues of 't' or 'f' into a bit field with valies of 1 or 0. Is therea built-in function that does that? I've been searching, but I can'tfind an answer.
View 6 Replies
View Related
Oct 1, 2007
Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result Thanks
View 2 Replies
View Related
Dec 5, 2001
Hi,
Does any body know of any performance implications of using 'varchar' data type against 'char'?
I have some columns that are using 'char' data type, but the data in them is not fixed length. So, to gain some disk space I am planning to change the data type to 'varchar'. But, I am concerned if there will be any performance de-gradation or any other implications of doing this.
Regards
Chakri
View 3 Replies
View Related
Mar 26, 2001
Hi,
Is that true that using CHAR datatype improves the performance comparing to VARCHAR
thanks
indeed.
View 3 Replies
View Related
Nov 2, 2000
Would the char or varchar datatype fieldname accept non-english letters like Japanees, Chinees, Russian..etc.
Or should I use nchar or nvarchar?
Thanks
View 2 Replies
View Related
Oct 27, 2000
Why would you want to use char when you have varchar? Is there any performance hit using a varchar and the size you make that varchar?
Debate going at work.
Phil
View 7 Replies
View Related
Feb 17, 1999
I have recently inherited a database where all of the tables use varchar instead of chars for fields. Very , very few of these fields are involved in keys of even indices, but performance is an issue. I thought that I had read that varchars are worse for performance than chars when page splits may occur. Is this related to updates only, or does it matter?
Any help appreciated.
View 1 Replies
View Related
