T-SQL (SS2K8) :: XML Validation - Invalid Simple Type Value DateTime
Jun 23, 2014
I have an issue with validating an XML file I have from a 3rd party.
Willing to improve and standardise company's process, I have created an XML schema collection (SQL 2008R2), but when I validate the XML file against it I always keep getting the above error: Invalid simple type value dateTime.
Definition of dateTime from w3schools website says:
------ Start of quote ----
"DateTime Data Type
The dateTime data type is used to specify a date and a time.
The dateTime is specified in the following form "YYYY-MM-DDThh:mm:ss" where:
YYYY indicates the year
MM indicates the month
DD indicates the day
T indicates the start of the required time section
hh indicates the hour
mm indicates the minute
ss indicates the second
Note: All components are required!
The following is an example of a dateTime declaration in a schema:
<xs:element name="startdate" type="xs:dateTime"/>
An element in your document might look like this:
<startdate>2002-05-30T09:00:00</startdate>
------ End of quote ----
So I would assume that dates like 2012-10-24T14:23:00 or 2012-10-24T14:15:00 should be fine, but no -fails.
It works though only if I add "Z" at the end. Obviously 3rd party do not want to change anything, as it works for ages.
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
if the variable contains any of the character that is not present in the following Below table then it should display "InValid Character" else "valid Character"
DECLARE @MyString VARCHAR(100) SET @MyString = 'ABC$[' select case
Advance thanks ....... My table is TimeSheet:----------------------------------- CREATE TABLE [dbo].[TimeSheet]( [autoid] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [summary] [nvarchar](50) NOT NULL, [description] [nvarchar](50) NULL, [dtOfEntry] [datetime] NOT NULL, [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------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.The statement has been terminated. can any body give any solution
Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards
Hello everyone, I have spent hours trying to figure out the validation issue with SQL Server Reporting Services with no luck.I am hoping someone can help me with the issue. Basically I am looking for a way to validate the data entered by the user in the parameterized Reports. say, the field is defined as int datatype. If user enter String, it should display the message saying string cannot be entered. Currently, My report does not check the user entered datatype, runs the underlying query and displays the error thrown by the DB ( oracle in my case). I get : An error has occured during report processing Cannot read the next data row.... ORA-01722: invalid number
Instead of this, validation even before query is executed would have been perfect. Is there a way to do that?
I want to make a conditional split based on the data type provided by the input. For example : If the comming (Column x) is of data type (numeric) then pass , else do not pass.
I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:
Public Function Validate( ByVal startdate As String) As Boolean If IsDate(startdate) = True Then Return True Else Return False End If End Function
And my report has a textbox which has the expression property set to;
=Code.Validate(Parameters!startdate.Value) the textbox on the report has to display if the entered date is valid or not.
But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.
Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.
Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.
Hi, I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date '31/08/2006 23:28:59' I get the error "...datetime data type resulted in an out-of-range datetime value" I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be? cheers, Ernest
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
How can we validate the input values from user for a datetime type report parameter? i.e. Validating whether the date is correct and in required format.
I know there must be something i'm missing. I'm logged on as sa on the Query Analyzer, and run a simple select statement, or any statement for that matter, and i get an 'INVALID OBJECT NAME' error.
There are certain errors that does not come during compilation and it shows during execution because of late-binding concept implemented in SQL Server from 7.0 version.
The problem is when Tables are not there, SPs are created. And there is no option where we can set like 'Validate object resolution during compilation"
There are lot of SPs that are in invalid status because the tables are really not there and SP needs to be modified to reflect the correct table name. In Oracle, if I have to find the list of objects that are in invalid state (because of object resolution problems), it was possible. How do I do it in SQL?
I need a listing of all objects in my database that is in invalid state. Searched in NET but there seems to be no supporting tool also that lists invalid objects.
Pl let me know whether there exists a way by which I can get to know the invalid object lists in my SQL 2005 database
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!
Kudos to y'all experts out there. I kinda needed your help. I'm trying to run a query...
SELECT a.AUF_POS AS Pos, c.ZL_STR AS Panel, a.POS_TEXT AS Description, a.BREITE AS W1, a.HOEHE
AS H1, a.BREITE2 AS W2, a.HOEHE2 AS H2, SUM(b.ANZ) AS Qty, SUM(b.LIEFER_ANZ) AS Dlvd,
SUM(b.RG_ANZ) AS Inv, (a.BREITE*a.HOEHE/CAST(1000000 AS NUMERIC)) AS UnitSQM,
(a.BREITE*a.HOEHE*SUM(b.ANZ)/CAST(1000000 AS NUMERIC)) as TotPosSQM
FROM liorder..LIORDER.AUF_POS a INNER JOIN liorder..LIORDER.AUF_STAT b ON a.AUF_NR = b.AUF_NR
AND a.AUF_POS = b.AUF_POS INNER JOIN liorder..LIORDER.AUF_TEXT c ON a.AUF_NR = c.AUF_NR AND
b.AUF_POS = c.AUF_POS
WHERE (c.ZL_MOD = 0) AND (b.AUF_NR = '86260')
GROUP BY a.AUF_POS, a.POS_TEXT, a.BREITE, a.BREITE2, a.HOEHE, a.HOEHE2, a.SFORM_NR, c.ZL_STR
...and I keep getting this error: Invalid operator for data type. Operator equals multiply, type equals nvarchar. I've tried every possible CAST and CONVERT but I just can't make it work. I'm pretty sure that the data types for the columns I mentioned on the mathematical equation are all numeric. Please help...
Hi, I got this field (dateSubmitted) having a data type of DateTime but I receive this error "Unable to cast object of type 'System.DateTime' to type 'System.String'." All value for dateSubmitted field are 12/27/2007 12:00:00 AM. cheers,imperialx
DB1 (main database) configured as a distributor and publishers and DB2 configured as a subscriber (backup database). The procedures I did the replication:
1. full backup the whole database said "test" on DB1 server. 2. Create a new database "test" on DB2 server 3. restore the full backed up database to "test" database on DB2 4. create transactional publishes (select all tables) on DB1 and "push" the articles to DB2
The results of replication monitor for replication all database's table: 1. sanpshot suceeded A snapshot of 100 articles was generated. 2. LogReader Idle No replication are available. 3. DB2:TEST Failed Invalid column name "TYPE"
If I only select saying 5 articles (tables) to replication at the same database "TEST". That the result was successful without any error.
Strange... the "TEST" database was restoring from the full backup (latest data) of DB1. Suppose that all the tables' column should be same. How come the error is "Invalid column name TYPE" and number is "207"
By the way, some of "unpublished objects" including tables couldn't be select for publishing which is default locked by a "key with red cross". How can I release such tables (articles) allow selecting for publishing
this is an error i am getting trying to run the query below. Invalid operator for data type. Operator equals minus, type equals varchar
SELECT regardingobjectidname, actualend, owneridname, createdbyname, activitytypecodename FROM (SELECT regardingobjectidname, actualend, Owneridname, createdbyname, activitytypecodename, row_number() OVER (Partition BY regardingobjectid ORDER BY actualend DESC) AS recid FROM FilteredActivityPointer WHERE statecodename = 'completed') AS d WHERE recid = 1 AND (owneridname IN (@user)) AND (activitytypecodename = 'phone call' OR activitytypecodename = 'e-mail' OR activitytypecodename = 'fax') AND (actualend > dateadd(d, -' + CONVERT(nVarChar(20), @NeglectedDays) + ',GetUTCDate() ORDER BY actualend
I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join..I should get as table 2 after updating
CREATE PROCEDURE fts_insert_service_tasks( @status_no int output, @status_text nvarchar(255) output, @fts_employee char(100) , @fts_SCCode bigint, @fts_TaskDescription ntext) AS
declare @str_err nvarchar(255) declare @err_no int
set @err_no=0
if ( isnumeric(@fts_SCCode) = 0 ) begin set @str_err ='The fts Sccode is not a number' set @status_text = @str_err set @err_no=@err_no+1 return end
if ( @fts_SCCode = '' ) begin set @str_err ='The fts Sccode can not be null ' set @status_text = @str_err set @err_no=@err_no+1 return end
if ( len(@fts_employee) > 100) begin set @str_err ='Maximum Employee length allowed is 100 characters' set @status_text = @str_err set @err_no=@err_no+1 return end
if ( @fts_employee = '' ) begin set @str_err ='The employee fiedl can not be null' set @status_text = @str_err set @err_no=@err_no+1 return end
if (@err_no=0) begin
INSERT INTO fts_ServiceTasks (fts_employee , fts_Sccode, fts_taskdescription) VALUES(@fts_employee, @fts_SCCode, @fts_taskdescription)
set @status_no=0 set @status_text = 'Add Service Task Ok' end
else
begin set @status_no=@err_no set @status_text = @str_err end GO
and I called it from the ASP
<%function Add_Service_Task(fts_employee,fts_sccode, fts_TaskDescription) cm.ActiveConnection = m_conn cm.CommandType = 4 cm.CommandText = "fts_insert_service_tasks" cm.Parameters.refresh cm.Parameters(3).Value = fts_employee cm.Parameters(4).Value = fts_sccode cm.Parameters(5).Value = fts_TaskDescription on error resume next cm.Execute if cm.Parameters(1)=0 then exec_command=cm.Parameters(2).Value else call obj_utils.ErrMsg(cm.Parameters(2).Value,3000) Response.End end if if err.number <> 0 then call obj_utils.ErrMsg("System error at " & err.number & err.Description & ", please contact the administrator", 5000) Response.End end if Add_Service_Task=exec_command end function%>
I test with SQL 2k, Win2k3 OK But with Win2k i got:
Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E30) Type name is invalid. /fmits/classes/cls_servicecall.asp, line 256
I have a query however i am getting the following error message “Msg 447, Level 16, State 0, Line 1 Expression type numeric is invalid for COLLATE clause.“
This is my query
SELECT sjo.ID, sjo.MID, sjo.Trade_Association_Name, da1.Account_Name As Trade_Association_Name, substring(do.[MM-CHN-AGENT],2,12) as Mass_Agent_Chain_No,
[Code] ....
And Dan.Stg_Jitter_Opp2 table consists of the following
ColumnNameData Type Idvarchar(50) Mid numeric(18, 0) RecordTypeIDvarchar(50) Trade_Association_Name varchar(50)
And [FDMS].[SalesForce].[DailyAccounts]table consists of the following
ColumnNameData Type Idint Account_Idvarchar(18) account_Name varchar(150) mid_externalvarchar(15) Mid_internalvarchar(15)
I'm having trouble with cube processing. While processing a code I'm getting a "Invalid column name MessageType" error.
I unfolded the cube, then I opened "measure groups", my failing dimension (ServiceRequestDim) and the partition.
In the partition I opened the "Source" attribute so it now includes my column which was missing. But it didn't solve the issue.
If I get the query used during the process I'm getting this :
SELECT DISTINCT [ServiceRequestDim].[MessageType] AS [ServiceRequestDimMessageType0_0] FROM ( Select IsNull(IsDeleted, 0) as IsDeleted, [ServiceRequestDimKey], IsNull([Status_ServiceRequestStatusId], 0) as [Status_ServiceRequestStatusId],[Status],IsNull([TemplateId_ServiceRequestTemplateId], 0) as
[Code] ....
In the nested query which defines ServiceRequestDim the messagetype attribute is still missing. In my source datamart the ServiceRequestDim has the "MessageType" column.
So the question is where do I change the nested request that the dim process use to reflect the actual columns in my datamart .
Hi All... How do you determine the day of the week from a DATETIME variable? I'm actually interested in finding the "weekending" day - saturday after - for any date. If I knew the day of the week, i could just iterate through the days until i get to saturday... Thanks! -- Curt
I found some duplicate data as I was going thru the logic of a data pump. The entire row is not duplicated however.I would like to delete only the one row.
This is a sample of the data: DECLARE @SomeData TABLE ( FirstName varchar(25) , MiddleName varchar(25) , LastName varchar(25) , StreetAddress varchar(25) , Suite varchar(25) , City varchar(25) , [State] varchar(25) , PostalCode varchar(10)
[code]...
As you can see, Joe Smith has two rows, but only one of the rows is complete. I would like to delete only the row that has a NULL value in the phone and area code for Joe Smith. There are a few thousand rows that are like this. They have duplicates all but the area code and phone number.I am used to using a CTE to remove duplicates, but I am a little lost on this one. The things that I have tried, have not worked exactly as I planned.
