Using a Concatenated DateTime Field in a DateDiff

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using a Concatenated DateTime Field in a DateDiff

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2013-02-06 : 15:01:00

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

352 Posts

Posted - 2013-02-06 : 15:31:37

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

sross81
Posting Yak Master

228 Posts

Posted - 2013-02-06 : 15:42:57

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

sross81
Posting Yak Master

228 Posts

Posted - 2013-02-06 : 15:47:22

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

sross81
Posting Yak Master

228 Posts

Posted - 2013-02-06 : 16:00:17

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources