Post reply

Conversion failed when converting date and/or time from character string

  • September 26, 2021 at 9:46 pm

    #3933264

    Hi Community,

    I have created the following table

    CREATE TABLE HMCTS.MDM_DEV.dimDate (
      DATE_KEY DATETIME NULL
     ,DAY_OF_WEEK DATETIME NULL
     ,DAY_NUM_IN_MONTH DATETIME NULL
     ,DAY_NUM_OVERALL DATETIME NULL
     ,DAY_NAME DATETIME NULL
     ,DAY_ABBREV DATETIME NULL
     ,WEEKDAY_FLAG DATETIME NULL
     ,WEEK_NUM_IN_YEAR DATETIME NULL
     ,WEEK_NUM_OVERALL DATETIME NULL
     ,WEEK_BEGIN_DATE DATETIME NULL
     ,WEEK_BEGIN_DATE_KEY DATETIME NULL
     ,MONTH DATETIME NULL
     ,MONTH_NUM_OVERALL DATETIME NULL
     ,MONTH_NAME DATETIME NULL
     ,MONTH_ABBREV DATETIME NULL
     ,MONTH_YEAR DATETIME NULL
     ,QUARTER DATETIME NULL
     ,YEAR DATETIME NULL
     ,YEARMO DATETIME NULL
     ,FISCAL_MONTH DATETIME NULL
     ,FISCAL_QUARTER DATETIME NULL
     ,FISCAL_YEAR DATETIME NULL
     ,LAST_DAY_IN_MONTH_FLAG DATETIME NULL
     ,SAME_DAY_YEAR_AGO DATETIME NULL
     ,WORKING_DAY_FLAG DATETIME NULL
     ,DDMMYY_DATE DATETIME NULL
     ,MONYY_DATE DATETIME NULL
     ,YEAR_QUARTER DATETIME NULL
     ,FISCAL_YEAR_QUARTER DATETIME NULL
     ,LAST_DAY_IN_WEEK_FLAG DATETIME NULL
     ,LAST_DAY_IN_QUARTER_FLAG DATETIME NULL
     ,LAST_DAY_IN_FISCAL_YEAR_FLAG DATETIME NULL
     ,FISCAL_WEEK_NUM DATETIME NULL
     ,WORKING_DAY_SCOTLAND_FLAG DATETIME NULL
    ) ON [PRIMARY]
    GO

    And I trying insert values into the columns as follows:

    USE MyTestDB
    SET DATEFORMAT ymd

    Insert into MDM_DEV.dimDate (DATE_KEY,DAY_OF_WEEK,DAY_NUM_IN_MONTH,DAY_NUM_OVERALL,DAY_NAME,DAY_ABBREV,WEEKDAY_FLAG,WEEK_NUM_IN_YEAR,WEEK_NUM_OVERALL,WEEK_BEGIN_DATE,WEEK_BEGIN_DATE_KEY,MONTH,MONTH_NUM_OVERALL,MONTH_NAME,MONTH_ABBREV,MONTH_YEAR,QUARTER,YEAR,YEARMO,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR,LAST_DAY_IN_MONTH_FLAG,SAME_DAY_YEAR_AGO,WORKING_DAY_FLAG,DDMMYY_DATE,MONYY_DATE,YEAR_QUARTER,FISCAL_YEAR_QUARTER,LAST_DAY_IN_WEEK_FLAG,LAST_DAY_IN_QUARTER_FLAG,LAST_DAY_IN_FISCAL_YEAR_FLAG,FISCAL_WEEK_NUM,WORKING_DAY_SCOTLAND_FLAG) values (convert(DATE,'11-SEP-01',104),2,11,254,'Tuesday  ','Tue','Y',37,null,convert(DATE, '10-SEP-01',104),20010910,9,null,'September','Sep','September 2001',3,2001,200109,6,2,2001,'N',convert(DATE,'11-SEP-00',104),'Y','11/09/01','Sep 01','2001-Q3','2001-Q2','N','N','N',24,null);

    However, I'm getting the following error:

    Conversion failed when converting date and/or time from character string

    Can someone let me know where I'm going wrong?

    Thanks

     

     

  • September 26, 2021 at 10:22 pm

    #3933274

    Not one of these are valid date/datetime values:

    'Sep 01', '2001-Q3', '2001-Q2', 'Sep 01', '2001-Q3', '2001-Q2'

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • September 26, 2021 at 11:05 pm

    #3933279

    Not to mention "Y", "N", month names with no reference to a year and much more.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 27, 2021 at 10:04 am

    #3933412

    Thank you Jeff. Spot on. Thanks

  • September 27, 2021 at 11:16 am

    #3933430

    carlton 84646 wrote:

    Thank you Jeff. Spot on. Thanks

    Scott was the first to respond. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 28, 2021 at 8:31 am

    #3933818

    Thanks all for you're contribution. I realise there are too many unknowns for this question to be properly addressed. Therefore, I would be happy for the administrator of this forum to delete this question.

    I will submit the question again with more detail.

    Thanks

  • October 6, 2021 at 11:49 am

    This was removed by the editor as SPAM

  • October 6, 2021 at 11:58 am

    #3938039

    Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply