Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using a Concatenated DateTime Field in a DateDiff
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/06/2013 :  15:01:00  Show Profile  Reply with Quote
I have 4 fields that are imported as nvarchar(255). I concatenated the dates and the time together so that I could have a specific datetime field so that I can do a date diff on it. It works if I import as datetime, but the package doesn't always import right if there are nulls so I thought it would be best to get all of the data in and then work with it.

,(m.AMIArrivalDate + ' ' + m.AMIArrivalTime)as Arrival
,(m.AMIFirstPCIDate + ' ' + m.AMIFirstPCITime) AS PCI

The fields are later used here:

AMI8aTimeToPCI = DateDiff("n",vw.Arrival,vw.PCI)
I get this error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

I have tried:

,convert(datetime,(m.AMIArrivalDate + ' ' + m.AMIArrivalTime))as Arrival
,convert(datetime,(m.AMIFirstPCIDate + ' ' + m.AMIFirstPCITime)) AS PCI

I still get the same errors.

Can anyone give me any advice?


Thanks in Advance!
Sherri

Thanks in Advance!
Sherri

djj55
Constraint Violating Yak Guru

USA
352 Posts

Posted - 02/06/2013 :  15:31:37  Show Profile  Reply with Quote
Try to use an ISNULL function
,(ISNULL(m.AMIArrivalDate + ' ','1') + ISNULL(m.AMIArrivalTime,'1'))as Arrival
your default values will need to be tweaked.

djj
Go to Top of Page

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/06/2013 :  15:42:57  Show Profile  Reply with Quote
The isnull didn't seem to help. I think I need to convert it, but nothing I do to try to convert it works.

ALTER VIEW [dbo].[vw_AMI8a_CalculateAMI8aPCITime]
As

SELECT
m.EncounterNbr
,m.MeasureSet
,cast(m.AMIArrivalDate as date) as AMIArrivalDate
,cast(m.AMIArrivalTime as time) as AMIArrivalTime
,cast(m.AMIFirstPCIDate as date) as AMIFirstPCIDate
,cast(m.AMIFirstPCITime as time) as AMIFirstPCITime
--,(m.AMIArrivalDate + ' ' + m.AMIArrivalTime)as Arrival
,(ISNULL(m.AMIArrivalDate + ' ','1') + ISNULL(m.AMIArrivalTime,'1'))as Arrival
--,(m.AMIFirstPCIDate + ' ' + m.AMIFirstPCITime) AS PCI
,(ISNULL(m.AMIFirstPCIDate + ' ','1') + ISNULL(m.AMIFirstPCIDate,'1'))as PCIFROM MeasureExportRAW m
WHERE (((m.MeasureSet)='AMI') AND ((m.AMIArrivalTime) Is Not Null))
OR (((m.MeasureSet)='AMI') AND ((m.AMIFirstPCITime) Is Not Null))


This is where I have the error when I try to do a date diff.

UPDATE AMI
SET
AMI8aTimePCI = vw.AMIFirstPCITime
,AMI8aTimePatientArrived = vw.AMIArrivalTime
,AMI8aTimeToPCI = DateDiff("n",vw.Arrival,vw.PCI) From vw_AMI8a_CalculateAMI8aPCITime vw
Join AMI a on a.AMIEncNbr = vw.EncounterNbr


Thanks in Advance!
Sherri
Go to Top of Page

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/06/2013 :  15:47:22  Show Profile  Reply with Quote
This is the kind of data in the fields that I am trying to do a date diff on when I execute the view.

Arrival: 2012-07-20 01:00:00.0000000

PCI: 2012-07-21 1899-12-30 08:22:00

Thanks in Advance!
Sherri
Go to Top of Page

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/06/2013 :  16:00:17  Show Profile  Reply with Quote
I found a fix.

I created a second view that takes the values of the first view. In my first view I casted all of the fields as either date or time:

ALTER VIEW [dbo].[vw_AMI8a_CalculateAMI8aPCITime]
As

SELECT
m.EncounterNbr
,m.MeasureSet
,cast(m.AMIArrivalDate as date) as AMIArrivalDate
,cast(m.AMIArrivalTime as time) as AMIArrivalTime
,cast(m.AMIFirstPCIDate as date) as AMIFirstPCIDate
,cast(m.AMIFirstPCITime as time) as AMIFirstPCITime
,(m.AMIArrivalDate + ' ' + m.AMIArrivalTime)as Arrival
,(m.AMIFirstPCIDate + ' ' + m.AMIFirstPCITime) AS PCI
FROM MeasureExportRAW m
WHERE (((m.MeasureSet)='AMI') AND ((m.AMIArrivalTime) Is Not Null))
OR (((m.MeasureSet)='AMI') AND ((m.AMIFirstPCITime) Is Not Null))

Then in my second view I converted the actual values that I concatenated to date time. Then in my stored procedure that calls the view I was able to just do a normal date diff.

alter view vw_AMI8a_CalculateAMI8aPCITime_2

as

SELECT
m.EncounterNbr
,m.MeasureSet
,m.AMIArrivalDate
,m.AMIArrivalTime
,m.AMIFirstPCIDate
,m.AMIFirstPCITime
,convert(datetime,m.AMIArrivalDate) + ' ' + convert(datetime,m.AMIArrivalTime)as Arrival
,convert(datetime,m.AMIFirstPCIDate) + ' ' + convert(datetime,m.AMIFirstPCITime) AS PCI
from vw_AMI8a_CalculateAMI8aPCITime m


Thanks in Advance!
Sherri
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000