Sql Server- This is my query: use AuntieB select LEFT(DATENAME(MONTH,[DonationDa

Question

Sql Server-

This is my query:

use AuntieB

select
LEFT(DATENAME(MONTH,[DonationDate]),3)
+ ' ' + DATENAME(DAY,DonationDate) +','+
right(DATENAME(YEAR,[DonationDate]),2) as [Donation Date], CharityID
from DONATION
where CharityID in (1001,1002) and DonationDate like '%13%'
order by CharityID

My output:

Donation Date                           CharityID
------------------------------------- -----------
May 1,13                                     1001
May 2,13                                     1001
Jan 1,13                                     1001
Jun 15,13                                    1002
Jun 30,13                                    1002

I need the day to have two digits, like May 01, 13.

Explanation / Answer

You can do it by using right function as:

select
LEFT(DATENAME(MONTH,[DonationDate]),3)
+ ' ' + right('0'+DATENAME(DAY,DonationDate),2) +','+
right(DATENAME(YEAR,[DonationDate]),2) as [Donation Date], CharityID
from DONATION
where CharityID in (1001,1002) and DonationDate like '%13%'
order by CharityID

We are simply adding a '0' in front of day. And then we are extracting 2 character from right.
Please see below how right function works.

Explanation:
How Right Function works
Right function extracts a substring from a string (starting from right).

Syntax:
RIGHT(string, number_of_chars)

Example 1:
SELECT RIGHT('CheggIndia', 3) ;

This command will extract 3 character form right that is 'dia'.

Example 2:

SELECT right('0'+DATENAME(day, '2017/08/7'),2) AS twodigitdate;

This will give output as: 07

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.