Regarding month text - MSSQL 2005

Question

Post reply

Regarding month text - MSSQL 2005

deepankarthiks 21157

SSC Enthusiast

Points: 194
More actions
July 8, 2010 at 9:53 am

#147923

Hi,
I have two variable one holds the month value(otherwise month id eg: january - 1 etc) another one holding the complete date.
Now using these two i want to take the month text of particular month.
Eg: if the date is 31/01/2008, The resultant what i want is jan.
How to achieve this?
:alien: Beginner in MSSQL, Be bit detail
Thanks in advance
Deepan
[font="Courier New"]Deepan[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

declare @testdate datetime

set @testdate = '20080131'

select DATENAME(month, @testdate)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

deepankarthiks 21157 (7/8/2010)
Hi,
I have two variable one holds the month value(otherwise month id eg: january - 1 etc) another one holding the complete date.
Now using these two i want to take the month text of particular month.
Eg: if the date is 31/01/2008, The resultant what i want is jan.
How to achieve this?
:alien: Beginner in MSSQL, Be bit detail
Thanks in advance
Deepan

What is the datatype of your two variables?

Do you want to determine the name of the month from one of them, and assign it to the other?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

deepankarthiks 21157 SSC Enthusiast Points: 194 More actions · Answer 3

Chris Morris-439714 (7/8/2010)
deepankarthiks 21157 (7/8/2010)
Hi,
I have two variable one holds the month value(otherwise month id eg: january - 1 etc) another one holding the complete date.
Now using these two i want to take the month text of particular month.
Eg: if the date is 31/01/2008, The resultant what i want is jan.
How to achieve this?
:alien: Beginner in MSSQL, Be bit detail
Thanks in advance
Deepan
What is the datatype of your two variables?
Do you want to determine the name of the month from one of them, and assign it to the other?

Yes I want to store it in another variable.

1. The variable which is specifying month Id is INT

2. The variable which is holding the date is DATETIME

3. The variable where the value is to stored is varchar of size 3.

[font="Courier New"]Deepan[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

deepankarthiks 21157 (7/8/2010)
Chris Morris-439714 (7/8/2010)
deepankarthiks 21157 (7/8/2010)
Hi,
I have two variable one holds the month value(otherwise month id eg: january - 1 etc) another one holding the complete date.
Now using these two i want to take the month text of particular month.
Eg: if the date is 31/01/2008, The resultant what i want is jan.
How to achieve this?
:alien: Beginner in MSSQL, Be bit detail
Thanks in advance
Deepan
What is the datatype of your two variables?
Do you want to determine the name of the month from one of them, and assign it to the other?
Yes I want to store it in another variable.
1. The variable which is specifying month Id is INT
2. The variable which is holding the date is DATETIME
3. The variable where the value is to stored is varchar of size 3.

Why do you need three variables for this? Extending Wayne's example:

DECLARE @testdate DATETIME, @Monthname CHAR(3)

SET @testdate = '20080131'

SET @Monthname = DATENAME(month, @testdate)

SELECT @Monthname, @testdate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

deepankarthiks 21157 SSC Enthusiast Points: 194 More actions · Answer 5

Chris Morris-439714 (7/8/2010)
deepankarthiks 21157 (7/8/2010)
Chris Morris-439714 (7/8/2010)
deepankarthiks 21157 (7/8/2010)
Hi,
I have two variable one holds the month value(otherwise month id eg: january - 1 etc) another one holding the complete date.
Now using these two i want to take the month text of particular month.
Eg: if the date is 31/01/2008, The resultant what i want is jan.
How to achieve this?
:alien: Beginner in MSSQL, Be bit detail
Thanks in advance
Deepan
What is the datatype of your two variables?
Do you want to determine the name of the month from one of them, and assign it to the other?
Yes I want to store it in another variable.
1. The variable which is specifying month Id is INT
2. The variable which is holding the date is DATETIME
3. The variable where the value is to stored is varchar of size 3.
Why do you need three variables for this? Extending Wayne's example:
DECLARE @testdate DATETIME, @Monthname CHAR(3)
SET @testdate = '20080131'
SET @Monthname = DATENAME(month, @testdate)
SELECT @Monthname, @testdate

My Apologies for not giving clear explanation.

Actually I have take all 12 months Text using monthID, for with I am using a While loop, the below code should give you some idea.

SELECT @calc_month_id = DATEPART(mm, (DATEADD (mm, @i, @rpt_start_date)))

SELECT @calc_month_text = (SELECT short_text FROM [month] WHERE @calc_month_id = @calc_month_id)

WHILE (@i < 12) BEGIN

INSERT INTO TempTable(

Monthly_Purchased_SIR_Hours,

Total_Available_SIR_Hours,

Utilized,

Unused_Hours,

SIR_Hours_to_Carry_Forward,

month_id,

month_text)

VALUES (

NULL,

@calc_month_id,

@calc_month_text

)

SELECT @i = @i + 1

SELECT @calc_month_id = DATEPART(mm, (DATEADD (mm, @i, @rpt_start_date)))

SELECT @calc_month_text = (SELECT short_text FROM [month] WHERE month_id = @calc_month_id)

END

When i execute the SP I get below error message

Invalid object name 'month'.

[font="Courier New"]Deepan[/font]

Ben Teraberry SSChampion Points: 11845 More actions · Answer 6

Can't really tell for sure what you're trying to do big picture, but it looks like you're trying to make 12 entries in a table. One of the big issues I see (if my assumptions are correct) is that you're storing both the numeric form of the month and the English text abbreviation at the same time. If so, this is ill advised because it's redundant and unnecessary. Instead, just leave the numeric version of the month and any time you want to get the text version you can do it in a query. Such as:

DECLARE @date DATETIME = GETDATE() --substitute GETDATE() with any valid date

DECLARE @month INT = DATEPART(mm, @date) --get the month and store only this

SELECT CONVERT(CHAR(3), DATEADD(mm, @month - 1, 0), 0) --get the text version of any month 1-12

The other thing that I can't help but point out is that you really should avoid using loops if at all possible. I understand that this is probably a small thing and the performance won't matter in this case, but you'll be much better off in the long run if you learn to code T-SQL in a set-based fashion rather than procedurally.

*edit: By the way, this will return exactly the same thing as the 3rd line of code above:

SELECT LEFT(DATENAME(MONTH, DATEADD(mm, @month - 1, 0)), 3) --will return exactly the same thing

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

deepankarthiks 21157 SSC Enthusiast Points: 194 More actions · Answer 7

Thank you very much every one for helping me in resolving this issue. But I have been bagged with bunch of SP's, Will be posting a lot and disturbing a lot you people. 🙂

[font="Courier New"]Deepan[/font]