none
Date and Month with conversion

    Question

  • Hi

    Below is an example of my date column

    120528

    120529

    120601

    I've converted these to small date time

    so it looks like this.

    2012-05-28

    2012-05-29

    2012-06-01

    I would now like to extract just the year and month so it shows like this.

    2012-05

    2012-05

    2012-06

    What would be the best way to do this?

    Regards

    Tuesday, July 09, 2013 12:54 PM

Answers

All replies

  • Try the below:

    Select Left('20'+Stuff(columnname,3,0,'-'),7) From TableName
    


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

    Tuesday, July 09, 2013 1:04 PM
  • HI i was looking to extract the year and month from the first part

    eg the 120528

    I tried year(cast(columnname as smalldatetime))+-+Month(cast(columnname as smalldatetime))

    but that seemed to add it altogether rather then seperate the date

    Tuesday, July 09, 2013 1:19 PM
  • SELECT CONVERT(CHAR(4), COLUMN, 100) + CONVERT(CHAR(4), COLUMN, 120)

    As long as it's in a datetime format (which you stated it is), then this will do it.

    Thanks.

    Tuesday, July 09, 2013 1:33 PM
  • SELECT CONVERT(CHAR(4), COLUMN, 100) + CONVERT(CHAR(4), COLUMN, 120)

    As long as it's in a datetime format (which you stated it is), then this will do it.

    Thanks.

    Hi that doesn't seem to work for me

    I need to extract the year and month from

    120528

    120529

    120601

    So that it is formatted like this (ignore that I have converted it to 2012-05-28 etc)

    2012-05

    2012-05

    2012-06

    Tuesday, July 09, 2013 1:47 PM
  • Taking the first date as an example

    declare @date datetime ='120528'
    SELECT CONVERT (CHAR(7),@date,120)

    or directly select from table

    SELECT CONVERT (CHAR(7),ColumnName,120) from tablename  --column needs to be in datetime else wont work

     

    Tuesday, July 09, 2013 1:56 PM
  • SELECT CONVERT(Varchar(7),cast(yourcolumnname as datetime),126)   
    --....

    Tuesday, July 09, 2013 1:57 PM
    Moderator
  • TRY THIS QUERY :

    select convert(varchar(4),datepart(MONTH,'2013-06-08 06:05:06.720')) + '-' +
    convert(varchar(4),datepart(YYYY,'2013-06-08 06:05:06.720'))

    Tuesday, July 09, 2013 1:59 PM
  • SELECT cast(datename(yy,getdate()) as varchar(10))+'-'+cast(datepart(mm,getdate())as varchar(10))
    Tuesday, July 09, 2013 3:28 PM
  • I would highly suggest using a calendar table and doing the calcuation once.

    Please see: http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

    Tuesday, July 09, 2013 10:01 PM
    Moderator

  • Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. 

    CREATE TABLE Month_Report_Periods
    (something_report_name CHAR(10) NOT NULL PRIMARY KEY
       CHECK (something_report_name LIKE  '[12][0-9][0-9][0-9]-[01][0-9]-00'),
     month_start_date DATE NOT NULL,
     month_report_end_date DATE NOT NULL,
      CONSTRAINT date_ordering
        CHECK (month_report_start_date <= month_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 09, 2013 11:17 PM
  • Try this:

    select convert (varchar(7),columnname)
    Cheers,

    Amar Deep Singh

    Tuesday, July 09, 2013 11:29 PM