Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2011
    Posts
    78

    Unanswered: Separating month and year

    I wanted to split the month and year apart using the convert function. Anyone know the number for month and year separately? Please help thanks.

    select

    CONVERT(VARCHAR(11),[I_DETAIL].[CASTMONTH],106) As CYEAR

    FROM [Opt].[EDA].[IDETAIL] as i_detail
    where CASTMONTH =201106


    I also tried and this return me some weird like 2020 instead of 2011

    select
    DatePart("yyyy",[I_DETAIL].[CASTMONTH]) as Cyear
    ,DatePart("m",[I_DETAIL].[CASTMONTH]) AS cmonth

    FROM [Opt].[EDA].[IDETAIL] as i_detail
    where CASTMONTH =201106

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT DATEPART(YYYY,[I_DETAIL].[CASTMONTH]) as Cyear
         , DATEPART(M,[I_DETAIL].[CASTMONTH])  AS cmonth
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    78
    Thank you. I tried it and there's a red thing under as.

    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'As'.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Mangolili View Post
    I tried it and there's a red thing
    please show your exact and entire query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    78
    My statements mostly look like these, everything else is similar. I got an error near the first two (the datepart).


    select

    DATEPART(YYYY,[I_DETAIL].[CASTMONTH] As CASTYEAR
    ,DATEPART(M,[I_DETAIL].[CASTMONTH] As CASTMONTH
    ,case [I_DETAIL].[VSTATUS]

    when 'Aired' then 'Yes'
    when 'Failed' then 'No'
    else 'NA'
    end as [VSTATUS]

    ,[I_DETAIL].[SLENGTH]

    FROM [Opt].[EDA].[I_DETAIL] as i_detail

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need a close parenthesis before each of the AS clauses. DatePart() is a function, it requires the opening and closing parenteses.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jul 2011
    Posts
    78
    Thank you so much, both. I fixed it =)

  8. #8
    Join Date
    Jul 2011
    Posts
    78
    Syntax works, but weird year. Why do I get 2020 instead of 2011 when I specify

    where CASTMONTH =201106 ?

  9. #9
    Join Date
    Jul 2011
    Posts
    78
    So weird, I just realized that the month=11, suppose to be 06. For some reason, the it's taking 11 out of 2011
    and the year=2020

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are most likely trying to use DatePart() on an integer. Use a date instead of using an integer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and drop the square brackets unless you need 'em

    if you use 'em when you doesn't has to, they are noise, not signal -- notice how they made you miss seeing the closing parenthesis that wasn't there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by the way, the problem you're having with your 6-digit year-month column was identified a few days ago in this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jul 2011
    Posts
    78
    Thanks for all the replies.

    @r937, the thread you linked only works for the month. It didn't resolve the problem for year.

  14. #14
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by Mangolili View Post
    So weird, I just realized that the month=11, suppose to be 06. For some reason, the it's taking 11 out of 2011
    and the year=2020
    Not weird at all, a date contains year, month and day, in a 6 character string that would be yyMMdd, hence year 20, month 11 day 06. What you need is to add 01 at the end, to get your date in format yyyyMMdd, that is year 2011, month 06 day 01.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by roac View Post
    Not weird at all, a date contains year, month and day, in a 6 character string that would be yyMMdd, hence year 20, month 11 day 06. What you need is to add 01 at the end, to get your date in format yyyyMMdd, that is year 2011, month 06 day 01.
    Ummm, no.
    Code:
    DECLARE @foo		DATETIME
    
    SET @foo = 201106
    
    SELECT @foo
    A DATETIME variable or constant is a string of bits. An INT is also a string of bits. For historical reasons, Transact-SQL has allowed the assignement of an integer to a DATETIME.

    As a geek that learned much of his programming skills in Pascal (which would cause the compiler to scream at this kind of code), this kind of type conversion horrifies me. When it is caught, it results in all kinds of confusion. When it isn't caught, it results in wrong answers that no one can explain.

    Ick!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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