SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Decimal to DateTime2 conversion SQL SERVER 2008 help


Decimal to DateTime2 conversion SQL SERVER 2008 help

Author
Message
jampabsatish
jampabsatish
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 537
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.
drew.allen
drew.allen
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6256 Visits: 10871
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16579 Visits: 19098
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
jampabsatish
jampabsatish
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 537
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
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16579 Visits: 19098
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
ssurve
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 16
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....
jampabsatish
jampabsatish
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 537
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16264 Visits: 19551
-- 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
jampabsatish
jampabsatish
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 537
It works like a champ

Thank you very much Chris !!
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16264 Visits: 19551
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search