none
how to extract month and year from date column

    Question

  • I have a column of date in mm/dd/yyyy format in my table and it's data type is text.

    My objective is to extract only month and year from that table with a specific name.Example:

    ID      Name       Date

    01      SAM       09/02/2012

    02     DAVID     08/05/2012

    I want the exact query to get the O/P.

    Thanks.

    Thursday, October 11, 2012 2:27 AM

Answers

  • Can you post the exact query you tried and also tell us your SQL Server version? Instead of CONVERT, you can use CAST function, e.g.

    declare @test table
    ( id int, Name varchar(7), [date] text);
    insert into @test
    select 1, 'Sam', '09/02/2012' union all select 2, 'David', '08/05/2012'
    ;
    select
      id,
      Name,
      [date],
      month(CAST([date] as varchar(10))) as [Month],
      year(CAST([date] as varchar(10))) as [Year]
    from @test;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 11, 2012 5:04 AM
    Moderator

All replies

  • Try

    select substring(date,7,4)+substring(date,1,2) as year_month,...

    from tab1;


    Many Thanks & Best Regards, Hua Min

    Thursday, October 11, 2012 2:45 AM
  • Just use the year() and the Month() functions; for example:

    declare @test table
    ( id int, Name varchar(7), date date);
    insert into @test
    select 1, 'Sam', '20120902' union all select 2, 'David', '20120805'
    ;
    select
      id,
      Name,
      Date,
      month(date) as [Month],
      year(date) as [Year]
    from @test;
    /* -------- Output: --------
    id          Name    Date       Month       Year
    ----------- ------- ---------- ----------- -----------
    1           Sam     2012-09-02 9           2012
    2           David   2012-08-05 8           2012
    */
    id          Name    Date       Month       Year
    

    Thursday, October 11, 2012 2:57 AM
    Moderator
  • You can use DATEPART function to retrieve month and year value from your datetime column.

    I use Kent's sample tabvle and data to show you another example with datepart:

    declare @test table
    ( id int, Name varchar(7), date varchar(10));
    insert into @test
    select 1, 'Sam', '20120902' union all select 2, 'David', '20120805'
    ;
    select
      id,
      Name,
      Date,
      DATEPART(month,date) as [Month],
      DATEPART(year,date) as [Year]
    from @test;

    Thanks Kent for the sample.

    Thursday, October 11, 2012 3:23 AM
    Moderator
  • Hi,

                  

    SELECT DATEPART(M,'10-11-2012') AS [MONTH],DATEPART(YYYY,'10-11-2012') AS YEAR

            

    SELECT MONTH('10-11-2012') AS [MONTH],YEAR('10-11-2012') AS YEAR

     

                    Hope you got,what u needed :)

    Thanks & Regards,

    Sathya


    sathyas


    Thursday, October 11, 2012 3:36 AM
    Moderator
  • Why you're storing date as text? This is a problem! See

    Dear ISV: You’re Keeping Me Awake Nights with Your VARCHAR() Dates. Hopefully this article is strong enough to convince you to never use varchar for dates.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 11, 2012 3:41 AM
    Moderator
  • Kindly note that date is stored as text here.I cannot change the data type as this is a software which is under use and the column is related to many other tables.

    Kindly update your queries according to the date type as text.

    I just need to fetch month and year for a specific name,thats all!
    • Edited by amlan12 Thursday, October 11, 2012 4:23 AM
    Thursday, October 11, 2012 4:16 AM
  • You need to first convert to varchar, then to datetime, e.g.

    declare @test table
    ( id int, Name varchar(7), [date] text);
    insert into @test
    select 1, 'Sam', '09/02/2012' union all select 2, 'David', '08/05/2012'
    ;
    select
      id,
      Name,
      Date,
      month(convert(datetime,convert(varchar(10),[date]))) as [Month],
      year(convert(datetime,convert(varchar(10),[date]))) as [Year]
    from @test;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 11, 2012 4:27 AM
    Moderator
  • Is there any other method of creating query without converting date type from text to datetime to fetch exact output?
    Thursday, October 11, 2012 4:34 AM
  • This seems to also work - it uses implicit conversion:

    declare @test table
    ( id int, Name varchar(7), [date] text);
    insert into @test
    select 1, 'Sam', '09/02/2012' union all select 2, 'David', '08/05/2012'
    ;
    select
      id,
      Name,
      Date,
      month(convert(varchar(10),[date])) as [Month],
      year(convert(varchar(10),[date])) as [Year]
    from @test;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 11, 2012 4:42 AM
    Moderator
  • I am getting error"Undefined function "Convert" in expression"
    Thursday, October 11, 2012 4:50 AM
  • Are you using case-sensitive SQL Server installation?

    Try using CONVERT in upper case.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 11, 2012 4:52 AM
    Moderator
  • Still receiving the same error in upper case too.
    Thursday, October 11, 2012 4:58 AM
  • It is a string so you can use substring function.

    declare @t table
    (id int, Name varchar(7), [date] text);
    insert into @t
    select 1, 'Sam', '09/02/2012' union all select 2, 'David', '08/05/2012'
    select ID,Name,SUBSTRING([date],4,2) as 'Month', SUBSTRING([date],7,4) as 'Year' 
    from @t


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Thursday, October 11, 2012 4:58 AM
  • Can you post the exact query you tried and also tell us your SQL Server version? Instead of CONVERT, you can use CAST function, e.g.

    declare @test table
    ( id int, Name varchar(7), [date] text);
    insert into @test
    select 1, 'Sam', '09/02/2012' union all select 2, 'David', '08/05/2012'
    ;
    select
      id,
      Name,
      [date],
      month(CAST([date] as varchar(10))) as [Month],
      year(CAST([date] as varchar(10))) as [Year]
    from @test;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 11, 2012 5:04 AM
    Moderator
  • Hello,

    In the date value '10/23/2016' user your column Date and user table name in the from clause. 

    SELECT MONTH(CONVERT(DATETIME, '10/23/2016', 101)) AS 'MONTH',YEAR(CONVERT(DATETIME, '10/23/2016', 101)) AS 'YEAR'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. By ganeshk

    Thursday, October 11, 2012 5:08 AM
  • For naomi

    Error showing as missing operator in query expression 

    month(CAST([date] as varchar(10)))
    Thursday, October 11, 2012 5:31 AM
  • Naomi's script works on SQL Server 2012.

    What is @@version?

    Here is even a stronger typed version:

    declare @test table
    ( id int, Name varchar(7), [date] text);
    insert into @test
    select 1, 'Sam', '09/02/2012' union all select 2, 'David', '08/05/2012'
    ;
    select
      id,
      Name,
      [date],
      month(CAST(CAST([date] as varchar(10)) as DATETIME)) as [Month],
      year(CAST(CAST([date] as varchar(10)) AS DATETIME)) as [Year]
    from @test;
    /*
    id	Name	date	Month	Year
    1	Sam	09/02/2012	9	2012
    2	David	08/05/2012	8	2012
    */

    More on CAST & CONVERT:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012





    Thursday, October 11, 2012 6:10 AM
    Moderator
  • For naomi

    Error showing as missing operator in query expression 

    month(CAST([date] as varchar(10)))

    Hallo Amlan,

    please post YOUR complete CODE. It is nonsens to post just a few letters and the regulars here try to read in a "cristal ball".
    You want us to help you so it is on you to provide us with necessary codes but not fragments!

    The usage of CAST is correct - that CAN NOT BE THE FAILURE!

    Furthermore you didn't answer the request for the version of your sql server and the full query text which has been requested by Naomi.


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    Thursday, October 11, 2012 6:17 AM
  • Uwe - see my correction. It requires double CAST.

    Hallo Kalman,

    for sure but basically the syntax was correct. That was my point of interest.
    If we don't see the complete query we can't help :)
    The error must be at another line...


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de

    Thursday, October 11, 2012 6:50 AM
  • 1. Once again, what is your SQL Server version and where (from SSMS or your application) you're testing this query?

    2. I tried the code I posted locally in SQL Server 2008 R2 SP2 - it worked fine. Alternatively we can use DATEPART functions and not rely on implicit conversion, but convert twice as I shown in the first message.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 11, 2012 12:07 PM
    Moderator