Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Decimal to DateTime2 conversion SQL SERVER 2008 help Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 2:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:54 PM
Points: 11, Visits: 269
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
Posted Friday, September 21, 2012 2:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Posted Friday, September 21, 2012 2:30 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 4,046, Visits: 9,205
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1362985
Posted Friday, September 21, 2012 2:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:54 PM
Points: 11, Visits: 269
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
Posted Friday, September 21, 2012 2:52 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 4,046, Visits: 9,205
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1363001
Posted Saturday, September 22, 2012 10:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 1:39 AM
Points: 79, Visits: 14
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....
Post #1363134
Posted Monday, September 24, 2012 7:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:54 PM
Points: 11, Visits: 269
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
Posted Monday, September 24, 2012 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
-- 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
Posted Monday, September 24, 2012 7:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:54 PM
Points: 11, Visits: 269
It works like a champ

Thank you very much Chris !!

Post #1363477
Posted Monday, September 24, 2012 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1363478
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse