To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.
2006 01
2006 02
2006 03
Etc……
Status of Problem:
The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:
20061
200610
200611
200612
20062
Etc….
This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says
Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?
So if I enter Year: 2007 Period 3
I should get:
2006 01
2006 02
2006 03
Etc…….
2006 11
2006 12
2007 01
2007 02
2007 03
Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.
I want to generate a resultset that is just a series of numbers inascending order or perhaps a series of dates.. What I mean is, isthere a way to generate a temporary table of dates given an input of astart date and an end date.. This table would then contain an entryfor each date in ascending order from the start date to and includingthe end date..Or perhaps with numbers, given a start of 5 and and end of 7the resulting table would be567Would appreciate any help with this one.. ThanksChris
Hello Forum, I am have posted questions regarding selecting a series of dates and am still having trouble doing so. I have been using vwd to build a website, and now would like to retrieve data from access and display a weeks worth of information at one time from selecting a column of information from the data base. I have used this section of code to do the required function. 'SELECT OrderId, CustomerId, Price FROM Orders WHERE OrderedDate BETWEEN DATEADD(d, -7, GETDATE()) AND GETDATE(), I have replaced these variables with my own, and keep getting an error message 'Undefined function 'GETDATE' in expression', I have tried many different functions to get it to work. Any ideas? Thanks Forum, Tim p.s. thank you todd for replying to my first question!
My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).
So my question is, what is the best way to do this? This is what I have so far:
SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar) + Cast(Datepart(mm,@MacolaDate) as varchar) + Cast(Datepart(dd,@MacolaDate) as varchar)
However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.
Any suggestions would be greatly apprectiated. Thank you.
Hello Nice simple T-SQL question that will no doubt prove totally uncontroversial :) Anyone know the easiest way to get from:123456789to123,456,789in T-SQL? Currently I cast my int as money, use convert to varchar with style 1 and then lop off the .00. Is there an easier way? Two things before you get all upset and start squealing about front ends:1) These are admin functions that I am running from SSMS. I don't want to go to the bother of creating an application when SSMS is perfectly adequate for what I need (namely to-a-large-degree unformatted result sets).2) I'm not too fussed about it just curious. Ta & tra la!
Hi all i'm having an issues when i trying to do a report:
i make a report and there is a field which contains data that can be numeric and numeric with letter: "77756" or "345WS" when i export my report to excel i and i open it i get like a make in the upper left coner of the cells that have only numbers asking me to convert this to numeric format, but this think is that i need this values to be string so i can apply filtering. is there a way that i can format my cell to string so the excel take it like that and allowme to do the autoifilter.
I would like to know if there is any trick to convert numbers to csv files. I don't have any control to format the numbers.
Source: Sql server
for example:
select cast( 1 as float)/ cast(201 as float)
Destination:CSV file
the output of the file is 4,9751243E-3
i don't want to have scientific notation on the number. I want the result to be 0,00497512437810945. how can i do it?
I tried to change the type of destination columns to String or numeric but nothing works. what is the influence of changing data type in the destination columns e the output to files?
I tried using convert but with the same results.
Can it be because of local settings or something else?
Program Descritpion: Our product allows our customers to enter their product catalogs into our ASP.NET pages and we save the data using SQL Server 2005. Customer use GridViews to edit their data and teh data is broken into manageable groupings (called Field Groups) stored as meta-data in SQL Server Tables. Then when a user wants to edit a set of data they choose the Field Group and we dynamically generate a data source and a gridview, bind them together, and our users update their data. A bit more complicated but that's teh gist. Another thing they can do, and where our problem occurs, is they can print reports or do exports into excel using this data. Since there are so many fields, they use the Field Groups to select which fields they want included so everything needs to be built dynamcially, at runtime, using the Field Group meta-data. Essentially, I want my grid to have these 10 fields. Problem:For the export we generate an XML data source using FOR XML in SQL Sprocs. The xml is pulled into a xmldatadocument, trasnformed with an xsl file into an Excel XML Spreadsheet. Our problem is that FOR XML is generating our XML real numbers in scientific format. The xsl "format-number" function does not recognize scientific format and returns NaN (not a number) instead of the value in my spreadsheet. If I leave it blank I get the scientific number but Excel doesn't format it correctly, the cell has an error tag that wants me to choose Number stored as text or convert to a number. I need it to show up on teh Excel form already formatted without that message. I can't change the DataType of the field in SQL, too many other things depend on it and it needs to be a real. I can't use CONVERT(decimal,fieldName) in SQL because the SQL string is dynmically generated using Dynamic SQL and most of the fields are not real. When we build the Field List we just have field names, we can't check anything to add CONVERT functions to only real fields. Is there any way to force the XML output to not be scientific for the entire document? Or another function in XSL I'm unaware of (pretty new to xsl)? Or perhaps something I can do in the XML Spreadsheet tags to force the conversion?
/* Format strings for fractional Numbers and Currency values
Return Values: VARCHAR Parameters: @n Specifies numeric expression to format. @sFormat: Specifies one or more format codes that determine how the expression is formatted.
The following table lists the available format codes.
Hello, I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-
The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.
I have table with 3 columns: record_id (int), car (varchar(50)), amount (decimal(18,4)).
I created dimension for drillthrough action. Also I set Amount's column FormatString to '##0.00' but when I use Drillthough column Amount values is shown like:
.0000 15.7900 18.4100
Is there any possibility to change that values would be shown as follow:
I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.
Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.
Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?
I don't know why my company did this, but dates are being stored in a char field within our database. Meanwhile, I've been setting up new pages using datetime. That's just a little back story. My question is, is it possible to change the format of all dates in the table from yyyy/MM/dd to MM/dd/yyyy in the char field? I'm just trying to think of an easier way to change a thousand or so records instead of doing it manually. Thanks.
I'm totally new at SQL Server, I'm sorry if this question looks very simple, but I just don't know how to change the datetime value in a field on my database.
I'm working in vb.net and I have a datetime field in sql server, I can store date values passing this value:
DatePick.Value.ToString("s")
dates are saved fine, but if want to make this query:
Select * from backlog where date1>=isdate('21/02/2006') order by date1 asc
I get all the values from that field, no filter is done. Date are shown like this 2004-05-23 14:25:10.000 in the query.
What do I have to do ? do I have to change the date format of my field to ISO 8601 ? how ? do I use a script or what ???????
Please remeber that my sql script should be done from vb.
hi Experts,what is the purpose writing a date in the following format:where x.event_date >= {ts '1980-01-01 00:00:00'}versus like this:where x.event_date >= '1980-01-01 00:00:00'what benifit does it add to later form of writing?Thanks in advance.schal.
ok so this is my table structure [odcCode] [int] NOT NULL , [insCode] [varchar] (11) COLLATE Modern_Spanish_CI_AS NOT NULL , [odcQty] [int] NOT NULL , [odcCostoUnitario] [numeric](18, 4) NOT NULL , [odcISV] [bit] NULL , [odcStatusInsumo] [varchar] (3) COLLATE Modern_Spanish_CI_AS NULL , [odcInsArrivalDate] [datetime]
and when I run, the insert command I get the next error: Syntax error converting datetime from character string.
The date is taken from a standar DateTime Picker control from Visual Studio 2003 and I can't change the regional settings on the computers (that's not an option)
What can I do to fix the problem and be able to insert the date in any format?
I have a need to display the difference between two dates, a start date and a end date in the format HH:mm where the hours could be greater than 60.
For example:
Start Date - 30/01/2007 09:00:01 End Date - 01/02/2007 20:40:04
When i use the following code (=Fields!dateend.Value - Fields!DateStart.Value) i get 2.11:40:03 which i can easily understand, but the customer wants it as above!
I'm currently having problems exporting formatted dates from reporting services 2005 to excel.
Basically what I require is a way to format a date in reporting services so that it only shows the date without the time (preferably british format) and when it is exported to excel it is still formatted as a date.
This is so the user can sort the data file via date, I appreciate it is easy to select the column and format the cells but i would prefer to have a 'cleaner' solution to this problem which avoids the need for users to be formatting exported reports.
Originally I was formatting the dates as convert(varchar,@date,103) in the SP which converts it to a character string and excel picks this up as a character as would be expected. So I changed this to a date and set about trying to format the date in Reporting Services, so far i've been unsuccesfull using cdate (brings back the time) and format as it again converts it to a character string.
I am having the "classical problem" of the forums; A local date changed to the american format (normally in the development environment) which I call "Switching months with days"... well it switches both again if you press "view report" again :)The problem I'm having is that when I Navigate from one report to other, the dates get switched... Everything's been set in the locale uk date format (dd/mm/aaaa), in the operating system, in the database and in the reports... and the reports are working properly through the web interface.. the only thing that is not working properly is the Navigation which switches the date month with the day...Changing all the Reporting Services server, database server to the american format date is not an acceptable solution. Thanks for any guidance on this, as I'm pretty lost...Jose
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
I encountered a very strange problem again. Why the time series displayed on the chart are so strange? The Key time column I chose for my time series algorithm is cal_month(e.g 199001...), but why the date displayed on the time series chart is like :05/06/2448? (it should be like 199001..?) What is that data? And where exactly did it come from? What is the exact cause of this?
Hope it is clear for your help.
I am really confused on this and thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.
