2

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!!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3400309
  • 21
  • 3
  • 1
    "i have a table in sql server 2014 with column "Year_Month_Snap" which has data in the nvarchar datatype" - there's your problem. There is a date type for a reason.... – Mitch Wheat Dec 07 '17 at 04:47

2 Answers2

0

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
Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44
0

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
Alex
  • 276
  • 2
  • 7
  • i have tried the EOMONTH() function in this case earlier, but it throws this error Msg 242. Error Message Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. – user3400309 Dec 08 '17 at 05:48
  • Can you link the query you're running? – Alex Dec 12 '17 at 09:56