none
CAST or CONVERT: invalid attributes specified for type 'datetime' Sql Server 2008 RRS feed

  • Question

  • Hello here is the partial SQL code - that uses  "CAST"   Thanks

    select distinct i.id id,  
    replicate('0', 2 - len(datepart(m, i.dt))) + cast(datepart(m, i.dt) as varchar(10))
    + '/' + replicate('0', 2 - len(datepart(d, i.dt))) + cast(datepart(d, i.dt) as varchar(10))
    + '/' + cast(datepart(yyyy, i.dt) as varchar(10)) dt,
    cast(cast(i.report_month as varchar(2)) + '/1/' + cast(i.report_year as varchar(4)) as datetime(8)) sort_report_date,
    isnull(replicate('0', 2 - len(i.report_month)) + cast(i.report_month as varchar(2)) + '/'
    + cast(i.report_year as varchar(4)), '--') report_date,  
    isnull(cast(i.report_count as varchar(10)), '--') report_count

    ect..

    Monday, August 27, 2012 5:46 PM

Answers

  • Why are you doing this? We have a DATE data type!  This is 1950's COBOL or BASIC with all those silly string functions! A magical generic "id" to go with a magical generic "dt", etc. Please stop programming until you know how. 

    A useful idiom is a report period calendar. It gives a name to a range of dates. 

    CREATE TABLE Report_Periods
    (report_name CHAR(10) NOT NULL PRIMARY KEY,
     report_start_date DATE NOT NULL,
     report_end_date DATE NOT NULL,
     CONSTRAINT date_ordering
     CHECK (report_start_date <= report_end_date),
     etc);

    These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created. 

    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. 



    --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

    Wednesday, September 5, 2012 7:48 PM

All replies

  • Hello - I narrowed the specific error to this piece of code  - it worked in SQL SQL 2000

    --- Start  this is were the error is

    --cast(cast(i.report_month as varchar(2)) + '/1/' + cast(i.report_year as varchar(4)) as datetime(8)) sort_report_date,

    -- end

    Monday, August 27, 2012 6:26 PM
  • Try runing this :-

    Datetime data type not expect lenght parameter.

    --- Start  this is were the error is

    --cast(cast(i.report_month as varchar(2)) + '/1/' + cast(i.report_year as varchar(4)) as datetime) sort_report_date,

    -- end


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    • Edited by RohitGarg Tuesday, August 28, 2012 11:34 AM
    • Proposed as answer by Naomi NModerator Wednesday, September 5, 2012 4:34 PM
    Tuesday, August 28, 2012 11:34 AM
  • Try

    cast(ltrim(i.report_year) + right('00' + ltrim(i.report_month),2) + '01' as datetime) -- ISO format is the safest


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


    My blog

    Wednesday, September 5, 2012 4:35 PM
    Moderator
  • Why are you doing this? We have a DATE data type!  This is 1950's COBOL or BASIC with all those silly string functions! A magical generic "id" to go with a magical generic "dt", etc. Please stop programming until you know how. 

    A useful idiom is a report period calendar. It gives a name to a range of dates. 

    CREATE TABLE Report_Periods
    (report_name CHAR(10) NOT NULL PRIMARY KEY,
     report_start_date DATE NOT NULL,
     report_end_date DATE NOT NULL,
     CONSTRAINT date_ordering
     CHECK (report_start_date <= report_end_date),
     etc);

    These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created. 

    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. 



    --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

    Wednesday, September 5, 2012 7:48 PM