Decimal to DateTime2 conversion SQL SERVER 2008 help

Question

Decimal to DateTime2 conversion SQL SERVER 2008 help

JSB_89

Ten Centuries

Points: 1081
More actions
September 21, 2012 at 2:04 pm

#281991

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.

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

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

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

JSB_89 Ten Centuries Points: 1081 More actions · Answer 3

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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

ssurve SSC Enthusiast Points: 176 More actions · Answer 5

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

JSB_89 Ten Centuries Points: 1081 More actions · Answer 6

Thanks Luis for your reply,

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

19970811

19970812

19970813

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

-- 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 19970812 UNION ALL

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

JSB_89 Ten Centuries Points: 1081 More actions · Answer 8

JSB_89

Ten Centuries

Points: 1081

September 24, 2012 at 7:26 am

#1541394

It works like a champ

Thank you very much Chris !!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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