如何在 SQL Server 2008 中设置 DateTime 变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7188222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set a DateTime variable in SQL Server 2008?
提问by Bob
SQL Server 2008 is not doing what I expected with DateTime. It doesn't let me set DateTimevariables, no matter what date format I use.
SQL Server 2008 使用DateTime. DateTime无论我使用什么日期格式，它都不允许我设置变量。
When I execute:
当我执行：
DECLARE @Test AS DATETIME
SET @Test = 2011-02-15
PRINT @Test
I get an output of:
我得到一个输出：
Jun 18 1905 12:00AM
I've checked all of the regional settings that I can find & it all appears okay. I've also tried setting the DateTimeto various literal alternatives, such as '15/02/2011', '2011-02-15 00:00:00', etc.
我已经检查了我能找到的所有区域设置，而且看起来一切正常。我还尝试将 设置DateTime为各种文字替代方案，例如“15/02/2011”、“2011-02-15 00:00:00”等。
回答by DaveRead
You need to enclose the date time value in quotes:
您需要将日期时间值括在引号中：
DECLARE @Test AS DATETIME
SET @Test = '2011-02-15'
PRINT @Test
回答by marc_s
First of all - use single quotes around your date literals!
首先 - 在日期文字周围使用单引号！
Second of all, I would strongly recommend always using the ISO-8601 date format - this works regardless of what your locale, regional or language settings are on your SQL Server.
其次，我强烈建议始终使用 ISO-8601 日期格式 - 无论 SQL Server 上的区域设置、区域或语言设置如何，这都有效。
The ISO-8601 formatis either:
在ISO-8601格式可以是：
YYYYMMDDfor dates only (e.g.20110825for the 25th of August, 2011)YYYY-MM-DDTHH:MM:SSfor dates and time (e.g.2011-08-25T14:15:00for 25th of AUgust, 14:15/2:15pm in the afternoon)
YYYYMMDD仅适用于日期（例如201108252011 年 8 月 25 日）YYYY-MM-DDTHH:MM:SS日期和时间（例如 8 月2011-08-25T14:15:0025 日，下午 14:15/2:15pm）
回答by Asad Y
Try using Select instead of Print
尝试使用 Select 而不是 Print
DECLARE @Test AS DATETIME
SET @Test = '2011-02-15'
Select @Test
回答by MatBailie
2011-01-15= 2011-16= 1995. This is then being implicitly converted from an integer to a date, giving you the 1995th day, starting from 1st Jan 1900.
2011-01-15= 2011-16= 1995。然后将其从整数隐式转换为日期，为您提供第 1995 天，从 1900 年 1 月 1 日开始。
You need to use SET @test = '2011-02-15'
你需要使用 SET @test = '2011-02-15'
回答by user3755795
Just to explain:
简单解释一下：
2011-02-15is being interpreted literally as a mathematical operation, to which the answer is 1994.
2011-02-15从字面上解释为数学运算，答案是1994。
This, then, is being interpreted as 1994 days since the origin of date (Jan 1st 1900).
因此，这被解释为自日期起源（1900 年 1 月 1 日）以来的 1994 天。
1994 days = 5 years, 6 months, 18 days = June 18th 1905
1994 天 = 5 年 6 个月零 18 天 = 1905 年 6 月 18 日
So, if you don't want to to the calculation each time you want compare a date to a particular value use the standard: Compare the value of the toString()function of date object to the string like this :
因此，如果您不想在每次将日期与特定值进行比较时都进行计算，请使用标准：将toString()日期对象的函数值与这样的字符串进行比较：
set @TEST ='2011-02-05'
回答by Jignesh.Raj
You Should Try This Way :
你应该试试这种方式：
DECLARE @TEST DATE
SET @TEST = '05/09/2013'
PRINT @TEST
回答by Влад Серг?йчук
1. I create new Date() and convert her in String .
2. This string I set in insert.
**Example:** insert into newDate(date_create) VALUES (?)";
...
PreparedStatement ps = con.prepareStatement(CREATE))
ps.setString(1, getData());
ps.executeUpdate();
...}
private String getData() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-M-dd hh:mm:ss");
return sdf.format(new java.util.Date());
}
**It is very important format** = "yyyy-M-dd hh:mm:ss"
回答by Felipe Rodriguez
Check This:
检查这个：
DECLARE
@_month TINYINT = 5,
@_year SMALLINT = 2020,
@date_ref DATETIME = NULL
IF @_year IS NULL
SET @date_ref = GETDATE() - 430
ELSE
BEGIN
SELECT @date_ref = CAST ( CAST ( @_year AS VARCHAR (4))
+
CASE
WHEN @_month < 10 THEN '0' + CAST ( @_month AS VARCHAR(1))
ELSE CAST ( @_month AS VARCHAR(2))
END
+
'01' AS DATETIME )
END
