如何从SQL中的datetime获取日期 [英] How do I get date from datetime in SQL

问题描述

我在xml中有一个日期符号. 2016-04-25T00:00:00+05:30
但是，当我使用sql将其插入表中时，它给了我日期，即一天. 2016-04-24

那么我该如何在sql中进行管理呢?

我尝试过的事情:

我已经尝试过sysdatetimeoffset和datetime的转换，但对我不起作用

hi i have a dateime in xml eg. 2016-04-25T00:00:00+05:30
but when i am inserting it into table using sql it gives me date one day before ie. 2016-04-24

so how can i manage that in sql?

What I have tried:

i have tried sysdatetimeoffset and converision of datetime but its not working for me

推荐答案

我在SQL中创建了一个简单的标量函数，该函数将日期作为字符串并将其限制到23个字符.如果字符串不为null或为空，则使用CONVERT转换为datetime.然后，到处都有日期，我都会将此函数包装起来.

示例:

I created a simple scalar function in SQL that takes in the date as a string and restricts it to 23 characters. Then a CONVERT to datetime is used if the string is not null or empty. Then everywhere I have a date coming in I wrap this function around it.

Example:

SELECT [dbo].GetFormattedDate('2013-07-01T14:27:00.434')
,[dbo].GetFormattedDate('')
,[dbo].GetFormattedDate(NULL)
,[dbo].GetFormattedDate('2013-07-01T14:27:00.434Z')
,[dbo].GetFormattedDate('2013-07-01T14:27:00.434445Z')
,[dbo].GetFormattedDate('2013-07-01')

功能代码:

Function Code:

CREATE FUNCTION [dbo].[GetFormattedDate] 
(  
    @p_DateString varchar(23)  
)
RETURNS datetime 
AS
BEGIN  

/* 
Notice that the in parameter truncates all datetime values to 23 characters
Which would truncate the date time to the thousandths place of the milliseconds
*/
DECLARE @returnDatetime as datetime;

IF ((@p_DateString IS NULL) OR (@p_DateString = '')) 
    BEGIN
        /* Return null is string is empty or null already */
        SET @returnDatetime = NULL;
    END
ELSE
    BEGIN
        /* Otherwise conver the string to a datetime */
        SET @returnDatetime = CONVERT(datetime, @p_DateString);
    END

RETURN @returnDatetime;

END

这篇关于如何从SQL中的datetime获取日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！