Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

convert month name to month number Expand / Collapse
Author
Message
Posted Thursday, May 6, 2010 1:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 4:24 AM
Points: 18, Visits: 76
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
Post #916705
Posted Thursday, May 6, 2010 1:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
there is no inverse of dateName.
I would use a table to do this.




Clear Sky SQL
My Blog
Kent user group
Post #916709
Posted Thursday, May 6, 2010 2:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
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




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #916728
Posted Thursday, May 6, 2010 3:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 10:49 AM
Points: 1,194, Visits: 791
the above Query works with short names of the month


Declare @month as varchar(10)
set @Month = 'Dec'
select Month(@month + ' 1 2010')
Post #916734
Posted Thursday, May 6, 2010 3:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,880, Visits: 2,846
CASE 'January' when 1 

etc?


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #916756
Posted Thursday, May 6, 2010 4:09 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 9:36 AM
Points: 711, Visits: 2,211
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.




MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #916771
Posted Thursday, May 6, 2010 4:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
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?



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #916782
Posted Thursday, May 6, 2010 4:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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




Clear Sky SQL
My Blog
Kent user group
Post #916790
Posted Thursday, May 6, 2010 6:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
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 :)



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #916848
Posted Thursday, May 6, 2010 8:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
madhu.gut (5/6/2010)
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


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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #917574
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse