Deriving value of 1 column from another column in SQL-CodePudding

I have a table with the following structure. I want to add one more column Month End in a SQL Server database such that Month End = the Last date of the month in DD-MMM format. can you suggest a query for this operation?

ID | Month 
--- -------
0  | Mar
1  | July     
2  | Jun        
3  | Aug     
.
.
.so on

CodePudding user response：

Please try this: select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE()) 1, 0))

This will give you end date of respected month of date.

Hope this may help you.

CodePudding user response：

your data

CREATE TABLE test(
   ID    INTEGER  NOT NULL 
  ,Month VARCHAR(40) NOT NULL
);
INSERT INTO test
(ID,Month) VALUES 
(0,'Mar'),
(1,'July'),
(2,'Jun'),
(3,'Aug');

since month column type is not clearly indicated as

Select name, alias, months, shortmonths
from   sys.syslanguages
where name='us_english'

name	alias	months	shortmonths
us_english	English	January,February,March,April,May,June,July,August,September,October,November,December	Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

and change it into

shortmonth	fullName
Jan	January
Feb	February
Mar	March
Apr	April
May	May
Jun	June
Jul	July
Aug	August
Sep	September
Oct	October
Nov	November
Dec	December

by using string_split, row_number

SELECT shortmonth,
       fullname
FROM   (SELECT NAME,
               alias,
               months,
               shortmonths,
               a.value                     AS shortmonth,
               Row_number()
                 OVER (
                   ORDER BY (SELECT NULL)) rn
        FROM   (SELECT NAME,
                       alias,
                       months,
                       shortmonths
                FROM   sys.syslanguages
                WHERE  NAME = 'us_english') b
               CROSS apply String_split(shortmonths, ',') a) t1
       JOIN (SELECT NAME,
                    alias,
                    months,
                    shortmonths,
                    c.value                     fullName,
                    Row_number()
                      OVER (
                        ORDER BY (SELECT NULL)) rn
             FROM   (SELECT NAME,
                            alias,
                            months,
                            shortmonths
                     FROM   sys.syslanguages
                     WHERE  NAME = 'us_english') b
                    CROSS apply String_split(months, ',') c) t2
         ON t1.rn = t2.rn

use above query in CTE and join it with your table with first three left character and then use EOMONTH and Right function as follows

SELECT t.*,
       m.fullname,
       RIGHT(Eomonth(( '01-'   m.fullname   '-2010' )), 5)
FROM   test t
       JOIN monthname1 m
         ON LEFT(t.month, 3) = m.shortmonth

however using a proper year considering leap year should not be neglected.

dbfiddle