May 6, 2010 at 1:34 am
Hi All
How should i convert the month name to month number like
'April' it should converted to 4
'January' it should converted to 1
in this how should i do this
can any one suggest the correct answer
May 6, 2010 at 1:40 am
May 6, 2010 at 2:39 am
Could you do something like this?
DECLARE @getmonth TINYINT,
@datestring VARCHAR(20),
@input VARCHAR(20)
SET @input = 'January'
SET @datestring = @input + ' 1 2010'
SET @getmonth = MONTH(CAST(@datestring AS DATETIME))
SELECT @getmonth
May 6, 2010 at 3:00 am
the above Query works with short names of the month
Declare @month as varchar(10)
set @Month = 'Dec'
select Month(@month + ' 1 2010')
May 6, 2010 at 3:49 am
CASE 'January' when 1 etc?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 6, 2010 at 4:09 am
SELECT ( CASE yourDate
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END )
That should do the trick.
May 6, 2010 at 4:23 am
I don't understand why the use of a case when a simple function would do the job. . .
My original suggestion fulfills the requirements: -
DECLARE @getmonth TINYINT,
@datestring VARCHAR(20),
@input VARCHAR(20)
SET @input = 'January'
SET @datestring = @input + ' 1 2010'
SET @getmonth = MONTH(CAST(@datestring AS DATETIME))
SELECT @getmonth
As does the post below mine which basically shortened the code.
DECLARE @month AS VARCHAR(20)
SET @Month = 'January'
SELECT MONTH(@month + ' 1 2010')
They'd both also work if you used short names for the months, e.g. "Jan", "Feb" etc. Making them more efficient than a Case, since you'd need to have 24 arguments to replicate it.
Unless I'm missing something?
May 6, 2010 at 4:33 am
skcadavre (5/6/2010)
I don't understand why the use of a case when a simple function would do the job
Try both methods over say 1 million rows.
I would suspect , though i havent tried, that the overhead of the date and then the extraction of the month number would be quite high.
In any case calling a Function (scalar udf) a million time is bad news.
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx
May 6, 2010 at 6:10 am
Dave Ballantyne (5/6/2010)
In any case calling a Function (scalar udf) a million time is bad news.
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx
That was a very interesting read, thankyou 🙂
This is my random months table - 1,000,000 rows (not the cleverest way to do it, just wanted to grab some test data)
USE testingdb
IF EXISTS (SELECT *
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.[Name] = 'monthsNO'
AND s.[Name] = 'dbo')
DROP TABLE dbo.monthsno
CREATE TABLE dbo.monthsno
(
[fldmonth] TINYINT NOT NULL
)
ON [PRIMARY]
GO
USE testingdb
IF EXISTS (SELECT *
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.[Name] = 'months'
AND s.[Name] = 'dbo')
DROP TABLE dbo.months
CREATE TABLE dbo.months
(
[fldmonth] VARCHAR(20) NOT NULL
)
ON [PRIMARY]
GO
USE testingdb
DECLARE @maxRandomValue TINYINT,
@minRandomValue TINYINT,
@cnt INT
SET @maxRandomValue = 12
SET @minRandomValue = 1
SET @cnt = 1000000
WHILE @cnt > 0
BEGIN
SET @cnt = @cnt - 1
INSERT INTO dbo.monthsno
([fldmonth])
SELECT CAST(CAST(( ( @maxRandomValue ) - @minRandomValue ) * Rand() + @minRandomValue AS TINYINT) AS VARCHAR)
END
GO
USE testingdb
INSERT INTO dbo.months
([fldmonth])
SELECT Datename(MONTH, Dateadd(MONTH, [fldmonth] - 1, 0)) AS monthname
FROM dbo.monthsno
GO
DROP TABLE dbo.monthsno
Now, to test the time I ran each query surrounded by: -
DECLARE @time datetime
SET @time=getdate()
/*CODE*/
SELECT datediff(ms,@time,getdate()) as "Time Taken"
Firstly: -
SELECT MONTH([fldmonth] + ' 1 2010')
FROM dbo.months
After 5 attempts I got - 4513, 4453, 4453, 4606 and 4426, so roughly 4.5 seconds on 1 million rows.
Secondly: -
SELECT ( CASE [fldmonth]
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END )
FROM dbo.months
After 5 attempts I got - 4513, 4406, 4533, 4530 and 4516, so once again roughly 4.5 seconds on 1 million rows.
I think the problem was my use of the word "function" in my post. . . it wasn't the word I was after 🙂
May 6, 2010 at 8:55 pm
madhu.gut (5/6/2010)
Hi AllHow should i convert the month name to month number like
'April' it should converted to 4
'January' it should converted to 1
in this how should i do this
can any one suggest the correct answer
There've been a lot of answers to this question and they could all be unnecessary because SQL Server is very forgiving for most date formats. With that thought in mind, what does the original data look like? And I'm not just talking about the month name. For example, if your trying to convert a string to an SQL Datetime and the date looks like '1 June 2010', there you don't need to find the month number to do the conversion....
SELECT CAST('4 June 2010' AS DATETIME)
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2010 at 10:31 pm
This is fairly simple:
select
*,
MonthNumber = month([month]+' 1 2010')
from
( --test data
select [month] = 'January' union all
select [month] = 'April' union all
select [month] = 'September' union all
select [month] = 'December'
) a
Results:
month MonthNumber
--------- -----------
January 1
April 4
September 9
December 12
(4 row(s) affected)
May 7, 2010 at 3:04 am
how abt this?
declare @month datetime
set @month = 'apr'+'1 2010'
SELECT DATEPART(MONTH, @month)
Cheers,,,
Cheers
June 11, 2011 at 12:43 am
Jeff Moden (5/6/2010)
There've been a lot of answers to this question and they could all be unnecessary because SQL Server is very forgiving for most date formats. With that thought in mind, what does the original data look like? And I'm not just talking about the month name. For example, if your trying to convert a string to an SQL Datetime and the date looks like '1 June 2010', there you don't need to find the month number to do the conversion....
SELECT CAST('4 June 2010' AS DATETIME)
Your are genious , Jeff Moden
I like your simplicity.
June 11, 2011 at 8:49 am
Dehqon D. (6/11/2011)
Your are genious , Jeff ModenI like your simplicity.
Agreed. Jeff has a way with cutting to the chase and seeing what is really needed.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 28, 2012 at 2:22 am
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply