Home > Software engineering >  Deriving value of 1 column from another column in SQL
Deriving value of 1 column from another column in SQL

Time:06-28

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

  • Related