locked
Datetime conversion RRS feed

  • Question

  • Hi,

    I have a table  tableA which has column datekey of type bigint. It has data like '20120101, 20121212'. I want to get the date in format like 2012 Jan, 2012 Dec etc.

    I tried below query for this but get the error:

    SELECT YEAR(CAST(CONVERT(VARCHAR(12),DATEKEY)AS DATE)) + ' ' +LEFT(DATENAME(MONTH,(CAST(CONVERT(VARCHAR(12),DATEKEY)AS DATE))),3)FROM tableA

    Conversion failed when converting the nvarchar value 'Oct' to data type int.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Friday, January 18, 2013 1:39 PM

Answers

  • I have done with it :)

    I changed to query as:

    SELECT cast(YEAR(CAST(CONVERT(VARCHAR(12),DATEKEY)AS DATE)) as varchar(10))+' '+ cast(LEFT(DATENAME(MONTH,(CAST(CONVERT(VARCHAR(12),DATEKEY)AS DATE))),3) as varchar(10))
    FROM tableA


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Marked as answer by Kapil.Kumawat Friday, January 18, 2013 1:47 PM
    Friday, January 18, 2013 1:47 PM

All replies

  • Use datename(year) instead of year. Year returns the year as a number, and when you have number and string in the same exprssion, the string will be converted to numeric, and then it goes downhill from there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, January 20, 2013 5:23 PM
    Friday, January 18, 2013 1:44 PM
  • I have done with it :)

    I changed to query as:

    SELECT cast(YEAR(CAST(CONVERT(VARCHAR(12),DATEKEY)AS DATE)) as varchar(10))+' '+ cast(LEFT(DATENAME(MONTH,(CAST(CONVERT(VARCHAR(12),DATEKEY)AS DATE))),3) as varchar(10))
    FROM tableA


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Marked as answer by Kapil.Kumawat Friday, January 18, 2013 1:47 PM
    Friday, January 18, 2013 1:47 PM
  • Hello,

    BigInt for such small nums? Int would be good enough. Anyway, you have to do some conversion; in the final, outer convert you can define the format; here ist format 107 = Mon dd, yyyy used. See CONVERT for further format options.

    DECLARE @num bigint;
    SET @num = 20120101;
    
    SELECT CONVERT(varchar(50), CONVERT(datetime, CONVERT(varchar(8), @num), 112), 107) AS RealDate


    Olaf Helper

    Blog Xing

    Friday, January 18, 2013 1:49 PM
  • can i use this expression in a join as I have a column in another table that has data in form 'NOV 2012' etc....


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Friday, January 18, 2013 2:19 PM
  • Of course you can use such an expression in a join. But be aware using function on filter prevents the usage of index(es), so the performance will be poor.

    Olaf Helper

    Blog Xing

    Friday, January 18, 2013 2:37 PM