varchar convert to date at universe platform error (SQL database)

[Former Member]

Hello Everybody .

Database SQL Server 2008

BI 4.1 SP4 P1

I want to change an area format which. is ddmmyyy (varchar) . I want to change varchar area to date format area at universe.

My formula :

CASE WHEN

LEN (VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10) < '8' OR LEN(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10) is NULL THEN VIKODWH.dbo.DWH_FACT_SIPARIS_DETAY.teslim_tarih ELSE

Convert(varchar,convert(varchar(10),VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10,101),101) end

Parse is Ok but I want to display it give me errors .

ettiğimizde “Exception: DBD, [Microsoft SQL Server Native Client 11.0] : Conversion failed when converting date and/or time from character string.State: 22007”

What can I do ? Could you help me please ?

[Former Member]

Hello,

for the else try

convert(date, VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 101)

This should work

Regards

Andreas

[Former Member]

I tried your formula but the problem still continue. Same Error. You can see at picture attachment

[nscheaffer]

I think the problem is that there is no conversion from a string to a date that can handle the string in the format of "mmddyyyy".  When using "101", the CONVERT function is expecting the string to be in the format of "mm/dd/yyyy" (See CAST and CONVERT (Transact-SQL)).

So give something like this a try...

CONVERT(DATE, SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 1, 2) + '/' +
    SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 3, 2) + '/' +
    SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 5, 4), 101)

Does that work?

Noel

[Former Member]

Sorry , İt is not working .

[Former Member]

the syntax ist ok. Can it be, that you have a null date in the data or an invalid date?

[Former Member]

Yes , it has null date in the data . What can I do in formula ?

[Former Member]

try this one:

case when isnull(genel_veri10) then null

else Convert(....)

end

This should be work

[Former Member]

[Former Member]

Ok, try this one:

Case when isnull(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 0) = 0 then null

Else CONVERT(DATE, SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 1, 2) + '/' +  SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 3, 2) + '/' +   SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 5, 4), 101)

end

[Former Member]

sorry ,             it is not working

[Former Member]

you have tried this definition?

Case when isnull(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 0) = 0 then null

Else CONVERT(DATE, SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 1, 2) + '/' +  SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 3, 2) + '/' +   SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 5, 4), 101)

end

Your Screen has a different one

[Former Member]

sorry but it still gives error .

[Former Member]

I tried this to statements on MS SQL Server 2005 and it works:

select

Case when isnull('11282014', 0) = 0 then null

Else CONVERT(DATETIME, SUBSTRING('11282014', 1, 2) + '/' +  SUBSTRING('11282014', 3, 2) + '/' +   SUBSTRING('11282014', 5, 4), 101)

end

from table

select

Case when isnull(null, 0) = 0 then null

Else CONVERT(DATETIME, SUBSTRING('11282014', 1, 2) + '/' +  SUBSTRING('11282014', 3, 2) + '/' +   SUBSTRING('11282014', 5, 4), 101)

end

from table

[Former Member]

Hello Mr.Andreas

I removed null values from table .(siparis_master.genel_veri10).

But still it gives me same. error

convert(date, VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 101)

Error Code :Exception: DBD, [Microsoft SQL Server Native Client 11.0] : Conversion failed when converting date and/or time from character string.State: 22007

I tried your other advices.But still give me errors

[Former Member]

Hello,

can you show the format of the data in siparis_master.genel_veri10

Thanks

Andreas

[Former Member]

[Former Member]

try this for the variable:

CONVERT(DATE, SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 1, 2) + '/' +  SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 3, 2) + '/' +   SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 5, 4), 101)

[Former Member]

[Former Member]

Can you try to execute the CONVERT(DATE, SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 1, 2) + '/' +  SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 3, 2) + '/' +   SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 5, 4), 101)

direct on the database like:

select CONVERT(DATE, SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 1, 2) + '/' +  SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 3, 2) + '/' +   SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 5, 4), 101)

from VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER

[Former Member]

First Advice , it is parse but it still gives some error.

Exception: DBD, [Microsoft SQL Server Native Client 11.0] : Conversion failed when converting date and/or time from character string.State: 22007

Second Advice , it is not parse and it gave error related to "Select".

[nscheaffer]

It looks like your date is in DDMMYYYY format.  Therefore, try reversing the the Day and Month portions.  What do you get this when you run it on directly on the database?

select

CONVERT(DATE,

SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 3, 2) + '/' +  SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 1, 2) + '/' +   SUBSTRING(VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER.genel_veri10, 5, 4), 101)

from VIKODWH.dbo.DWH_FACT_SIPARIS_MASTER

Noel

[Former Member]

Hi Noel ,

When I run your code on database , I get error ,

[Former Member]

Hi,

can you try the type datetime instead of date

Thanks

Andreas

[Former Member]

yes ,When using datetime  , give me underline error .

Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

[Former Member]

I think, you have a problem in your data. Can it be, that you have one not vaild date in the data? Perhaps you can check this first