I have a character string for a zip code field. I am only looking for zip codes in the 01001 - 02791 range. Will where zip_code between '01001' and '02791' work on a character field? It seems to be dropping out some records but I cant find them.....
View 3 RepliesI have one table that has a column 'amount' with datatype 'numeric(19,5)'
I'm want to insert the data from this column into another column (in a different database) with this format: '00000000.00' as 'char'
example: If in the first column I have 800.75864 I want to insert it in the other column as '00000800.75'
any suggestions on how to make this conversion?
I am looking to be able to add 15 minutes to a time value that is in character format. here is sample data:
0530
0545
0600
0615
0630
0645
0700
0715
0730
0745
Whenever there is a leading zero, I need to preserve that as well. Here is an example of what I an looking for:
0545 + 15 = 0600
0600 + 15 = 0615
1345 + 15 = 1400
In my select statement I concatenate city+state+zipcode. In some cases the zipcode is 9 digits but is missing the '-'. How can I format zipcode to be '#####-####' when > 5 characters from the select statment.
Thanks,
Ken
Hi all,
I am trying to change the negatives to (100,000) instead of -100,000. I am using N0 as my format but I dont want to use currency because I dont want the dollar sign. How can I change this? Is there anyway to use currency without the $ sign?
This can be acheived by writing a format expression for a text box, but i wanted this to be acheived in the bar chart where there is no expression(Fx) for format option(Chart Properties ->Data Tab -> Values ->>Value1 -> Edit tab -> Point Labels -> Format code) in the chart.
Please help me if anyone knows the answer to this issue.
Hello,
I am attempting to modify an existing matrix report into something a bit more diverse and aesthetically appealing to our users. The problem is changing the size of columns, rows, border types, etc.
So far I have not yet been able to locate any custom code samples that refer to ways of changing the overall appearance and properties of the matrix. I am not concerned with the data but in some cases a column's width will need to be larger or smaller based on the size of the data inside it.
The strings can range from 3 characters to 15 or more and the number of columns can be just as dynamic in number. If that happens I need to shrink or expand a column to match.
I am familiar with using custom code just not in using it to access the matrix properties.
Thanks.
I have statement which is comparing a smalldatetime column to literal string as follows:
sales_date ='21-9-2004 0:0:0.000'
when I run the statement in query analyzer it bombs out with:
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.
Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?
Hello,
I am trying to get this to work - but it only returns minutes & seconds:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Can anyone help me out? I am not that familiar with VB.
Thanks,
Deb
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
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?
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
I have created SSRS report which has many overlapping objects, the output in PDF format seems to good but in word format it is not giving the required output.
View 5 Replies View RelatedWe are migrating data from old DB2 systems to sql server 2012, the DATE FORMAT in those systems is in decimal format with 7 digits. CYYMMDD format.
I need to convert this into DD/MM/YYYY format.
I have date coming to one page as a string in the following format"May 4 2005 12:00AM"
I need to query one of my tables using this date in combination of other nondate values. How can I convert this date into valid sql server datetime format before I query a database tables
Please help
I have a table which stores date-of-birth in varchar 19861231(yyyymmdd). A view takes this data. I want to store this date as mmddyyyy in the view. How can we achieve this?
View 18 Replies View RelatedHi,
I have a set of csv files and a set of Format Specification files for each of the csv files. I need to convert the csv files into another format of csv files as specified in the Format Specification files. All the columns of the input csv files do not have a mapping with the columns of the output csv files. How can I achieve this using SSIS ? This is an urgent requirement. Please reply asap. Thanks.
Hello Expert!
I need help to I translate this data...
Table "LeaveDetail"
StaffNo | StartDate | EndDate | LeaveType |
1 | 23/04/2006 | 26/04/2006 | AL |
2 | 24/04/2006 | 25/04/2006 | MC |
3 | 26/04/2006 | 27/04/2006 | EL |
1 | 30/04/2006 | 02/05/2006 | EL |
Into this format...
|Apr|Apr|Apr|Apr|Apr|Apr|Apr|Apr|May|May|May|May|
StaffNo |23 |24 |25 |26 |27 |28 |29 |30 |01 |02 |03 |04..
---------------------------------------------------------
1 |AL |AL |AL |AL | | ... |EL |EL |EL |
2 | |MC |MC | | |
3 | | | |EL |EL |
Parameter:
Date From e.g. 23/04/2006 to 23/05/2006
Using only query statement...
Is this possible??
TIA
Regards.
Hi All,
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Regards,
Kuldeep Chauhan
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
Hi all,
Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?
Also, can custom code function alter the parameters of the report, or refresh the report?
Thanks.
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
View 2 Replies View Relateddear all can anybody help me soon....
i am using visual studio 2005 webapplication based on sql server 2005 database.
i can get one date from sql using one query.
I am selecting my field based on following code CONVERT(varchar, Oman.Positions.Datum, 9) AS LastUpdate
this case my output is May 4 2008 3:19:45:000AM.....
this output is correct but from this output i want to avoid millisecond part.
ie i want the output like May 4 2008 3:19:45 AM....
how i can do this
regards
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 RelatedWhat does it mean char(13) and char(10) when I use this in my sql code
ali
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 RelatedE.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.
View 1 Replies View RelatedHi, 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 RelatedHi,
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
Hi,
Is that true that using CHAR datatype improves the performance comparing to VARCHAR
thanks
indeed.
Would the char or varchar datatype fieldname accept non-english letters like Japanees, Chinees, Russian..etc.
Or should I use nchar or nvarchar?
Thanks
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
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.