How to convert short month name to last date of month

Question

0.00/5 (No votes)

See more:

I have a SQL Server table which contains 5 columns

The structure is as follows
-------------------------------------------
ID Month Emp_ID Count Year
-------------------------------------------
01 | Jan | 1 | 1.500 | 2015
02 | Feb | 1 | 1.500 | 2015
03 | Mar | 1 | 1.500 | 2015
04 | Apr | 1 | 1.500 | 2015
05 | May | 1 | 1.500 | 2015
06 | Jun | 1 | 1.500 | 2015
07 | Jul | 1 | 1.500 | 2015
08 | Aug | 1 | 1.500 | 2015
09 | Sep | 1 | 1.500 | 2015
10 | Oct | 1 | 1.500 | 2015
11 | Nov | 1 | 1.500 | 2015
12 | Dec | 1 | 1.500 | 2015

I want to convert the second column to like this
-------------------------------------------
ID Month Emp_ID Count Year
-------------------------------------------
01 | 31-Jan-2015 | 1 | 1.500 | 2015
02 | 28-Feb-2015 | 1 | 1.500 | 2015
03 | 31-Mar-2015 | 1 | 1.500 | 2015
04 | 30-Apr-2015 | 1 | 1.500 | 2015
05 | 31-May-2015 | 1 | 1.500 | 2015
06 | 30-Jun-2015 | 1 | 1.500 | 2015
07 | 31-Jul-2015 | 1 | 1.500 | 2015
08 | 31-Aug-2015 | 1 | 1.500 | 2015
09 | 30-Sep-2015 | 1 | 1.500 | 2015
10 | 31-Oct-2015 | 1 | 1.500 | 2015
11 | 30-Nov-2015 | 1 | 1.500 | 2015
12 | 31-Dec-2015 | 1 | 1.500 | 2015

Is there anyway to achieve this?

Posted 26-Mar-15 4:39am

Christopher Fernandes

Add a Solution

3 solutions

Solution 1

If you can do a count for all the days in a month and then replace the month name with the format you wanted?

Posted 26-Mar-15 4:43am

jk0391

Solution 2

You should not store month as string (varchar or anything like that); you should not store time-related data in the table with numeric and string attributes; it is quite unsupportable. Use appropriate data types, such as DATE. Then the particular form (string representation) of date/time data can be decided in your client code, not even SQL code.

—SA

Posted 26-Mar-15 4:46am

Sergey Alexandrovich Kryukov

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-03-26T05:36:00

If you're using SQL 2012 or higher, you can use the EOMONTH function[^] to get the last day in a month. Otherwise, starting from the first day in the month, adding one month and then subtracting one day will give you the same result.

SQL

SELECT
    -- SQL 2012:
    EOMONTH(Convert(date, Convert(char(4), Year) + '/' + Month + '/01')) As LastDayOfMonth
    
    -- Earlier versions:
    -- DateAdd(day, -1, DateAdd(month, 1, Convert(date, Convert(char(4), Year) + '/' + Month + '/01'))) As LastDayOfMonth
FROM
    YourTable

As Sergey said, don't store date values in a string column; use the date type[^] instead.