Decimal to DateTime2 conversion SQL SERVER 2008 help

  • Hi Forum,

    I have a EndDate column decimal(18,0)

    Q1 -- works perfectly

    select

    convert(datetime2,CONVERT(varchar(30),clx.prdt)) as ADate

    from EXPM clx where clx.prdt<>0

    Error Q2--Msg 241, Level 16, State 1, Line 5

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

    select distinct

    col1

    ,col2

    ,convert(datetime2,CONVERT(varchar(30),clx.prdt)) as ADate

    ,clx.aprvdt

    ,'approved' as ApprovalStatus

    ,'default_data:1' as RequestingUserID

    from a clx

    join b

    join C

    Can anyone throw some light on it.

  • Your query 2 is invalid, because an (INNER) JOIN requires an ON clause, which is missing from your query. I suspect that the problem is in that missing clause.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • There's not enough information on your post to give you a real answer.

    We can't see your data.

    However, I would recommend you to examine this:

    select

    CONVERT(varchar(30),clx.prdt) as ADate

    from EXPM clx where clx.prdt<>0

    Or this

    select

    CONVERT(varchar(30),clx.prdt) as ADate

    from EXPM clx where clx.prdt<>0

    AND ISDATE(CONVERT(varchar(30),clx.prdt)) = 0

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi ,

    I need to convert the varchar(30) to datetime2 in SQL server , which works well in a select statement

    -select

    --cast(convert(datetime2,cast(clx.aprvdt as varchar(10)),101)as datetime2) ApprovalDate

    --from CLMEXPM clx where clx.aprvdt<>0

    but when i join multiple columns to it give me an error

    Msg 241, Level 16, State 1, Line 5

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

    select distinct

    convert(datetime,CONVERT(nvarchar(27),clx.aprvdt)) as ADate

    --,convert(datetime,cast(clx.aprvdt as varchar(30)),101) as ApprovalDate

    --,convert(datetime2,cast(clx.aprvdt as varchar(10)),101) as ApprovalDate

    --,cast(cast(clx.aprvdt as varchar(10)) as datetime2) ApprovalDate

    --,ajm.ADJNUM

    --,clx.CHECK#

    --,clx.ACHK$

    ,clx.aprvdt

    ,'approved' as ApprovalStatus

    ,'default_data:1' as RequestingUserID

    from CLMEXPM clx

    join ADJMSTR ajm on ajm.ADJNUM=clx.ADJNUM

    join CLMMSTR cm on cm.CLPOL#=clx.CLPOL#

    join POLMSTR pl on cm.CLPOL#=pl.POLNBR

    join CTS.dbo.CTS_CLAIM cts on cm.CLMNBR=cts.CLAIM_ID

  • I can see that you're new in here. I really would like to help you but there's nothing I can't do unless you give me something to work with. I can't see your data and just have an idea on what the problem it could be, but it's just a shot in the dark.

    Please read the article about Forum Etiquette linked in my signature.

    My guess is that there's problem with the data stored in your table (that's why it's a best practice to use the correct data type). Use the queries I gave you to find which are the rows that don't have a valid format.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • jampabsatish (9/21/2012)


    Hi ,

    I need to convert the varchar(30) to datetime2 in SQL server , which works well in a select statement

    -select

    --cast(convert(datetime2,cast(clx.aprvdt as varchar(10)),101)as datetime2) ApprovalDate

    --from CLMEXPM clx where clx.aprvdt<>0

    but when i join multiple columns to it give me an error

    Msg 241, Level 16, State 1, Line 5

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

    select distinct

    convert(datetime,CONVERT(nvarchar(27),clx.aprvdt)) as ADate

    --,convert(datetime,cast(clx.aprvdt as varchar(30)),101) as ApprovalDate

    --,convert(datetime2,cast(clx.aprvdt as varchar(10)),101) as ApprovalDate

    --,cast(cast(clx.aprvdt as varchar(10)) as datetime2) ApprovalDate

    --,ajm.ADJNUM

    --,clx.CHECK#

    --,clx.ACHK$

    ,clx.aprvdt

    ,'approved' as ApprovalStatus

    ,'default_data:1' as RequestingUserID

    from CLMEXPM clx

    join ADJMSTR ajm on ajm.ADJNUM=clx.ADJNUM

    join CLMMSTR cm on cm.CLPOL#=clx.CLPOL#

    join POLMSTR pl on cm.CLPOL#=pl.POLNBR

    join CTS.dbo.CTS_CLAIM cts on cm.CLMNBR=cts.CLAIM_ID

    Just add where clause in your second SQL statement

    where clx.aprvdt<>0

    or write case statement in select as

    convert(datetime,CONVERT(nvarchar(27),case when clx.aprvdt=0 then NULL else clx.aprvdt end)) as ADate

    and try....

  • Thanks Luis for your reply,

    Sample Data with decimal(18,0) datatype to be converted to datetime2

    19970811

    19970811

    19970811

    19970811

    19970811

    19970812

    19970812

    19970813

    19970812

    19970812

    19970812

    As the Datatype is in Decimal i convert into Varchar(27) as i need to convert to datetime2

    Query :

    select

    convert(datetime2,CONVERT(varchar(27),clx.aprvdt),102) as ADate

    from CLMEXPM clx where clx.aprvdt<>0

    1997-08-11 00:00:00.0000000

    1997-08-11 00:00:00.0000000

    1997-08-11 00:00:00.0000000

    1997-08-11 00:00:00.0000000

    1997-08-11 00:00:00.0000000

    1997-08-12 00:00:00.0000000

    My questions is i run this query alone and works like a champ but when i add convert(datetime2,CONVERT(varchar(27),clx.aprvdt),102) as ADate in a large query joining multiple tables it throws an error.

    select distinct

    'CheckS' as SubType

    ,convert(datetime2,CONVERT(varchar(27),clx.aprvdt),102) as ADate

    ,clx.aprvdt

    ,'approved' as Approval

    ,'default:1' as UserID

    from CLMEXPM clx

    join tabA ajm on ajm.ADJNUM=clx.ADJNUM

    join tabB cm on cm.CLPOL#=clx.CLPOL#

    join tabC pl on cm.CLPOL#=pl.POLNBR

    where cm.STATUS='closed' and pl.FTYPE in(2,3)

    and cm.CLMNBR>0

    Msg 241, Level 16, State 1, Line 5

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

    What is causing this error

  • -- use NULLIF to convert 0 to NULL

    -- conversion code 112 matches the input style

    -- Char(8) matches the input length

    SELECT

    aprvdt,

    ApprovalDate = CONVERT(DATETIME2,CAST(NULLIF(aprvdt,0) AS CHAR(8)),112) -- 112 matches the input

    FROM (

    SELECT aprvdt = CAST(19970811 AS DECIMAL(18,0)) UNION ALL

    SELECT 19970811 UNION ALL

    SELECT 19970811 UNION ALL

    SELECT 19970811 UNION ALL

    SELECT 19970811 UNION ALL

    SELECT 19970812 UNION ALL

    SELECT 19970812 UNION ALL

    SELECT 19970813 UNION ALL

    SELECT 19970812 UNION ALL

    SELECT 19970812 UNION ALL

    SELECT 19970812 UNION ALL

    SELECT 19970821 UNION ALL

    SELECT 0

    ) [sample]

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • It works like a champ

    Thank you very much Chris !!

  • jampabsatish (9/24/2012)


    It works like a champ

    Thank you very much Chris !!

    You're welcome, and thanks for the feedback. Note that both the problem and the solution appear earlier in this thread;

    ssurve (9/22/2012)


    ...

    Just add where clause in your second SQL statement

    where clx.aprvdt<>0

    or write case statement in select as

    convert(datetime,CONVERT(nvarchar(27),case when clx.aprvdt=0 then NULL else clx.aprvdt end)) as ADate

    and try....

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 10 posts - 1 through 9 (of 9 total)

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