我有一个列ENTRY_MONTH,其中的日期作为字符串,如 11/2017。
我正在尝试将列转换为日期时间,最好是每个月的最后一天,因此在上面的示例中将是 11-30-2017。
我试过了
CONVERT(datetime, ENTRY_MONTH, 110)
无济于事。有什么建议吗?
select convert(datetime,right(entrymonth,4) + left(entrymonth,2) + '01')
有很多方法可以做到这一点。
Declare @strDate varchar(10) = '11/2017'
-- concatenate '01/' to get the first date of the month.
-- (it is needed to make a proper date,
-- and not necessary to make it the first date, it can be '17/' as well
Select '01/' + @strDate
Select Convert(DateTime,'01/' + @strDate, 103)
-- the EOMONTH can be used here
Select EOMONTH(Convert(DateTime,'01/' + @strDate, 103))
在您的情况下:
EOMONTH(Convert(DateTime,'01/' + ENTRY_MONTH, 103)
你可以尝试这样的事情:
DECLARE @MyDate varchar(16) = '11/2017'
SELECT DATEADD(d,-1,DATEADD(m,1,CONVERT(datetime, '1/' + @MyDate, 103)))
这使用欧洲格式,其中一天先到位。添加一个月,然后休息一天,将您带到月底。
如果你一步一步地转换和格式化,你可以这样做(中间结果也显示在结果中(:
DECLARE @entryMonth VARCHAR(7) = '11/2017';
SELECT
@entryMonth AS "entry month",
FORMAT( -- create a date from the parts you have in the varchar and a 1 for the day
DATEFROMPARTS(RIGHT(@entryMonth, 4), LEFT(@entryMonth, 2), 1),
'MM-dd-yyyy' -- and format it the way you want
) AS "First of month",
FORMAT(
DATEADD(MONTH,
1, -- add a month in order to get the first of next month
FORMAT(
DATEFROMPARTS(RIGHT(@entryMonth, 4), LEFT(@entryMonth, 2), 1),
'MM-dd-yyyy')
),
'MM-dd-yyyy'
) AS "First day next month",
FORMAT(
DATEADD(DAY,
-1, -- subtract a day from the first of next month
DATEADD(MONTH, 1, FORMAT(
DATEFROMPARTS(RIGHT(@entryMonth, 4), LEFT(@entryMonth, 2), 1),
'MM-dd-yyyy'))
),
'MM-dd-yyyy'
) AS "Last day entry month"
entry month First of month First day next month Last day entry month
11/2017 11-01-2017 12-01-2017 11-30-2017