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"
View 4 Replies
ADVERTISEMENT
Aug 19, 2007
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.
View 2 Replies
View Related
Feb 21, 2007
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
View 8 Replies
View Related
Jul 16, 2007
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."
View 10 Replies
View Related
Apr 10, 2008
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
and i want to see my files here:
1- DataAccess(put here Stored proceure)public static DataTable GetCalendar(DateTime eventdate)
{SqlDataAdapter DA=new SqlDataAdapter("GetCalendar",createConn());
DA.SelectCommand.CommandType=CommandType.StoredProcedure;
DA.SelectCommand.Parameters.Add("@eventdate",eventdate);DataTable DT=new DataTable();
DA.Fill(DT);DA.SelectCommand.Connection.Close();return DT;
}
2- User Control Page
private void Calendar1_SelectionChanged(object sender, System.EventArgs e)
{
DataTable DT=DataAccess.GetCalendar(Calendar1.SelectedDate);if(DT.Rows.Count == 0)
{DGEvent.Visible = false;
}
else
{DGEvent.Visible = true;
DGEvent.DataSource=DT;
DGEvent.DataBind ();
}
}private void Calendar1_DayRender(object sender, System.Web.UI.WebControls.DayRenderEventArgs e)
{
DataTable DT2=DataAccess.GetCalendar(Calendar1.SelectedDate);
// If the month is CurrentMonthif (! e.Day.IsOtherMonth )
{for(int i=0 ; i< DT2.Rows.Count;i++)
{if ((DT2.Rows[i]["eventdate"].ToString() != DBNull.Value.ToString()))
{
DateTime dtEvent= (DateTime)DT2.Rows[i]["eventdate"];if (dtEvent.Equals(e.Day.Date))
{
e.Cell.BackColor = Color.PaleVioletRed;
}
}
}
}
//If the month is not CurrentMonth then hide the Dates
else
{
e.Cell.Text = "";
}
}
so, i want the exaclty solution for this problem,plz
View 1 Replies
View Related
Apr 17, 2008
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."
View 11 Replies
View Related
Dec 1, 2005
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()
Catch e As Exception
Beep()
MSG.Text = e.ToString
End Try
'Response.Redirect("~/audiobook_services/audiobook_view.aspx")
End Sub
Any help at all would be MUCH appreciated... Thanks in advance.
View 2 Replies
View Related
Jun 27, 2006
Hi,
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.
View 7 Replies
View Related
Feb 5, 2007
I've gote 2 Tables with about 50.000 rows and I left outer join them with MERGE JOIN.
The result are 9999 rows. Has anybody got the same problem. Maybe it's a bug!?
View 2 Replies
View Related
Jan 12, 2008
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.
View 5 Replies
View Related
Sep 13, 2007
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!
View 5 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
Nov 8, 2007
How to formatting numeric value like 20,5 to money value like 20,50 USDusing MS SQL server 2000regards IJKK
View 1 Replies
View Related
Dec 19, 2006
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
View 3 Replies
View Related
May 9, 2014
One purchased app stored user's multiple lines input into a varchar column in which including char(13) and char(10).
My app need to select this value and format to multiple lines text in one text box.
How to code to output it?
View 5 Replies
View Related
Mar 3, 2008
i want to do something like this
i know it does not work this way, i want other way to do this
declare @a int
set @a = 2
select convert(numeric(18,@a), 76.3823)
View 7 Replies
View Related
Jun 20, 2015
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:
But Some values are repeating ....
View 2 Replies
View Related
Jul 21, 2015
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.
View 5 Replies
View Related
Oct 21, 2013
Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,
Also create a new column called “BegMonth” which selects first day of the given month of the converted date column
The table is ;
SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]
Results
FDMSAccountNoINSTALLATION_DATE
87800000088420030521
Required Results
FDMSAccountNoINSTALLATION_DATEBegMonth
8780000008842003-05-212003-05-01
View 3 Replies
View Related
Oct 3, 2006
hi all,
how can i select numeric (int, money, numeric, etc) columns only
from syscolumns.
i need to run a sum() aggregate on all numeric columns
thanks,
joey
View 4 Replies
View Related
Apr 6, 2005
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.
View 4 Replies
View Related
Sep 6, 2007
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
View 10 Replies
View Related
Jun 11, 2008
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);
View 3 Replies
View Related
Oct 24, 2001
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?
Thanks!
View 2 Replies
View Related
Jul 19, 2007
Hi Everyone,
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".
Regards,
A.Akin
View 1 Replies
View Related
Jul 12, 2007
I want to fin out the number of records that are not numeric on my database, how can I fin that using an SQL query?
View 2 Replies
View Related
May 20, 2004
Hello All,
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?
Thanks in advance!!
View 2 Replies
View Related
Apr 6, 2015
Select '"' + CAST( GETDATE() as varchar(100) ) + '"' as Obs_dt_1
I get this output---> "Apr 6 2015 4:07PM"
But what I really need is for it to show in this format---> "2015-04-06 16:08:05.317" .... How do I do the select ?
View 3 Replies
View Related
Jan 4, 2000
I am not sure if this is the right place, but here's my question:
with a field being varchar, can only the rows that are numeric be selected? For example-
ID Data1
1 don
2 jerry
3 3030
4 1234
5 susan
6 4321
Does SQL have an IsNumeric type function that can be used in the where clause?
Don
View 2 Replies
View Related
Sep 5, 2006
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
View 5 Replies
View Related
Dec 15, 2005
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.
View 2 Replies
View Related
Jun 14, 2002
Using the following:
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....
View 4 Replies
View Related
Apr 23, 2008
Hi All,
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.
How can achive this in SQL Server 2005?
Looking for a quick help.
Thanks a million,
Zee
View 4 Replies
View Related
