sql datetime在varchar中转换 [英] sql datetime convert in varchar

问题描述

SELECT dateadd(day, -1, Dateadd(MONTH, 1, CONVERT(Datetime, CONVERT(Varchar(6), GETDATE(), 112) + '01', 112)))

输出正常但是previeas查询错误

this is output ok but previeas query is error

SELECT dateadd(day, -1, Dateadd(MONTH, 1, CONVERT(Datetime, CONVERT(Varchar(10), '2015-12-19', 112) + '01', 112)))

推荐答案

试试这个，

Try this,

SELECT dateadd (day, -1, Dateadd(MONTH, 1, CONVERT(Datetime, CONVERT(Varchar(6), convert(datetime,'2015-12-19',111), 112) + '01', 112)))

如果你想获得一个月的第一天，那么应该使用

If you want to get the first day of a month, you should use

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS ADateTimeValue

这里GETDATE（）可以替换为从表中检索的日期时间字段。



你只如果要在SQL脚本中明确声明特定日期时间，则必须使用CONVERT函数。示例：

Here GETDATE() can be replaced by a datetime field retrieved from a table.

You only have to use CONVERT function when you want to explicitly state a specific datetime in your SQL script. Example:

SELECT DATEADD(month, DATEDIFF(month, 0, CONVERT(datetime, '2015-12-19', 112)), 0) AS ADateTimeValue

但是你永远不应该将日期时间转换为字符串只是为了修剪它并改变日期。那是一种可怕的做法。当您处理日期时间时，您应该坚持使用日期时间类型。

日期时间必须与字符串相关的唯一时刻是与用户交互时，作为输入（就像当你在SQL脚本中指定一个特定的日期时间）或作为输出（当datetime值呈现给最终用户时，不应该将其用于数据层，而应该用于表示层）。



希望这会有所帮助。这是一个你应该早点的重要概念。

But you should never convert a datetime to a string just for the sake of trimming it and changing the day. That's a terrible practice. When you are dealing with datetimes, you should stick to the datetime type itself.
The only moment when a datetime has to be involved with a string is when interacting with user, either as input (like when you specify a specific datetime in your SQL script) or as output (when the datetime value is presented to the end-user, which should not be devoted to the data layer, but to the presentation layer).

Hope this helps. That's an important notion you should get early.

这篇关于sql datetime在varchar中转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！