Home Calculate the Last day and Hour of the day from Year_Month_Snap String in SQL Server 2014
Reply: 2

Calculate the Last day and Hour of the day from Year_Month_Snap String in SQL Server 2014

user3400309
1#
user3400309 Published in 2017-12-07 04:44:43Z

I am learning SQL Server and am stuck at this conversion issue. Any help is greatly appreciated.

I have a table in SQL Server 2014 with a column Year_Month_Snap which has data in the nvarchar datatype.

I need to obtain the last day and hour of the monthend for 35 consecutive months from 2016 jan.

I see there are many suggestions to snap the fixed day to the YYYY-MM part to make it as YYYY-MM-DD part and convert it to date & datetime, but since the last date of every month varies every month (it may be 30 days month or 31 days or 28/29 days February month) so I need to calculate the last date and hour of the given month from the YYYY-MM nvarchar datatype in the Year_Month_Snap column.

When I try to convert it to the date/datetime, it throws the following error:

Msg 241, Level 16, State 1, Line 25
Conversion failed when converting date and/or time from character string.

Year_Month_Snap column

Any help is greatly appreciated!!

sarslan
2#
sarslan Reply to 2017-12-07 06:10:53Z

You can convert your year and month values to DateTime by adding 01 to it. And add 1 month and subtract 1 hour.

For example for 2017-10 you can obtain 2017-10-31 23:00:00.000 with this.

SELECT DATEADD(HOUR,-1,DATEADD(MONTH,1, CONVERT(DATETIME, ('2017-10' + '-01') )))

your final query.

SELECT DATEADD(HOUR,-1,DATEADD(MONTH,1, CONVERT(DATETIME, (Year_Month_Snap + '-01') ))) as LastDayAndHourOfMonth
FROM MyTable
Alex
3#
Alex Reply to 2017-12-07 08:07:02Z

SQL Server has the function EOMONTH() that gets the last day of each month. Add a day and subtract an hour to get what you need, then stick it in a recursive CTE and off you go :)

DECLARE @StartDate DATETIME = N'20160101';

WITH times AS
(
    SELECT 1 AS MonthId, 
    DATEADD(HOUR, -1, DATEADD(DAY, 1, CONVERT(DATETIME, EOMONTH(@StartDate, 0)))) AS MonthEndTime

    UNION ALL

    SELECT MonthId + 1,
           DATEADD(HOUR, -1, DATEADD(DAY, 1, CONVERT(DATETIME, EOMONTH(MonthEndTime, 1)))) AS MonthEndTime
    FROM times AS t
    WHERE t.MonthId < 35
)

SELECT * FROM times
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.332479 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO