SQL Server中的YYYY-MM格式

Question

我用来创建SQL数据库的数据集的数据点格式为YYYY-MM，但据我所知，没有SQL的格式来专门存储它。我正在使用Python和pyodbc创建一个脚本来构建并更新这个数据库

SQL中有没有我缺少的这种数据类型的解决方案，或者使用Pandas或Python中的工具来解决这个问题？

Answer 1

如果您不能修复python源以输出日期而不是部分日期，我们可以用不同的方式处理。

假设您的真实表格如下：

CREATE TABLE dbo.PythonInput
(
SomeKey int CONSTRAINT PK_pi PRIMARY KEY,
EventDate date
);

创建一个模拟它的暂存表，只是日期列实际上是一个字符串：

CREATE TABLE dbo.Staging_PythonInput
(
SomeKey int CONSTRAINT PK_st_pi PRIMARY KEY,
EventDate char(7)
);

现在，您可以有一个触发器，只需在按摩日期列后将数据移动到真实的表中：

CREATE TRIGGER dbo.CorrectBadPythonInput
ON dbo.Staging_PythonInput
INSTEAD OF INSERT
AS
INSERT dbo.PythonInput
(
SomeKey, 
EventDate
)
SELECT 
i.SomeKey, 
TRY_CONVERT(date, i.EventDate + '-01', 120)
FROM inserted AS i;

然后告诉Python将其插入到您的暂存表中，例如：

INSERT dbo.Staging_PythonInput(SomeKey, EventDate)
VALUES(1, '2022-07'), (2, '2022-03'), 
(3, '2019-05'), (4, '0123-67');

现在对源表进行查询：

SELECT SomeKey, EventDate FROM dbo.PythonInput;

收益率：

事件日期12022-07-012022-03-0132019-05-014

Answer 2

MS SQL Server支持XML数据类型、XQuery、XPath和XSD。

XML有49种数据类型。其中之一是CCD_ 1。

它保存以下格式的数据：yyyy-MM。这正是我们所需要的。

首先，我们需要创建一个UDF函数。否则，我们将得到以下错误：

Xml data type methods are not supported in check constraints. Create a scalar user-defined function to wrap the method invocation. The error occurred at table "".

之后，我们将在常规检查约束中使用该UDF。

要运行阴性测试，只需取消对INSERT语句中注释行的注释即可。

SQL

USE tempdb;
GO
/*
DROP FUNCTION IF EXISTS dbo.CheckYearMonthXML;
GO
CREATE FUNCTION dbo.CheckYearMonthXML(
@yearMonth AS CHAR(7)
)
RETURNS BIT
AS
BEGIN
DECLARE @IsValid AS BIT

IF TRY_CAST('<r><![CDATA[' + @yearMonth + ']]></r>' AS XML)
.value('(/r/text())[1] cast as xs:gYearMonth?','CHAR(7)') IS NOT NULL
SET @IsValid = 1
ELSE
SET @IsValid = 0

RETURN @IsValid
END
GO
-- test
DECLARE @var CHAR(7) = '2022-12';
SELECT dbo.CheckYearMonthXML(@var);
*/
DROP TABLE IF EXISTS dbo.tbl;
GO
CREATE TABLE dbo.tbl (
ID INT IDENTITY PRIMARY KEY, 
yearMonth CHAR(7),
CONSTRAINT CK_tbl_yearMonth CHECK (dbo.CheckYearMonthXML(yearMonth) = 1)
);
INSERT INTO dbo.tbl (yearMonth) VALUES
('2022-12'),
--('2022-22'),
('2022-08');
SELECT * FROM dbo.tbl;