I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible?
I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail.
SELECT rsbirthday CAST(rsbirthday AS smalldatetime)
FROM panelists
the error returned is "incorrect syntax near 'rsbirthday'.
I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing.
I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.
I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.
The code I'm using is:
Dim sqlcommand As New SqlCommand _
("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)
Try
conn.Open()
sqlcommand.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.
The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.
Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
Hi,I would like to convert a dollar amount ($1,500) to represent Fifteenhundred dollars and 00/100 cents only for SQL reporting purposes. Isthis possible and can I incorporate the statement into an existingleft outer join query.Thanks in advance,Gavin
if i save a date value within a smalldatetime field, then later want to edit the record and set the smalldatetime field to nothing, how can i do this. I've tried populating the field with Null, and anything else, without success. Any suggestions?
I am newbie in asp and sql, and I am using VS & SQL expresswhen I try to submit I get following error"Conversion failed when converting character string to smalldatetime data type"Following is my insert statement<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@DropDownList1','@DropDownList2','@DropDownList3','@TextBox6','@DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3','@TextBox1','@TextBox2','@TextBox3','@TextBox4','@TextBox5','@TextBox7')"></asp:SqlDataSource>These are @DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3' defined as datetime in database.I would appriciate if somebody could help.
Hello, I have problem with this code.(This program presents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are 2 time categories in DropDownList - this day, this week. Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error. Conversion failed when converting character string to smalldatetime data type. Here is code: <%@ Page Language="C#" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">
I am running the following query: select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and cast(HQDIS as varchar(8)) >= (Select CONVERT(varchar(8), getdate(), 1)) and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead order by hqvend,fprod The bold sections are giving me the error message. The query works as written above, butis only evaluating on one date, contained in hqeff. However, when I try to modify the second date, HQDIS, by casting it the same way as the one before it: select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and cast(cast(HQDIS as varchar(8)) as smalldatetime) >= (Select CONVERT(varchar(8), getdate(), 1)) and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead order by hqvend,fprod I get the error message. I need to select based on both dates (hqeff AND HQDIS), but cannot seem to be able to it.... Both fields are of type Dedimal, with a length of 8, and dates stored in them look like: 20071003 (YYYYMMDD). Any suggestions?
Hi guys and gals,I have the following code:CREATE PROCEDURE searchRecords( @searchFor NVarchar (25) = NULL, @fromDate NVarchar (25) = NULL, @toDate NVarchar (25) = NULL) AS IF @fromDate IS NOT NULL DECLARE @fromDateString VarChar(200) SET @fromDateString = ' CONVERT(smalldatetime, ''' + @fromDate + ''') ' IF @toDate IS NOT NULL DECLARE @toDateString VarChar(200) SET @toDateString = ' CONVERT(smalldatetime, ''' + @toDate + ''') ' SELECT * FROM MIPR WHERE ID = @searchFor OR DESC_QTY = @searchFor OR REQ_ACTIVITY = @searchFor OR ACC_ACTIVITY = @searchFor OR MANYEARS = @searchFor OR TECH_POC = @searchFor OR RESOURCE_POC = @searchFor OR SI_DATE BETWEEN @fromDateString AND @toDateString;GOit creates an error that I can't seem to fix. the error I get is:Syntax error converting character string to smalldatetime data type.How can I fix this? Thanks in advance!
I have SQL Server 2005 database table with the following data definition as follows:
ID int LST_TIME varchar(5) LST_DATE varchar(21) LST_WEEK int SUBJECT varchar(100)
Date format (Month,day YEAR) eg.- October, 21 2007 Time format, 00 - 23 hours, HH:MM eg. - 18:58
, and a VB code as below:
Dim Command As New SqlClient.SqlCommand("SELECT * FROM dbo.LISTS WHERE (LST_WK = DATEPART(wk, GETDATE())) AND (CONVERT(datetime, LST_DATE) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 110))) AND(CONVERT(smalldatetime, LST_TIME) = CONVERT(smalldatetime, CONVERT(varchar(5), GETDATE(), 108)))", conn)
Dim dataReader As SqlClient.SqlDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection) While dataReader.Read()
Dim NewAlert As New Alert(dataReader("SUBJECT").ToString(), DateTime.Parse(dataReader("LST_DATE").ToString(), DateTime.Parse(dataReader("LST_TIME").ToString())))
: : : I am encountering error: System.Data.SqlClient.SqlException: Conversion failed when converting character string to smalldatetime data type, which points to the codes in bold. Just wondering anyone out there has got a solution to my problem. Thank you.
Recently i traspased a database from sql server 2000 in spanish to sql server 2005 in english. I change the language of the user sa to spanish and the database is in spanish. . The problem is our dateformat is dmy, i try to force with set dateformat dmy, but always i execute a stored procedure that have a date as input it fail. The error is:
Msg 295, Level 16, State 3, Procedure pa_CalendarioGestion, Line 40 Conversion failed when converting character string to smalldatetime data type.
The code of stored procedure in this line is:
set @diaActual = dateadd("d", @i, @fechaInicio)
This stored procedure in sql server 2000 in spanish not have any problems. For this reason i think the problem is in the configuration.
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The ERP manufacturer used an image data type to store large text data fields. I am trying to move these data types from one database to another database using either Sql Queries or MS Access. I can cast them as an 8000 char varchar to read them directly but have no luck importing into these image data fields.
Access and Crystal are not able to read these fields directly.
Any suggestions? Most information about these fields has to do with loading files but I am just moving data.
Hi, I'm using the data type "money" in my SQL database and want to convert what's in txtPrice_textBox to the "money" format. I'm currently using the following code: ' objectCym.price = Convert.ToInt16(txtPrice_textBox.Text) ' Will this work? Is there any reason I should stay away from the "Money" data type?
I have the following SqlDataSource in my page:<asp:SqlDataSource ID="sqlds" runat="server" ConnectionString="<%$ ConnectionStrings:cs %>" SelectCommand="SELECT [Quest1], [Quest2], [Quest3], [Quest4], [Quest5] WHERE ([QuestID] = @QuestID)"> <SelectParameters> <asp:Parameter Name="QuestID" /> </SelectParameters> In my code I get the following error: "Cannot convert type 'int' to 'System.Data.DataView'", I don't understand why I am getting this error because I am casting my SqlDataSource as type DataView when passing it to my DataView type.Code: protected void Chart(string record) { System.Data.DataView dv = (System.Data.DataView)sqlds.SelectParameters.Add("QuestID", System.TypeCode.Int32, record); //Error line ... ... ...}If anyone could point me in the right direction I would be very grateful.
I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.
Error from SQL Debug: --- Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type. ---
Error from page execution: --- Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip") Line 78: Line 79: cmd.ExecuteNonQuery() ---
Below is what I currently have for my stored procedure and the pertinent code from the page itself.
hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
in my SQL database I have the date feild as datetime and the version as nvarchar(max) this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient Imports system.web.configuration
Partial Class Admin_emag_insert Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Record_DateTextBox.Text = DateTime.Now
End Sub
Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click Me.VersionTextBox.Text = "" End Sub
Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click Dim objConnection As SqlConnection Dim objDataCommand As SqlCommand Dim ConnectionString As String Dim record_date As Date Dim version As String Dim emagSQL As String
'save form values in variables record_date = Record_DateTextBox.Text version = VersionTextBox.Text
'Create and open the connection objConnection = New SqlConnection(ConnectionString) objConnection.Open() emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _ "values('" & record_date & "','" & version & "')"
'Create and execute the command objDataCommand = New SqlCommand(emagSQL, objConnection) objDataCommand.ExecuteNonQuery() objConnection.Close()
AddMessage.Text = "A new emagazine was added successfully"
Hi All, In the SQL Server documentation, it is said that, the text data type will contain upto 2GB size. I assumed it will save upto 2GB. But what happened is, it saves only 255 characters all others were truncated. Is there anything wrong with me? Or the wrong is with the SQL Server?
I'm having a bit of a problem with this data type. It is supposed to be able to store about 2,147,483,647 characters, but when I enter more than about 1,834 characters no more charaters can be stored.
Has anyone experienced this and knows how to allow up to the 2,147,483,647 characters. Is it anything to do with another SQL Server setting or variable that needs to be changed
Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht : ..... Do While NOT oInFile.AtEndOfStream oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)Loop ..... After that, I will use a BULK INSERT to input data to ms sql. I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!! I guess it's because the file is not being converted into a correct format??
