Re: xsd:dateTime and sqltypes:datetime validations differ. Any idea why? Please help.
- From: "Mike C#" <xyz@xxxxxxx>
- Date: Thu, 13 Dec 2007 02:10:30 -0500
I would stick with the XSD data types personally. Note also that you can
create your own types. If you wanted to store "date/time" type data without
the time zone you could use an xsd:string type with a pattern restriction
(regular expression). Note tht SQL 2008 offers more flexible date, time,
and date/time support.
Another interesting feature of SQL 2005 xs:dateTime support is that all
date/time values are converted and stored as UTC time (time zone Z). So
setting an xs:dateTime to 2002-10-10T12:00:00-05:00 (EST) actually stores
2002-10-10T17:00:00Z. I have no idea how, or if, they compensate for
Daylight Savings Time (not all governments recognize it) in this conversion.
This appears to have changed in SQL 2008, and the date/time is stored with
the time zone you specify originally.
"Farmer" <someone@xxxxxxxxxxxxx> wrote in message
news:AAB13367-04EF-4095-92E1-E2D41660B626@xxxxxxxxxxxxxxxx
Thank you, Mike!
Very good explanation, even though the outcome sucks.
I am quite new to schemas but I have been mandated to make one, or more
like few;
I know it's bad combination but I am quite SQL guru with a lot of
experience, so it's good experience and I understand data well.
In your opinion, should I use MS derivative data type validation ?
<xsd:attribute name="PaymentCode">
<xsd:simpleType>
<xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033"
sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth"
sqltypes:sqlSortId="52">
<xsd:maxLength value="20" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
versus (I hope I modified it right.)
<xsd:attribute name="PaymentCode">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="20" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
thanks
farmer
"Mike C#" <xyz@xxxxxxx> wrote in message
news:eDXHDQSPIHA.5264@xxxxxxxxxxxxxxxxxxxxxxx
From MSDN:
"In SQL Server 2005, all types derived from xs:date, xs:time, and
xs:dateTime are required to have time zones. Sqltypes:datetime and
sqltypes:smalldatetime are two of these types. However, the SQL datetime
and smalldatetime types do not have time zones. This is because the
pattern facets for sqltypes:datetime and sqltypes:smalldatetime do not
allow for time zones. As a result, SQL Server does not accept
sqltypes:datetime or sql:smalldatetime values. Although you can reference
sqltypes:datetime and sqltypes:smalldatetime in user-defined schemas, you
cannot validate XML documents that contain values of these types. This
makes them unusable."
http://msdn2.microsoft.com/en-us/library/ms190665.aspx
"Farmer" <someone@xxxxxxxxxxxxx> wrote in message
news:0F154946-A80C-4990-9E4C-076A36F5F735@xxxxxxxxxxxxxxxx
According to
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=167649
dates(datetimes) need to have Z specified when schema validation is
desired.
Using xsd:dateTime, it works.
Why no matter what I try, I cna't make SQL smalldatetime or datetime
derivaties to work?
Could someone have any idea?
thank you for your time.
farmer
IF EXISTS
(
SELECT *
FROM sys.xml_schema_collections c
JOIN sys.schemas s ON s.schema_id = c.schema_id
WHERE s.[name] = N'dbo'
AND c.[name] = N'TimeTest'
)
DROP XML SCHEMA COLLECTION TimeTest
GO
CREATE XML SCHEMA COLLECTION TimeTest
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes";
targetNamespace="TimeTest">
<xsd:import
namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes";
schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd";
/>
<xsd:element name="date" type="xsd:dateTime"/>
<xsd:element name="datesql" type="sqltypes:datetime"/>
</xsd:schema>'
go
-- works as expected
DECLARE @x xml(TimeTest)
SET @x='<date xmlns="TimeTest">2002-10-10T12:00:00Z</date>'
-- does not work
DECLARE @x2 xml(TimeTest)
--SET @x2='<datesql xmlns="TimeTest">9999-12-31T23:59:59Z</datesql>'
SET @x2='<datesql xmlns="TimeTest">9999-12-31T23:59:59.997Z</datesql>'
.
- References:
- Prev by Date: Re: xsd:dateTime and sqltypes:datetime validations differ. Any idea why? Please help.
- Next by Date: Help with SQL Mapping
- Previous by thread: Re: xsd:dateTime and sqltypes:datetime validations differ. Any idea why? Please help.
- Next by thread: Help with SQL Mapping
- Index(es):
Relevant Pages
|
