Decimal to DateTime2 conversion SQL SERVER 2008 help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Decimal to DateTime2 conversion SQL SERVER...

Decimal to DateTime2 conversion SQL SERVER 2008 help

Rate Topic

Display Mode

Topic Options

Author

Message

jampabsatish

Posted Friday, September 21, 2012 2:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:10 AM
Points: 9, Visits: 97

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.

Post #1362970

drew.allen

Posted Friday, September 21, 2012 2:28 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:20 PM
Points: 1,235, Visits: 5,389

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

Post #1362984

Luis Cazares

Posted Friday, September 21, 2012 2:30 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 960, Visits: 1,922

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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1362985

jampabsatish

Posted Friday, September 21, 2012 2:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:10 AM
Points: 9, Visits: 97

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

Post #1362995

Luis Cazares

Posted Friday, September 21, 2012 2:52 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 960, Visits: 1,922

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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1363001

ssurve

Posted Saturday, September 22, 2012 10:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, September 22, 2012 10:57 AM
Points: 3, Visits: 6

jampabsatish (9/21/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....

Post #1363134

jampabsatish

Posted Monday, September 24, 2012 7:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:10 AM
Points: 9, Visits: 97

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

Post #1363466

ChrisM@Work

Posted Monday, September 24, 2012 7:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 5,618, Visits: 10,990

-- 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
Exploring Recursive CTEs by Example Dwain Camps

Post #1363474

jampabsatish

Posted Monday, September 24, 2012 7:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:10 AM
Points: 9, Visits: 97

It works like a champ

Thank you very much Chris !!

Post #1363477

ChrisM@Work

Posted Monday, September 24, 2012 7:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 5,618, Visits: 10,990

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}

Post #1363478

« Prev Topic | Next Topic »

Permissions