CODEWITHSUNDEEP
sqlsql-server
xorpower
xorpower

Reputation: 18963

Fetch month name based on various dates in table

I have one table that contains BillDate column i.e. Bill Dates for various customers. The bill date range from September 1 to September 30. Based on the billdate, I want to fetch Month name in format "Sep-19" (19 being year 2019)

The issue is that from next month, the BillDate will have records for both September and October 2019.

For Oct 2019, the bill month that I want to fetch is "Oct-19" as I will create a report based on data present for bills in October month.

How to fetch bill month based on data that changes every month?

Upvotes: 0

Views: 188

Answers (4)

data_matata
data_matata

Reputation: 36

Use the format function to convert the date in your format. And then you can use the where function to get the results for current month (oct 2019).

Note: Format is small and nice function if you're using to build this report query for small data. But if you're working with relatively large datasets I would recommend convert as format would have affect on performance.

Select *
From
(select your_field1, your_field2, format(BillDate, 'MMM-yy') as Current_Date from your_table) as alias_table
Where [Current_Date] = format(GETDATE(), 'MMM-yy');

In SQL you cannot use alias columns names within the same query that is why I have used a subquery in paranthesis to force SQL to handle where before select.

Upvotes: 0

THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

You can try this:

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(20), BillDate, 106), 8), ' ', '-')

For Getting Most Recent Data you can try this 

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(20), BillDate, 106), 8), ' ', '-') AS 'YourColumnName' FROM TableName
ORDER BY BillDate DESC

For Testing Purpose:-

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 106), 8), ' ', '-') AS 'CurrentDate'

Output:-

 CurrentDate
 -----------
 Oct-2019

Upvotes: 0

Thom A
Thom A

Reputation: 95554

FORMAT is an awful function for this, it's horrifically slow. I highly recommend CONVERT and a style code, along with some string manipulation.

REPLACE(STUFF(CONVERT(varchar(11),BillDate,106),1,3,''),' ','-')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

One method uses format():

select format(BillDate, 'MMM-yy')

A more old-fashioned method constructs the string:

select left(datename(month, BillDate), 3) + '-' + right(datename(year), BillDate), 2)

Upvotes: 1

Related Questions