hello,
I am trying to insert date and time into my table.
insert into <table_name> values('12/12/2006','12:23:04');
but it displays error at " ; "
can anyone help me to figure out the problem
Thanks a lot in advance.
Regards,
Sweety
What happens if you remove ";"?
|||hi,
thx for responding..i figured out the problem and i solved it..
bye
Sweety
I'm trying to format a field value to currency on a SQL report.
I need to allow for 4 decimal places to the right of the decimal if the field value contains those digits. (ie. $6.8484). Most of the time I will not have 4 places to the right of the decimal and would like to format according to the amout of decimal places. For example, If the value is 1 dollar, I don't want to format as $1.0000. I need a way to format the values according to the amount of digits to the right of the decimal. (ie. 1 dollar = $1.00, 3.453 = $3.453, 2.4453 = $2.4453)
Is there an easy way to do this using C2,C3, and C4? Please help.
When you say "SQL report", I suppose you mean you are using SQL Server Reporting Services.
In that case, you can just set the Format property of the textbox where you display the numeric field value to C4.
Note: if the query returns the value as string (instead of a numeric value), you will need to explicitly convert the value into its numeric representation first. You can do this in the query or directly in the report textbox by using the CDbl() function to convert a string to a double, e.g. =CDbl(Fields!SomeString.Value).
-- Robert
|||The C4 format will work for the four decimal place values but how do handle the three and two decimal situations. The report values should not display as $4.4200 if the initial value is $4.42. This also applies to three digit values( $4.421 should not display as $4.4210 on the report).|||I'm not sure if there are any standard functions to determine how many decimal places the value has (if not, you can write a custom function to do it). Suppose we have such a function called GetDecimalPlaces, you can set the format property to =IIF(GetDecimalPlaces(<fieldvalue>) <= 2, C2, GetDecimalPlaces(<fieldValue>) = 3, "C3", "C4").Hi,
I have successfully created my first report. however when i tried to export the report to other format like excel, it doesn't work. and give me some funny codes
======================================
MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related;boundary="--=_NextPart_01C35DB7.4B204430"
This is a multi-part message in MIME format.
--=_NextPart_01C35DB7.4B204430
Content-Type: text/html;
Content-Transfer-Encoding: base64
Content-Location:file:///c:/Report.htm
77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTp2bWwiIHhtbG5zOm89InVybjpzY2hlbWFzLW1pY3Jvc29mdC1jb2[TRIMMED]=
--=_NextPart_01C35DB7.4B204430
Content-Type: text/html
Content-Transfer-Encoding: base64
Content-Location:file:///c:/Sheet1.htm
77u/PGh0bWwgeG1sbnM6dj0idXJuOnNjaGVtYXMtbWljcm9zb2Z0LWNvbTp2bWwiIHhtbG5zOm89InVybjpzY2hlbWFzLW1pY3Jvc29mdC1jb2[TRIMMED]==
--=_NextPart_01C35DB7.4B204430
Content-Type: text/css
Content-Transfer-Encoding: base64
Content-Location:file:///c:/stylesheet.css
77u/Lnhscl8zewpwYWRkaW5nLWJvdHRvbTowcHQ7ZGlyZWN0aW9uOkxUUjt0ZXh0LWRlY29yYXRpb246Tm9uZTtmb250LXNpemU6MTBwdDtjb2xvcjp[TRIMMED]
--=_NextPart_01C35DB7.4B204430--
=====================================================
Anything that i need to set?
______________
Post trimmed by moderator SomeNewKid
Hi l0n3i200n ,
Yup, i used the SRS toolbatr to export to excel, but it doesn't give me the end result.
Another issue that i face is that when i export to pdf, it seems to break the report horizontally to serveral pages, instead of 1 page only.
Is there anywhere i can set this?
Hi
I've had the same problem with PDF files, I read in one of the posts that you should set the Report Margins to the required size, however that didn't work for me. In the end I had to resize the controls to fit on a report(SRS Report Designer Page) set with margins the same as a A4 page. This made things better but didn't solve the problem completely.
|||Thanks l0n3i200n,
I managed to export to excel file after installing service pack 2 for Reporting server.
I guess we have to make our report fit on a A4 page. :(
Hi there,
I have designed a package that works perfectly well, exporting data to an excel file from an ole db source. The problem is that in the excel destination file, columns of data that originally were numbers, are formatted as text. It would be just annoying if it weren't because I use those figures in a pivot table that operates with them.
Any idea on how to tell Excel that those columns are numbers?
Thx in advance
What is the original data type in the OLE DB source of those columns? If they are strings you may need tu use the Data Conversion transformation. Does the excel file already exists? if so, what are the format of the columns?
I run a quick test loading 100 rows od AdventureWorks.Sales.SalesOrderDetail table into an Excel file and all the data types mapped fine. The only thing is that I used the Excel Destination in BIDS to create the file and the destination tab using the 'Name of the excel sheet' -->New... button; it generated an statement like:
CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER,
`SalesOrderDetailID` INTEGER,
`CarrierTrackingNumber` NVARCHAR(25),
`OrderQty` SMALLINT,
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY,
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6),
`rowguid` UNIQUEIDENTIFIER,
`ModifiedDate` DATETIME
)
I only had to adjust the Numeric data type precision and I worked just fine.
Rafael Salas
|||
Thx for your answer Rafael.
The numeric data from the ole db data source is in four-byte signed int [DT_I4]. I have a data conversion between the ole db data source and the excel destination and I've tried to both leave the numeric fields unchanged and "convert" them into the same type. Regarding the excel file, I use a template pre-formatted that i copy into a new file through a system file task in the control flow. I've tried several formatting in the columns (general, numeric, etc...) to no avail...
I've also tried in the excel destination to create the worksheets in which I export the data through that "new button" and with a command very similar to the one you posted, with type for the numeric columns as INTEGER. If it worked for you I'm really confused then :/, I was beginning to think it could be a bug in excel.
Thx again
|||Loslor,
I just looked into the details of mi excel destination file and compared how the data types from BIDS got mapped. I found that all my DT_I4, DT_I2, DT_WSTR and Numeric in BDIS have 'general' when I looked into the format of the cells in the Excel file. I also used the data in the excel file to build a pivot table and worked fine. Are you getting the same behavior?
CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER, --> mapped from an DT_I4 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`SalesOrderDetailID` INTEGER,
`CarrierTrackingNumber` NVARCHAR(25), --> mapped from an DT_WSTR in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`OrderQty` SMALLINT, --> mapped from an DT_I2 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY, --> mapped from an DT_CY in the dataflow; loaded in excel as 'Currency' (when opening the execel file and look a the format of the cell)
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6), --> mapped from a Numeric in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`rowguid` UNIQUEIDENTIFIER,--> mapped from an DT_DBTIMESTAMP in the dataflow; loaded in excel as 'Date' (when opening the execel file and look a the format of the cell)
`ModifiedDate` DATETIME
)
Rafael Salas
|||Hi again,
Yes, my excell is doing the same. After exporting the data, all the cells have the format "General", though they are still treated as text. In fact, the numeric columns display a warning saying that "The number in the cell is formatted as text or preceded by apostrophe" (the last thing obviously being not true) and asks me if i want to reformat the cells, wich sounds a bit like a joke to me. The pivot table is still not getting right the data.
Thx Rafael
|||Then it seems there is an issue in how excel treats the format 'general' in your side. As I told you I was able to generate the Pivot table regardless of the 'general' formating.
Sorry if I didnt hel you
Rafael Salas
|||I'm beginning to fight in that front since I don't see any problem in the package.
Thx a lot for your time Rafael
Hi there,
I have designed a package that works perfectly well, exporting data to an excel file from an ole db source. The problem is that in the excel destination file, columns of data that originally were numbers, are formatted as text. It would be just annoying if it weren't because I use those figures in a pivot table that operates with them.
Any idea on how to tell Excel that those columns are numbers?
Thx in advance
What is the original data type in the OLE DB source of those columns? If they are strings you may need tu use the Data Conversion transformation. Does the excel file already exists? if so, what are the format of the columns?
I run a quick test loading 100 rows od AdventureWorks.Sales.SalesOrderDetail table into an Excel file and all the data types mapped fine. The only thing is that I used the Excel Destination in BIDS to create the file and the destination tab using the 'Name of the excel sheet' -->New... button; it generated an statement like:
CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER,
`SalesOrderDetailID` INTEGER,
`CarrierTrackingNumber` NVARCHAR(25),
`OrderQty` SMALLINT,
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY,
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6),
`rowguid` UNIQUEIDENTIFIER,
`ModifiedDate` DATETIME
)
I only had to adjust the Numeric data type precision and I worked just fine.
Rafael Salas
|||Thx for your answer Rafael.
The numeric data from the ole db data source is in four-byte signed int [DT_I4]. I have a data conversion between the ole db data source and the excel destination and I've tried to both leave the numeric fields unchanged and "convert" them into the same type. Regarding the excel file, I use a template pre-formatted that i copy into a new file through a system file task in the control flow. I've tried several formatting in the columns (general, numeric, etc...) to no avail...
I've also tried in the excel destination to create the worksheets in which I export the data through that "new button" and with a command very similar to the one you posted, with type for the numeric columns as INTEGER. If it worked for you I'm really confused then :/, I was beginning to think it could be a bug in excel.
Thx again
|||Loslor,
I just looked into the details of mi excel destination file and compared how the data types from BIDS got mapped. I found that all my DT_I4, DT_I2, DT_WSTR and Numeric in BDIS have 'general' when I looked into the format of the cells in the Excel file. I also used the data in the excel file to build a pivot table and worked fine. Are you getting the same behavior?
CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER, --> mapped from an DT_I4 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`SalesOrderDetailID` INTEGER,
`CarrierTrackingNumber` NVARCHAR(25), --> mapped from an DT_WSTR in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`OrderQty` SMALLINT, --> mapped from an DT_I2 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY, --> mapped from an DT_CY in the dataflow; loaded in excel as 'Currency' (when opening the execel file and look a the format of the cell)
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6), --> mapped from a Numeric in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`rowguid` UNIQUEIDENTIFIER,--> mapped from an DT_DBTIMESTAMP in the dataflow; loaded in excel as 'Date' (when opening the execel file and look a the format of the cell)
`ModifiedDate` DATETIME
)
Rafael Salas
|||Hi again,
Yes, my excell is doing the same. After exporting the data, all the cells have the format "General", though they are still treated as text. In fact, the numeric columns display a warning saying that "The number in the cell is formatted as text or preceded by apostrophe" (the last thing obviously being not true) and asks me if i want to reformat the cells, wich sounds a bit like a joke to me. The pivot table is still not getting right the data.
Thx Rafael
|||Then it seems there is an issue in how excel treats the format 'general' in your side. As I told you I was able to generate the Pivot table regardless of the 'general' formating.
Sorry if I didnt hel you
Rafael Salas
|||I'm beginning to fight in that front since I don't see any problem in the package.
Thx a lot for your time Rafael
|||I'm having the same problem using Excel 2003 in SSIS 2005 and wonder if you discovered the solution. I'd appreciate any tips you may have. Thanks.
Dan
Hi all,
I have a time column in CHAR(4) format. The contents are stored as 'military time':
1800
1830
2130
I tried using various functions, but was not able to convert to standard time.
I need to take an existing datetime column, strip the time (00:00:00:000), convert the time column to standard and concatonate together to get the following result:
2006-05-31 06:30 PM
Any help would greatly be appreciated.
Thanks.
- gshaf
Your military time conversion wont work because you dont have a colon separating your hours and minutes.
Try this statement:PRINT CONVERT(varchar(20), CONVERT(varchar(20), GetDate(), 101) + CONVERT(smalldatetime, '21:30'), 0)
Try this one instead, this will add a zero to your hour:
IF LEFT(RIGHT(CONVERT(datetime, '21:30', 109), 7), 1) = '1'
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END
ELSE
BEGIN
PRINT LEFT(CONVERT(varchar(20), GetDate(), 20), 10) + ' ' + '0' + LTRIM(RIGHT(CONVERT(datetime, '21:30', 109), 7))
END
Which prints out
2006-05-31 09:30PM
Your time column is going to need that colon. Otherwise your military times will not be output properly.
|||
Worked! Just needed to correct the formatting.
Thanks so much Elliot!
- gshaf
Any help on this ? tnks in advance
Anyone ? It's a very urgent problem, my deadline is approaching and this problem remains. Do i have to replace the points by dot's ? It's the only solution ?
|||I don't know anything about locales, but I guess if I had this problem I'd read the amounts in as strings and then use a Derived Column component to do the necessary string manipulations and data conversion. Hopefully you don't have a lot of them or you have a convenient asynchronous component (like a Union) where you can drop out the string artifacts. Otherwise, you can do the transformation/conversion and drop the artifacts at the same time using an asynchronous script.
|||
What locale have you tried to use? Where did you set it? English (United States) should be available on your machine.
Thanks.
|||First of all tnks for your answers. Well the problem was indeed very simple, i was setting English as the locale and not English (United States), that's what i call a stupid error ;-). Anyway thank you very much for the help