Deriving value of 1 column from another column in SQL

Question

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

Have you researched the available date functions such as [EOMonth](https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-ver16)? Please show what you have tried and where you are stuck. — Stu, Jun 27 '22 at 10:34
@Stu my input is just the month name and not the date and the output should be the end date in DD-MMM format. I couldn't find such a function when I tried to search. — Sachin Bhutekar, Jun 27 '22 at 10:41
@Stu is right. You have the month name, you can [convert to num](https://stackoverflow.com/questions/8314310/convert-month-name-to-month-number-in-sql-server) and from that the EOMonth. — Max, Jun 27 '22 at 10:48
This seems to be the wrong way around anyway. You should be storing the date as date datatype (with canonical year if you don't care about that) and then you can derive the month name and DD-MMM formatted string for presentation purposes from that (With no need to store either as a string) — Martin Smith, Jun 27 '22 at 10:51
This is not a free code-writing service but your goal just isn't that difficult. Did you consider asking someone at a giant multi-national outsourcing firm for help? If you want to ignore the year, then this is a simple case expression involving three different logical conditions - is date in Feb then 28, is date in (Jan, Mar, ...) then 31 else 30. Have a go! — SMor, Jun 27 '22 at 10:52

score 0 · Answer 1 · edited Jun 28 '22 at 00:28

0

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.

edited Jun 28 '22 at 00:28

Jeremy Caney

7,102
69
48
77

answered Jun 27 '22 at 15:54

Dharit Sanghvi

1
2

score 0 · Answer 2 · answered Jun 27 '22 at 16:08

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

Deriving value of 1 column from another column in SQL

2 Answers2