如何从SQL Server中的部分日期和字符串创建新的日期时间？

How would I take the month, append "/01/" to it, then append the year to it, and finally append "00:01 AM" to it. I would like it end up looking like this:

我怎么用这个月,给它添加“/ 01 /”,然后将年份附加到它上面,最后将“00:01 AM”附加到它上面。我希望它最终看起来像这样:

2018/01/01 00:01 AM

I managed to do it in ACCESS but cannot accomplish this in SQL Server. Your help is greatly appreciated!

我设法在ACCESS中执行此操作但无法在SQL Server中完成此操作。非常感谢您的帮助!

3 个解决方案

#1

Using Concat,Cast,Year,Month and right

使用Concat,Cast,Year,Month和right

replace getdate() with your variable/field

用变量/字段替换getdate()

select cast(concat(year(getdate()),right(concat('0',month(getdate())),2),'01',' 00:01 AM') as datetime)

or

select convert(varchar(22),cast(concat(year(getdate()),right(concat('0',month(getdate())),2),'01',' 00:01 AM') as datetime),100)

#2

You can get the desired result with this script:

您可以使用此脚本获得所需的结果:

DECLARE @Date DATETIME;
SET @Date = '2018-01-02 13:45:30.000';
SELECT CAST(YEAR(@Date) AS CHAR(4)) + '/' + CASE WHEN MONTH(@Date) >= 10 THEN '' ELSE '0' END + CAST(MONTH(@Date) AS VARCHAR(2)) + '/01 00:01 AM';

Edit:

You can also create a function and make it reusable:

您还可以创建一个函数并使其可重用:

CREATE FUNCTION dbo.InitializeDate(@Date DATETIME)
RETURNS VARCHAR(19)
AS
BEGIN
    RETURN CAST(YEAR(@Date) AS CHAR(4)) + '/' + CASE WHEN MONTH(@Date) > 10 THEN '' ELSE '0' END + CAST(MONTH(@Date) AS VARCHAR(2)) + '/01 00:01 AM';
END
GO

-- Usage example:
SELECT dbo.InitializeDate('2018-01-02 13:45:30.000') AS MyDate;
GO

-- Output:
2018/01/01 00:01 AM

#3

There are different ways obviously - and always mention the version you are using since that affects the functionality that is available. I will say that it is highly unusual to require a time portion as you describe. I fear you have made an assumption and chosen a path that may turn into a problem. I suggest you read Tibor's discussion of the datetime datatype (and everything else as well). One possible way - which is really to demonstrate the functionality available in steps to get you thinking - is:

显然有不同的方法 - 并且总是提到您正在使用的版本,因为这会影响可用的功能。我会说,如你所描述的那样需要一个时间部分是非常不寻常的。我担心你已经做出了一个假设并选择了一条可能变成问题的道路。我建议你阅读Tibor关于datetime数据类型(以及其他所有内容)的讨论。一种可能的方法 - 实际上是为了让您思考的步骤中展示的功能 - 是:

set nocount on;
declare @x datetime = '20180102 13:45:30.000';
with cte as (select cast('20180102 13:45:30.000' as datetime) as d1 union all
    select '20180101 13:45:01:997' union all
    select '20180228 00:00:05.003' union all
    select '20120229 12:11:30.007') 
select d1, 
datefromparts(year(d1), month(d1), 1),
dateadd(second, 1, cast(datefromparts(year(d1), month(d1), 1) as datetime)) 
from cte;