Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: Convert month into its number equivalent...(Ex. JAN=1,FEB=2...)

    Good Day to Everyone,

    Need help!

    I have this SQL table w/ fieldname "PayPeriod". Sample value of PayPeriod are SEP06-1, SEP06-2, OCT06-1, OCT06-2... Wherein the first 3 chars is the month, 06 is the year, 1 or 2 is the cutoff where 1 means the 2nd half of the previous month and 2 is the first half of the current month.

    Now my questions are:
    1.) How can I select these values sorted according to date. Since this is a character data type. Sorting is alphabetical. I need to sort it according to date where JAN05-1 is the first record and the last record is DEC06-2 (assuming that the oldest year is 05 and 06 is the latest).
    2.) How can I convert the 3 char month into its number equivalent. Example, JAN is equal to 1, FEB is 2, MAR is 3....
    3.) How can I count the no. of days in a month? Is there a function that would result to the no. of days in a given month?

    Thank you so much in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Any DBA who stores dates as string should be shot.
    Code:
    declare @CrappyDateString varchar(20)
    
    set	@CrappyDateString = 'OCT06-1'
    
    select	datepart(m,
    	convert(datetime, left(@CrappyDateString, 3) + ' '
    	+ substring(@CrappyDateString, 7, 2) + ' '
    	+ substring(@CrappyDateString, 4, 2)))
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1) see 2)

    2) use something like this --
    Code:
    select PayPeriod
         , ( charindex(left(PayPeriod,3)
               ,'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
             + 2 ) / 3 as PayPeriod_numeric
         ,  ...
    3) start with the first day of the given month, add 1 month, add -1 day, then take the DATEPART(d,...)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Extra credit for thinking outside the box...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    Any DBA who stores dates as string should be shot.
    You shouldn't really bottle up your feelings....

    In any case, I would add a new column and convert all the data into valid dates and then try to attack any reporting problems...or maybe even create a derived column..

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 varchar(25))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 'SEP06-1'UNION ALL
    SELECT 'SEP06-2'UNION ALL
    SELECT 'OCT06-1'UNION ALL
    SELECT 'OCT06-2'
    GO
    
    SELECT * FROM myTable99
    
    SELECT    CONVERT(datetime,
    	  LEFT(Col1,3) + ' ' 
    	+ CASE WHEN RIGHT(Col1,1) = 2 THEN '15' ELSE '01' END + ' ' 
    	+ '20'+SUBSTRING(Col1,4,2) + ' '
    )
      FROM myTable99
    GO
    
    ALTER TABLE myTable99 ADD myDateCol AS
    	  CONVERT(datetime,
    	  LEFT(Col1,3) + ' ' 
    	+ CASE WHEN RIGHT(Col1,1) = 2 THEN '15' ELSE '01' END + ' ' 
    	+ '20'+SUBSTRING(Col1,4,2) + ' '
    )
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    An added bonus I think is that it will act like a contraint on your varchar "mask"...I'll have to test that

    EDIT: Nope, you can insert a non-comforming data, and you get the error when you reference it
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    2) Do you want to extract 1 and 1 for JAN06-1 and JAN06-2
    or do you want 12 an 1? See ans 2.1 and 2.2

    Assuming the 15th is the last day of a month
    Code:
    select * into #t1 from (select 'PayPeriod'=
    'OCT06-1' union all select
    'OCT06-2' union all select
    'FEB06-1' union all select
    'FEB06-2' union all select
    'JAN06-1' union all select
    'JAN06-2' union all select
    'SEP06-1' union all select
    'SEP06-2' union all select
    'MAR06-1' union all select
    'MAR06-2' union all select
    'APR06-1'
    ) v1
    
    select PayPeriod
    ,'ans2.1'=datepart(mm,'01'+left(PayPeriod,5)) 
    ,'ans2.2'=datepart(mm,
     case when right(PayPeriod,1)=1 
          then dateadd(mm,-1,'16'+left(PayPeriod,5))
          else convert(datetime,'01'+left(PayPeriod,5))
          end )
    ,'ans3'=1+datediff(dd,dateadd(mm,-1,'16'+left(PayPeriod,5))
                         ,convert(datetime,'15'+left(PayPeriod,5)))
    from #t1
    -- 'Ans1'= order by case statement in ans2.2 or
    order by convert(datetime,'01'+left(PayPeriod,5)),right(PayPeriod,1)
    
    drop table #t1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •