hi, my Date field in the database formated as 2/15/2006 11:40:46 AM . i try to manually give a date (no) but it give me error. the error come from myReader!. help me to correct, thanks
no = "2152006"
Dim myConn As SqlConnection
Dim myCmd As SqlCommand = New SqlCommand
Dim myReader As SqlDataReader
Dim strSQL As String
myConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
strSQL = "SELECT Author FROM Booktbl WHERE cast(Date as datetime) ='" & no & "'"
myCmd.CommandText = strSQL
myConn.Open()
myCmd.Connection = myConn
myReader = myCmd.ExecuteReader
myReader.Read()
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
Hi Playing around on server at new giggerooo. SELECT CAST('2007-01-28'ASDATETIME)throws:Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.This:SELECT CAST('20070128'ASDATETIME)Works fine. ASFAIK (or recall) former is SQL Standard, latter is ISO. Should I just start using the ever-so-slightly-less-readable YYYYMMDD format or is there a specific setting I should be looking at and perhaps change? SS 2KLanguage: English (United States)Collation: Latin1_General_CI_AI Ta:)
while using following function I am getting this Error "Msg 195, Level 15, State 10, Line 1 'DATE' is not a recognized built-in function name."
select callid, DATE(segstart) AS calldate from October_CLI.dbo.raw Where callid >0 Segstart Column = Data with call date & time Callid Column = Unique Call Id October_CLI.dbo.raw = Table Calldate = Where i want the only date to be capture from segstart column
i am passing date value from a textbox in c# and asp.net. i am calling an sql procedure . The result is binding to a datagrid. my sql procedure is like this
) as DECLARE @SQL varchar(5000) SET @SQL = 'select Customers.CustomerFirstName as Name,ComplaintLog.LogDate,ComplaintLog.LogID, ComplaintLog.ComplaintStatus,ComplaintLog.DueDate,ComplaintCategories.CategoryName from ComplaintLog Join ComplaintCategories on ComplaintLog.CategoryID=ComplaintCategories.CategoryID join Customers on ComplaintLog.CustomerID=Customers.CustomerID where ComplaintLog.IsActive=1'
IF Datalength(@FirstName)>0 SET @SQL = @SQL + ' AND Customers.CustomerFirstName LIKE ''' + @FirstName + '%'''
IF Datalength(@DueDate)>0 SET @SQL = @SQL + ' AND Convert(DateTime(10),ComplaintLog.DueDate,101) = '+@DueDate + ' '
EXEC (@SQL) GO
my error is Syntax error converting datetime from character string.
if i pass nothing in test boxit will show another error
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
I am trying to import a table from Pervasive into SQL 2000 however I am getting an error due to a date column with some dates of 0000-00-00. How can I import the data without errors?
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Here is the About information from . Microsoft SQL Server Management Studio 9.00.1399.00 Microsoft Analysis Services Client Tools 2005.090.1399.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 5.0 6.0 Microsoft Internet Explorer 7.0.5730.13 Microsoft .NET Framework 2.0.50727.1433 Operating System 5.1.2600
I know this is not the best way to use datetime or conversions, but I did not write it, I am just supporting it.
The strange thing is, the above code has worked every week since January 2006?
The even stranger thing is, it runs on perfectly well on Server 2000 and on the version below that has not had any recent service packs installed.
Microsoft SQL Server Management Studio 9.00.3042.00 Microsoft Analysis Services Client Tools 2005.090.3042.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
Is this a bug, or have I lost the plot?
Thanks in advance for any feedback.
P.S: The style code 101, if changed to 103 allows the code to work. But, when apllied to the pre-RTM server, is returns the same error message.
I'm using ODBC (System DSN) to connect to SQL Server 7.0 Database. Whenever I try to connect to SQL Server Database from my client application, I'm getting the following error message -
---------------------------------------------------------- Microsoft SQL Server has reported the following error:
[Microsoft] [ODBC SQL Server Driver] Invalid Date format ----------------------------------------------------------
After this message, I'm getting another error message -
I'm trying to pull a list of users with upcoming or recently passed birthdays. I'm using a CAST to create a birthday of the person using their actual birth month and day, but substituting the current day. This CAST works just fine in the actual query, but fails when I put it in the WHERE clause with the error: Conversion failed when converting date and/or time from character string. I don't see how it can work in one area and not in the other.
Code: SELECT P.EMF_EMPL_ID, CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '/' + CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) AS Birthday, DateDiff(d, getdate(), CAST(CAST(Month(P.EMF_BIRTH_DATE8) AS varchar) + '/' + CAST(Day(P.EMF_BIRTH_DATE8) AS varchar) + '/' +
I try to write a function which includes a statement:
SELECT @dateReturn = CAST(@dateString As datetime)
to convert a string to datetime.
When it runs, sometime it will generate : "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." and all related are terminated.
Can I find a way to catch this error and don't let it terminate the whole thing? For example, when this happens, I want to get datetime as NULL instead of just being terminated.
sql = "update ABC.dbo.member set user_name = '"& c_user_name &"', rec_date = #"& c_rec_date &"# where user_id='"& c_user_id &"'"
conn.Execute sql conn.close set rsinsert=nothing
%>
error :
錯誤類型： Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '上�'.
Hi Folx, I am new to SQL Server and I am struggling with source data for a table with two db_datetime columns. The data can be inserted using native SQL, but errors when I build a Data Flow Container. Versions: Microsoft SQL Server Integration Services Designer Version 9.00.1399.00
Microsoft SQL Server Management Studio 9.00.1399.00
Error: [Flat File Source [1]] Error: The "output column "extraction_date" (24)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "extraction_date" (24)" specifies failure on error. An error occurred on the specified object of the specified component.
How I got here (in approximate order...): Using ETL I created a Container in which I created a PackageCreated a Flat File ConnectionCreated a Flat File SourceEdited the columns on the Flat File Connectiondatabase timestamp [DT_DBTIMESTAMP]NOTE: corresponding columns on destination table in SQL Server Management Studio are of type datetime Created an OLE DB DestinationExecuted the package which returned the above named error.I mucked about with data types, Derived Columns, Data Conversion, etc and Googled for solutions without success. One of the errors (which I could not duplicate...) I noticed during my "experimentation" was an out-of-memory condtion. Questions: 01. What is the proper format for my source data? 02. Could this be a memory issue? If so, how do I diagnose it?
In advance, thanks for your help.
Bill
ps: be kind to me...not only am i an old guy, but i'm a unix guy too...
I have a Foreach loop container that I'm using to loop through a directory and copy files to a new directory. The file name contains the date and I need to compare the value to the current date to determine if the file should moved. I'm having a problem converting the string to a date.
I have a FileName variable of type string that is in this form: 200710221200_FileName_7001.log
I have a FileDate variable of type datetime that I'm trying to use the following expression on: (DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4) +"-"+ SUBSTRING(@[User::FileName] , 5,2) +"-"+SUBSTRING( @[User::FileName] , 7,2))
Which is giving me the following error:
The expression "(DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4) +"-"+ SUBSTRING(@[User::FileName] , 5,2) +"-"+SUBSTRING( @[User::FileName] , 7,2))" has a result type of "DT_DBDATE", which cannot be converted to a supported type.
From the help files, it appears to me that the DT_DBDATE cast uses the form of yyyy-mm-dd. I'm not sure what I'm doing wrong. Any help is greatly appreciated.
I have a web page which executes a stored procedure with several parameters. On execution I get an error for the following piece of code. I will be passing null value to the stroed procedure for this parameter "ActiveDate". Dim parameterActivedate As SqlParameter = objCommand.Parameters.Add("@Activedate", SqlDbType.DateTime) parameterActivedate.Value = "" The error is: System.InvalidCastException: Conversion from string "" to type 'Date' is not valid. at Microsoft.VisualBasic.CompilerServices.Conversions.ToDate(String Value) at webpage.Do_Update(Object Sender, EventArgs e)
I'm trying to put in an SQL server database some data extracted from Oracle Server 9i.
During the load process, the "OLE DB Destination" in the task chokes up when it finds a record containing the date '0197-01-01 00:00:00' (i got this by putting the error output to a column of type varchar(50)). I can't use the Condicional Split's date functions to filter this out because they also choke on the strange date.
I have a RS2005 report that was deployed to a ReportServer and has been running for over 1 month. I personally have not run this report in the past month, however, today when I tried to run it I got an error trying to select a date parameter. I can't paste samples of the windows displayed, so I'll do my best to explain. When I click on the report, it goes to the View window and displays 2 date parameter fields. When I click the icon next to one of the parameter fields, I get an error window that says A Runtime Error has occurred. Do you wish to Debug?
Line: 383 Error: 'resultfield.id' is null or not an object
I click Yes and get a VS Just-in-Time Debugger window, where I choose New instance of VS 2005. When VS2005 opens, then I get this error:
Microsoft JScript runtime error: 'resultfield.id' is null or not an object
The Continue option just keeps displaying this error, when I click Break it shows me the code below and has highlighted the line I show in BOLD:
function ClickDay(date) { var win = document.parentWindow; if (!win) win = document.contentWindow; if (!win) win = document.defaultView; if (!win) return true;
var ifrm=win.frameElement; if (ifrm==null) { return MoveToDate(date, false); } var eltValidator=win.parent.document.getElementById(ifrm.resultfield.id+g_strDatePickerRangeValidatorID); if (eltValidator !=null) { eltValidator.style.display="none"; } var OnSelectDateCallback=ifrm.OnSelectDateCallback; OnSelectDateCallback(ifrm.resultfield, date); var resultfunc=ifrm.resultfunc; resultfunc(ifrm.resultfield); return true; }
To make this more confusing, after I get this error a few times, eventually it lets me choose a date and run the report. Another quirk of my PC is that I often get runtime errors that ask me if I want to Debug when I'm in IExplorer.
Any idea what is going on and how to fix it? Thanks in advance!
I am trying to schedule a report from Report Manager Interface (SSRS 2005) as below:
Scenario 1:
Start Date: GetDate() [today] e.g: 03/07/2008 (mm/dd/yyyy) End Date: 31-dec-2009 Schedule: Monthly (Current Month) €“ Days: [any day in the Current Month which is < getdate()] e.g: March €“ 4th
Scenario 2:
Start Date: GetDate() [today] e.g: 03/07/2008 (mm/dd/yyyy) End Date: 31-dec-2009 Schedule: Monthly (Less than Current Month) €“ Days: [any day] e.g: Feb €“ 12th
Scenario 3:
Start Date: GetDate() [today] e.g: 03/07/2008 (mm/dd/yyyy) End Date: 31-dec-2009 Schedule: Monthly (Grater than Current Month) €“ Days: [any day] e.g: May €“ 10th
In all the above scenarios we observed a strange behavior of SSRS.
Scenario 1: Report is scheduled on 4th April 2008 where as it should be 4th March 2009. Scenario 2: Report is scheduled on 12th Feb 2009 as expected. Scenario 3: Report is scheduled on 10th May 2009 as expected.
Now, we changed the end date (31st Dec 2008) of scenario 1 as below:
Scenario 1:
Start Date: GetDate() [today] e.g: 03/07/2008 (mm/dd/yyyy) End Date: 31-dec-2008 Schedule: Monthly (Current Month) €“ Days: [any day in the Current Month which is < getdate()] e.g: March €“ 4th
The next execution date is again 4th April 2008, which is wrong.
After scheduling the above reports we executed the below query in the ReportServer DB (SQL Prompt)
SELECT S.ScheduleId AS ScheduleId , S.LastRunTime AS LastRunTime , JA.Next_Scheduled_Run_Date AS NextRunTime , RecurrenceType = CASE S.RecurrenceType WHEN 1 THEN 'Once' WHEN 2 THEN 'Hourly' WHEN 3 THEN 'Daily' WHEN 4 THEN 'Weekly' WHEN 5 THEN 'Monthly' WHEN 6 THEN 'Monthly' END FROM ReportServer.dbo.Schedule S WITH (NOLOCK) Inner Join ( select j.name,max(ja.next_scheduled_run_date) as next_scheduled_run_date from MSDB.dbo.SysJobActivity as ja WITH (NOLOCK) inner join MSDB.dbo.SysJobs j WITH (NOLOCK) on ja.Job_ID = j.Job_ID where ja.next_scheduled_run_date is not null and ja.next_scheduled_run_date >= Getdate() group by j.name ) as JA on convert(nvarchar(256),S.ScheduleId) = convert(nvarchar(256),JA.name) Where JA.name = (select top 1 name from msdb.dbo.sysJobs order by Date_Created desc ) -- Where JA.name in (select top 10 name from msdb.dbo.sysJobs order by Date_Created desc )
We have tried the same scenarios using the SSRS APIs. But unfortunately we received the same results.
Did anyone face this type of issue in past? Is this a SSRS BUG or are we missing any point?
hi all i try to get data from sql as select * from datetable where @d between firstdate and lastdate i add parameter @d and the type is datetime when i excute the quiry
An error occurred while reading data from the query result set.Arithmetic overflow error converting expression to data type smalldatetime. how can i fix that plz
I have a select statement which has to convert some date fields stored as varchar based on critera of adding numbers to the date. What I have below will return data show below but gives me an error message when it encounters any data with null in the field. Is there something wrong in my conversion? Thanks
IF EXISTS (SELECT * FROM SYS.COLUMNS WHERE NAME =N'Case_Escalated__c' AND OBJECT_ID = OBJECT_ID(N'[Case]')) begin alter table [Case] alter column Case_Escalated__c bit null print 'Case_Escalated__c altered sucessfully for [Case]' end
Msg 1792, Level 16, State 1, Line 3 Alter table 'Case' failed because the added fixed column might cause existing data to go beyond the maximum allowable table row size of 8060 bytes.
I have a problem with Date/Time parameters. When I try to preview reports locally, I get the following error message for dates past the 12th of a month:Â
An error occured during local report processing. The value provided for the report parameter 'Date' is not valid for its type.
My report is set to de-CH. If I enter a date into the datepicker in Swiss/German format (dd.mm.yyyy) Visual Studio changes them to mm/dd/yyyy but somehow tests them against dd.mm.yyyy and throws an error.
Windows language is German, SQL Server and Visual Studio are in English.
I installed the German language pack for Visual Studio and switched over to German to no avail. Changing the report language to en-US did not work either.
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]
@ClientCode VARCHAR(7) = '',@UserID INT = 0 ,@OrderID INT =0 ,@StartDate datetime,@EndDate datetime
IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE') ,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END
Hi this is my problem. mshflexgrid converts date value from dd.mm.yyyy to yyyy-mm-dd or mm.dd.yyyy
When i use JetOLEDB & .mdb file I can show correctly date type field in mshflexgrid with str() function. For example
rst.open"Select str(MyDateField) from table set mshflexgrid.datasource = rst
i can see dd.mm.yyyy ' namely good when i dont use str() function i see mm.dd.yyyy ' bad version
Now, i'm using SQL server 2000 & mdf file. mshflexgrid don't show correctly date field ( i want it to be like dd.mm.yyyy ) because i cant use str() function with SQL sever 2000 & mdf file
For example rst.open"Select str(MyDateField) from table... occur error When i want to use Convert function in SQL server 2000 & mdf file it automaticly converts it like Jun 18 2004 12:00AM but i dont want this
I want see this 18.06.2004 in Mshflexgrid. What can i do. help me pls.
