Formatting A Select Of A Numeric Column As Format -9999.9999
Aug 25, 2006
scott writes "I am relatively new to SQL Server, but after a couple of hours searching help, I'm stuck.
I am trying to select a numeric column from a table and format it using sql. Desired format "-9999.9999" with leading sign and zeros. IS_NEGATIVE is a function to determine sign. The code below works, but seems inefficient.
Is there a better way?
SELECT
RIGHT(dbo.IS_NEGATIVE(ISNULL(COST,0)),1) + RIGHT(REPLICATE('0',10) + RIGHT(abs(CONVERT(decimal(9,5),COST)),10),10) AS 'PRICE'
FROM ORDERS"
i am write an archive program, there are some date before 1/1/1753, but there are a constraint of system.data. How can i fix this. Could i change the minimum data to 1/1/1200. SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
I cannot for the life of me figure out why im still getting this error. I have tried everything i can think of from using dbnull.value to setDOBnull() to datetime.minvalue. Can anyone help me? Here's the code on my BLL: Public Function AddEmployee(ByVal SSN As String, ByVal FirstName As String, ByVal LastName As String, ByVal PreferredName As String, ByVal MaidenName As String, ByVal MaritalStatus As Nullable(Of Char), ByVal DOB As Nullable(Of Date), ByVal Gender As Nullable(Of Char), ByVal Ethnicity As String, ByVal StartDate As Nullable(Of Date), ByVal TerminationDate As Nullable(Of Date)) As Boolean ' Create a new employeeRow instance Dim employees As New HRMS.EmployeesDataTable() Dim employee As HRMS.EmployeesRow = employees.NewEmployeesRow() Dim sqldatenull As SqlDateTime sqldatenull = SqlDateTime.MinValue.Value employee.SSN = SSN employee.FirstName = FirstName employee.LastName = LastName If PreferredName Is Nothing Then employee.SetPreferredNameNull() Else employee.PreferredName = PreferredName If MaidenName Is Nothing Then employee.SetMaidenNameNull() Else employee.MaidenName = MaidenName If Not MaritalStatus.HasValue Then employee.SetMaritalStatusNull() Else employee.MaritalStatus = MaritalStatus.Value If Not DOB.HasValue Then employee.SetDOBNull() Else employee.DOB = DOB.Value If Not Gender.HasValue Then employee.SetGenderNull() Else employee.Gender = Gender.Value If Ethnicity Is Nothing Then employee.SetEthnicityNull() Else employee.Ethnicity = Ethnicity If StartDate = DateTime.MinValue Then employee.StartDate = SqlDateTime.MinValue.Value Else employee.StartDate = StartDate.Value 'If StartDate = DateTime.MinValue Then employee.SetDOBNull() Else employee.StartDate = StartDate.Value 'If Not StartDate.HasValue Then employee.StartDate = sqldatenull Else employee.StartDate = StartDate.Value If TerminationDate = DateTime.MinValue Then employee.TerminationDate = SqlDateTime.MinValue.Value Else employee.TerminationDate = TerminationDate.Value P
Hi there... i have a search page with consitst of DatePicker and DataView this is how i'm binding my dataview <asp:ObjectDataSource runat="server" EnableCaching="false" ID="ods" TypeName="daab.Employee" SelectMethod="GetEmployee"> <SelectParameters> <asp:ControlParameter ControlID="DatePicker1" ConvertEmptyStringToNull="true" PropertyName="SelectedDate" Name="SearchDate" Direction="Input" Type="DateTime" /> </SelectParameters> </asp:ObjectDataSource> .......................................................................................... the searchDate is optional user may select date from datepicker or leave it blank if (!string.IsNullOrEmpty(DatePicker1.SelectedDate)) { // } else { this.ods.SelectParameters["SearchDate"].DefaultValue = DBNull.Value.ToString(); } when i execute the above code i get this error:, what i'm missing? error Message = "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."
hey, i will make calender(by use Asp.net 2003 - C# - framework 2.0 ) that stored and retreive data from Database(SQL Server 2000), that success , but if add this function (Calendar1_DayRender)
Server Error in '/bwarq' Application.
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. 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.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.Source Error:
Line 1999:DA.SelectCommand.Parameters.Add("@eventdate",eventdate); Line 2000:DataTable DT=new DataTable(); Line 2001:DA.Fill(DT); Line 2002:DA.SelectCommand.Connection.Close();return DT; Line 2003:} Source File: c:inetpubwwwroot4paldataaccess.cs Line: 2001
Yes, I've parsed many a topic on many a site regarding this issue.sigh.I am using a calendar control within a TemplateField on a DetailsView.I know that my SQL DB will accept NULL for my DATETIME field, because if I don't bind the control to the insert, the insert succeedes, yeilding a null for DATETIME in the new record.My problem is that I want the user to be able to choose to not select a date. I find that I have to bind selected value to the insert parameter, so that if they do select a date, it get inserted.( duhh!! ) When I do though, and the user does not select a date, I get the above error.My best guess? it is sending a datetime value of 00:00:0000 00:00:00 (or something like that anyhow)?So... does anyone have any ideeas on a simple way to evaluate the value when the form is submitted? and how to set it to NULL if it is, for example "in the past" or "00:00:0000....." ? ,Gary"We truly fail, only when we quit."
Hi. I am getting the error described in the title - SqlDateTime overflow. I have read what is available online but am still not able to get rid of it. Help much appreciated... thanks.
The Full Error Is:
Code:
System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) at System.Data.SqlTypes.SqlDateTime..ctor(DateTime value) at System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.audiobook_viewdetails_aspx.DetailsView1_ItemInserting(Object Src, DetailsViewInsertEventArgs Args) in C:InetpubwwwrootdushkinmediasupportwebPresentationTieraudiobook_servicesaudiobook_viewdetails .aspx:line 151
And, below is the method that is raising the errors. The culprit is "created_date" as "DateTime".
Code:
Sub DetailsView1_ItemInserting(ByVal Src As Object, ByVal Args As DetailsViewInsertEventArgs)
'validate the controls
If Args.Values("ISBN") = "" Then Args.Cancel = True MSG.Text = "-- Missing ISBN number" End If
If Args.Values("author_first") = "" Then Args.Cancel = True MSG.Text = "-- Missing first name" End If
If Args.Values("author_last") = "" Then Args.Cancel = True MSG.Text = "-- Missing last name" End If
If Args.Values("publisher") = "" Then Args.Cancel = True MSG.Text = "-- Missing Publisher" End If
If Args.Values("text_description") = "" Then Args.Cancel = True MSG.Text = "-- Missing item description" End If
If Not IsNumeric(Args.Values("regular_price")) Then Args.Cancel = True MSG.Text = "-- regular price is not numeric" Else If Args.Values("regular_price") < 0 Then Args.Cancel = True MSG.Text = "-- regular price is out of range" End If End If
If Not IsNumeric(Args.Values("discount_price")) Then Args.Cancel = True MSG.Text = "-- Discount price is not numeric" Else If Args.Values("discount_price") < 0 Then Args.Cancel = True MSG.Text = "-- Discount price is out of range" End If End If
'if all the controls are valid, decalre the types Dim audiobookID As Integer Dim title As String Dim author_first As String Dim author_last As String Dim publisher As String Dim ISBN As String Dim regular_price As String Dim discount_price As String Dim text_description As String Dim created_date As DateTime
' ||||| Set up a Connection Object to the SQL DB Dim MyConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("server=ARIA;database=dushkinmedia;Integrated Security=SSPI") ' ||||| Pass in the StoreProcedure or Command String, as well as the Connection object Dim MyCmd As New System.Data.SqlClient.SqlCommand("sp_RegisterNewAudioBook", MyConn) ' ||||| Set the Command Type (Stored Procedure, Text, etc) MyCmd.CommandType = System.Data.CommandType.StoredProcedure ' ||||| Create Parameter Objects for values passed in Dim objParam1, objParam2, objParam3, objParam4, objParam5, objParam6, _ objParam7, objParam8, objParam9, objParam10, objParam11, objParam12, _ objParam13, objParam14, objParam15 As System.Data.SqlClient.SqlParameter ' ||||| Add your parameters to the parameters Collection objParam1 = MyCmd.Parameters.Add("@audiobookID", Data.SqlDbType.Int) objParam2 = MyCmd.Parameters.Add("@title", Data.SqlDbType.VarChar) objParam3 = MyCmd.Parameters.Add("@author_first", Data.SqlDbType.VarChar) objParam4 = MyCmd.Parameters.Add("@author_last", Data.SqlDbType.VarChar) objParam5 = MyCmd.Parameters.Add("@publisher", Data.SqlDbType.VarChar) objParam6 = MyCmd.Parameters.Add("@ISBN", Data.SqlDbType.VarChar) objParam7 = MyCmd.Parameters.Add("@regular_price", Data.SqlDbType.VarChar) objParam8 = MyCmd.Parameters.Add("@discount_price", Data.SqlDbType.VarChar) objParam9 = MyCmd.Parameters.Add("@text_description", Data.SqlDbType.Text) objParam10 = MyCmd.Parameters.Add("@created_date", Data.SqlDbType.DateTime) 'objParam10 = MyCmd.Parameters.AddWithValue("@img_name", imgName) objParam11 = MyCmd.Parameters.AddWithValue("@img_data", System.Data.SqlDbType.Image) 'objParam12 = MyCmd.Parameters.AddWithValue("@img_contenttype", imgcontenttype) 'objParam13 = MyCmd.Parameters.AddWithValue("@aud_name", audName) objParam14 = MyCmd.Parameters.AddWithValue("@aud_data", System.Data.SqlDbType.VarBinary) 'objParam15 = MyCmd.Parameters.AddWithValue("@aud_contenttype", audcontenttype) ' ||||| Set the Parameter values to the passed in values objParam1.Value = audiobookID objParam2.Value = title objParam3.Value = author_first objParam4.Value = author_last objParam5.Value = publisher objParam6.Value = ISBN objParam7.Value = regular_price objParam8.Value = discount_price objParam9.Value = text_description objParam10.Value = created_date 'objParam11.Value = imgBinaryData 'objParam14.Value = audBinaryData
Try
' ||||| Check if Connection to DB is already open, if not, then open a connection ' ||||| DB not already Open...so open it
MyConn.Open() MyCmd.ExecuteNonQuery()
' ||||| Close the Connection Closes with it MyConn.Close()
Is there a way to catch the exeption (SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM) with in SQL Server, so it does not need to be caught in the code calling the procedure. E.g. I do not want to send null to the stored procedure.
e.g. The stored procedure should be able to catch dates that are outside the range 1/1/1975 and 12/31/9999 and change them to the limits.
E.g.
IF @StartDate < CONVERT(DateTime, '01/01/1753 12:00:00')
BEGIN
SET @StartDate = CONVERT(DateTime, '01/01/1753 12:00:00')
END
IF @EndDate > CONVERT(DateTime, '01/01/1753 11:59:59')
BEGIN
SET @EndDate = CONVERT(DateTime, '12/31/9999 11:59:59')
END
This still throws an exception because the StartDate or EndDate when tested fail the condition.
Hi there --I'm writing a SQL query that works something like this:SELECT a, b, a/b AS col_name FROM TABLEDividing a by b can result in a value above or below 1, e.g. 1.234 or 0.123.If the value is below 1, I don't want the preceding zero, e.g. .123.I've been using a STR() function to format it to three decimal points, e.g.:STR(a/b, 5, 3) AS col_name.... but for values below 1 it'll look like 0.123.Any suggestions for how I can make this look like .123?Thanks in advance.
Hi, I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete. My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error: Character to numeric conversion error This report will be used to find students who have failing grades. Thanks for any help!
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.
I am using SQL2005 and ASP.NET 2.0 I have one column in database called DateTime and it is defined like type datetime.It is formated like: day.month.year hour:minutes:seconds My question is: I want to get date from Column DateTime in format day.month.year in SELECT query, not in stored procedure. thanks
Please find below my query and result , how to display [Total Service Time ] in HH:Min format (Currently values in minutes)
Query: SELECT DISTINCT dbo.sectn_dept.sectn_sc AS Customer, MONTH(dbo.incident.date_logged) AS Month_Number, DATENAME(month, dbo.incident.date_logged) AS Month, YEAR(dbo.incident.date_logged) AS Year, dbo.incident.incident_ref PM_ref, dbo.product.product_n "Product",
[Code] ....
Result: Need to Display [Total Service Time] in below Format:
I have to display the data in the below said formats..Current sample Data in the table and the data type is numeric(23,10)
50.00 0.50 0.00 0.00
To be displayed in the below format
1.25 0.75 0 0 1
I have to map this column in teh report and should dipslay like above.I think if 0.00 is available then it should display as 0..If 1.0 is available then it should display 1.Any value that has postive number after the decimal should display all the values example : 2.25,3.75,5.06, So in general the solution to display values like 1.75,1,0 we should not dispaly 0 as 0.00 and 1 as 1.00 and 2 as 2.00 and so on...Any Solutions in terms of SQL query or SSRS expression.
Hi I'm trying to convert and format integer values in a SQL Server select statement to a string representation of the number formated with ,'s (1000000 becomes 1,000,000 for example). I've been looking at CAST and CONVERT and think the answers there somewhere. I just don'tseem to be able to work it out. Anyone out there able to help me please? Thanks,Keith.
Hi all,I've set up a page with a gridview, and I'm trying to create a query based on three parameters and to display all records when the page loads.I have created the first two parameters no problem, but I'm having problems with the last one.The parameter is to be populated by a dropdownlist, where the selectedvalue is numeric. I have done a search and found that I can't use the '%' wildcard as it is for a string data type.I have read somewhere that to get around this I can use the CHAR() function to convert to a character, I have tried this without success.When creating the ControlParameter the datatype gets set to string, which I think is not working becuase the input has to be an integer for it to conver to char, am I right?This is the query, and it is the @Application parameter that I'm having this problem with. SELECT dbo.Issue.IssueID, dbo.Issue.ReportedBy, dbo.Issue.ShortDescription, dbo.Issue.DateReported, dbo.Issue.Status, dbo.Priority.Description AS Priority, dbo.Application.Application FROM dbo.Issue INNER JOIN dbo.Priority ON dbo.Issue.Priority = dbo.Priority.PriorityCode INNER JOIN dbo.Application ON dbo.Issue.Application = dbo.Application.ApplicationID WHERE (dbo.Issue.Status LIKE '%' + @Status) AND (dbo.Issue.AssignedTo = @AssignedTo) AND (CHAR(dbo.Application.ApplicationID) LIKE '%' + @Application) ORDER BY dbo.Priority.PriorityCodeHas anyone managed to do this another way?Thanks
Hi,I have this code following my signature to SELECT a number.I don't know what am I missing, because it always return the number which the variable banID was initialized (32 in this case).The strangest thing is that if I run the statement inside MS SQL SERVER, I get the right result.Any help would be appreciated.Warm Regards,Mário Gamito--// Get inserted user IDint banID = 32 ;SqlConnection myConn = new SqlConnection("Data Source=192.168.1.6; Initial Catalog=db1; User=sa; Password=secret");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlCommand myCommand = new SqlCommand("SELECT MAX(PublisherID) FROM table1", myConn);banID = Convert.ToInt32(myCommand.ExecuteScalar);}catch (Exception e){ Console.WriteLine(e.ToString());}Response.Write(banID);
I have a field with State and Zip (ie; CA94526) which is a varchar field. I have lots of data that is invalid and need SELECT all records that the right(myfield,5) IS NOT Numeric. Can this be done?
I am trying to hide a column in a matrix table. I have no trouble using conditional formatting to control the visibility of the column, but when I hide the column the main row in the matrix does not shrink. Therefore, I have a big gap My thought was to use conditional formatting to control the column width of the main matrix row. Listed below is the expression that I used. Can anyone help me with the error message or recommend a better solution?
=IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) = 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be parsed as a unit because it does not contain numeric values. Examples of valid unit strings are "1pt" and ".5in".
Can someone pls help me with how to check for a numeric value in a varchar column?
For example I have a column called client_id , it has values "AB" , "CD" , "18", "19" . I need to delete those client_id where the values are 18 and 19. How would I do that?
Hi There,I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.AutoRow1234 -------- reset1234 --------resetAny help would be great, many thanksStuart
I am trying to insert some values into a table where the column is of the data type "numeric". The insert works fine.Update does not work. Update BUT_BREAKDOWN_PCT SET BDP_EFFORT_BREAKDOWN_PCT=0.15 WHERE BDP_BREAKDOWN_ID =1 AND BDP_PHASE_ID = 3 AND BDP_START_EFF_DT = '12/31/2004' BDP_EFFORT_BREAKDOWN_PCT is a numeric column with a size 5 (4,3) When I do the updatedirectly from QA, it works fine. I was googling it and read a KB article saying it's a problem with Service Pack of SQL Server 2000. If it is, then the query should not work even from QA....isn't it? Anyone had this problem before? Please help.
select hl, substring(hl,1,patindex('%/%',hl)) as test from appointment
returns
hl test A PCM/RODRIGUEZ A PCM/ Y OPTOMETRY/VISUAL FIELD TESTSY OPTOMETRY/ W DENTAL/DUNDON W DENTAL/ Y LAB Y PCM/NEMES F/U Y PCM/ W NUTRITION/FIRM-A (ROOM E116)W NUTRITION/ W FIRM-A/SILVER/ABBOUD IIW FIRM-A/
I want to be able to remove the first 2 digits and the / to just have the clinic only remaining. Note that Y LAB is not listed in the test column..why? Any help is greatly apprecitated. Thank you....
I have table called Product with seven columns all of nvarchar type. There are 150,000 records in this table. Also there's no unique identifier column in this table. I want to put a column lets say Column1 which start from 1 to 150000. The Column1 should be a unique column.
