当前位置：文江博客话题详情

使用DateTimeOffset（在C＃和SQL Server上协调的通用时间（UTC）

发布于 2025-02-05 04:44:42 字数 2500 浏览 2 评论 0原文

我正在尝试将UTC与.NET和SQL Server 2019一起使用。

我想获得两个日期之间过期的文件，在.NET上，我正在使用此代码获得UTC：new DateTimeOffset（new DateTime（utcnow.year），utcnow.month，utcnow.day，0，0，0））;

启动“ 12/06/2022 00:00:00:00 +01：00”
EndDate“ EndDate” 12/06/2022 00:00:00 +01：00“

在SQL Server上，我将字段到期日设置为dateTimeOffset（7）。

示例：2020-07-30 23：00：00.0000000 +00：00。

当我尝试执行查询时，我会得到此错误：

microsoft.data.sqlclient.sqlexception（0x80131904）：转换日期和/或从字符字符串转换时间时失败。

生成的查询包含以下行：expirationDate＆gt; = '23/06/2022 00:00:00:00 +01：00''和ExpirationDate＆lt; '29/06/2022 00:00:00 +01：00我得到此错误：

转换日期和/或从字符串转换时间时失败。

如果我将行更改为：expirationDate＆gt; = '06/23/23/2022 00:00:00 +01：00''和ExpirationDate＆lt; '12/29/2022 00:00:00 +01：00，它有效，我得到结果。

总之，如果我将“月”的字段切换为“天”，将“天”转换为“月”。我应该在.NET上做什么将日期正确发送到SQL？

下面，我在.net上显示我使用的代码：

img src =“ https://i.sstatic.net/5yqjk.png” alt =“ enter image Description there there”>

Select Distinct

    [DefaultLanguage],
    [Email],
    [SupplierCompanyId]
FROM
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY SupplierQualification.Id, [File].FileTypeId ORDER BY [File].ExpirationDate DESC) RowNumber,
        [File].ExpirationDate,
        Companies.DefaultLanguage,
        Companies.Email,
        SupplierQualification.CompanyId [SupplierCompanyId]
    FROM
        Reply
    INNER JOIN
        FormReply
    ON  FormReply.Id = Reply.FormReplyId
    INNER JOIN
        SupplierQualification
    ON  SupplierQualification.Id = FormReply.SupplierQualificationId
    INNER JOIN
        AttachmentQuestion
    ON  AttachmentQuestion.QuestionId = Reply.QuestionId
    INNER JOIN
        [File]
    ON  [File].FileTypeId = AttachmentQuestion.FileTypeId AND
        [File].CompanyId = SupplierQualification.CompanyId
    INNER JOIN
        FileType
    ON  FileType.Id = [File].FileTypeId
    INNER JOIN
        Companies
    ON  Companies.CompanyID = SupplierQualification.CompanyId
)   AttachedFile
WHERE
    AttachedFile.RowNumber = 1 AND
ExpirationDate >= '12/06/2022 00:00:00 +01:00' AND ExpirationDate < '13/06/2022 00:00:00 +01:00'ORDER BY 1
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY

database

expirationDate -dateTimeOffsect -dateTimeOffset（7）

原文

I'm trying to use UTC with .NET and SQL Server 2019.

I want to obtain the files expired between two dates, on .net i am using this code to obtain UTC:new DateTimeOffset(new DateTime(utcNow.Year, utcNow.Month, utcNow.Day, 0, 0, 0));

begindate "12/06/2022 00:00:00 +01:00"
endDate"12/06/2022 00:00:00 +01:00"

On SQL Server I have the field expirationDate set to datetimeoffset(7).

example: 2020-07-30 23:00:00.0000000 +00:00.

When I try to execute the query, I get this error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.

The query generated contains this line: ExpirationDate >= '23/06/2022 00:00:00 +01:00' AND ExpirationDate < '29/06/2022 00:00:00 +01:00 and I get this error:

Conversion failed when converting date and/or time from character string.

If I change the line to this: ExpirationDate >= '06/23/2022 00:00:00 +01:00' AND ExpirationDate < '12/29/2022 00:00:00 +01:00, it works and i get results.

In conclusion, if I switch the field "month" to "day" and the field "day" to "month" it works. What should I do on the .NET to send the date correctly to SQL?

Below, I show the code I used on .NET:

Query SQL:

Select Distinct

    [DefaultLanguage],
    [Email],
    [SupplierCompanyId]
FROM
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY SupplierQualification.Id, [File].FileTypeId ORDER BY [File].ExpirationDate DESC) RowNumber,
        [File].ExpirationDate,
        Companies.DefaultLanguage,
        Companies.Email,
        SupplierQualification.CompanyId [SupplierCompanyId]
    FROM
        Reply
    INNER JOIN
        FormReply
    ON  FormReply.Id = Reply.FormReplyId
    INNER JOIN
        SupplierQualification
    ON  SupplierQualification.Id = FormReply.SupplierQualificationId
    INNER JOIN
        AttachmentQuestion
    ON  AttachmentQuestion.QuestionId = Reply.QuestionId
    INNER JOIN
        [File]
    ON  [File].FileTypeId = AttachmentQuestion.FileTypeId AND
        [File].CompanyId = SupplierQualification.CompanyId
    INNER JOIN
        FileType
    ON  FileType.Id = [File].FileTypeId
    INNER JOIN
        Companies
    ON  Companies.CompanyID = SupplierQualification.CompanyId
)   AttachedFile
WHERE
    AttachedFile.RowNumber = 1 AND
ExpirationDate >= '12/06/2022 00:00:00 +01:00' AND ExpirationDate < '13/06/2022 00:00:00 +01:00'ORDER BY 1
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY

Database

expirationDate - datetimeoffset(7)