WHERE (dbo.ED_ATTENDANCE.AttendDateTime >= CONVERT(DATETIME, '2015-04-01 00:00:00', 102))
Hi guys is there anything wrong with the above WHERE clause? How the data outputs is displayed as 09/04/2015  13:07:00 normally, so don't see why SQL Management Studio has put in the Convert. Its this line thats timing me out? Any thoughts I could try

Hi ProfessorFudger,

Could you Please clarify 'breaking my SQL'. What is the error message?

What is the datatype of AttendDateTime?

As such there shouldn't be anything wrong with usage of the above where clause

The optional style element of the CONVERT function is for converting to one of the string datatypes. It doesn't apply when converting to datetime.

But... That doesn't really matter in this case because the CONVERT function isn't even needed... The following will work just fine.

WHERE dbo.ED_ATTENDANCE.AttendDateTime >= '2015-04		

It times me out when I add the above into my SQL, and runs in seconds when I take it back out. The field is a DateTime field which outputs as 09/04/2015  13:07:00

This could be because there is no index on the column you are filtering the date on?

Look at the Query Estimation Plan to see if on this filter it is costing a lot of resource.

The optional style element of the CONVERT function is for converting to one of the string datatypes. It doesn't apply when converting to datetime.

But... That doesn't really matter in this case because the CONVERT function isn't even needed... The following will work just fine.

WHERE dbo.ED_ATTENDANCE.AttendDateTime >= '2015-04		

The optional style element of the CONVERT function is for converting to one of the string datatypes. It doesn't apply when converting to datetime.

But... That doesn't really matter in this case because the CONVERT function isn't even needed... The following will work just fine.

WHERE dbo.ED_ATTENDANCE.AttendDateTime >= '2015-04		

Instead of using '2015-04-01' try using '01-Apr-

>Sorry buddy Timeout again

So you have a query that's taking a long time to execute, and have ruled out the wrapping of the datetime string in a convert function as the culprit.

You'll now need to share more information about your issue.

David

The optional style element of the CONVERT function is for converting to one of the string datatypes. It doesn't apply when converting to datetime.

But... That doesn't really matter in this case because the CONVERT function isn't even needed... The following will work just fine.

WHERE dbo.ED_ATTENDANCE.AttendDateTime >= '2015-04		

You can also use 

YYYYMMDD format which is universal and always treated the same.

You can also use 

YYYYMMDD format which is universal and always treated the

A contractor who is an SQL expert Im told came up with this:  

However it seems a long winded way to get around a simple datetime issue, or as a total newbie to sql am i wrong?

My original SQL was: 

SELECTdbo.ED_ATTENDANCE.AENo AS [ED Number 1],

      ED_ATTENDANCE_1.AENo AS [ED Number 2],

      dbo.ED_ATTENDANCE.AttendDateTime AS [Attendance Time/Date 1],

      ED_ATTENDANCE_1.AttendDateTime AS [Attendance Time/Date 2],

      DATEDIFF(MINUTE, dbo.ED_ATTENDANCE.AttendDateTime, ED_ATTENDANCE_1.AttendDateTime) AS 'Minutes Between Attendances',

      dbo.ED_ATTENDANCE.DepartDateTime AS [Departure Time/Date 1],

      ED_ATTENDANCE_1.DepartDateTime AS [Departure Time/Date 2],

      dbo.MF_PATIENT.FullName AS [Patients Full Name],

      dbo.MF_PATIENT.NHSNo1 AS [NHS Number],

      dbo.MF_PATIENT.HEYNo AS [HEY Number],

      dbo.ED_ATTENDANCE.MethOfDepartureDesc AS [Departure Method 1],

      ED_ATTENDANCE_1.MethOfDepartureDesc AS [Departure Method 2]

FROMdbo.ED_ATTENDANCE

INNER JOINdbo.ED_ATTENDANCE AS ED_ATTENDANCE_1 ON dbo.ED_ATTENDANCE.InternalNo = ED_ATTENDANCE_1.InternalNo

LEFT JOINdbo.MF_PATIENT ON dbo.ED_ATTENDANCE.MFPatientID = dbo.MF_PATIENT.MFPatientID

WHERE(dbo.ED_ATTENDANCE.DepartDateTime IS NOT NULL)

      AND (ED_ATTENDANCE_1.DepartDateTime IS NOT NULL)

      AND (dbo.ED_ATTENDANCE.AttendDateTime >= '2015-04-01')

      AND (ED_ATTENDANCE_1.AttendDateTime >= '2015-04-01')

      AND (DATEDIFF(MINUTE, dbo.ED_ATTENDANCE.AttendDateTime, ED_ATTENDANCE_1.AttendDateTime) > 0.01)

      AND (DATEDIFF(MINUTE, dbo.ED_ATTENDANCE.AttendDateTime, ED_ATTENDANCE_1.AttendDateTime) < 10)

ORDERBY [Attendance Time/Date 1]

Looking at this query, I think I can see what the author was trying to accomplish...  Just not going about it in the fastest way possible...

Couple questions...

What version of SQL Server are you using?

Am I correct in assuming that "InternalNo" repeats with each attendance of a given patient?

And no... It's not necessarily long winded... Just not as efficient as it probably cou

Minor points for improvement

What's wrong with a between instead of >= and <=?

DATEDIFF returns an INT, no point making it convert to a decimal.

From my limited time using SQL Server, I've found it isn't the best at conversions so prevent it converting when it doesn't need to.

By the looks of the query, attempting to identify how quickly subsequent patients are seen? If so, you would only need to look at the next record in line, not every record thereafter (as a way of reducing the Cartesian)

Hi Jason, InternalNo is indeed a patient identifier which can have multiple attendances. I am using SQL Server 2008.

What I am doing in this is looking for possible duplicate attendances where a patient has been recorded twice within 10 minutes (this shouldn't and rarely happens). 

I feel a right idiot now, the answer was obvious, rather tha using the AttendDateTime field I just used the AttendFinYear field which did the trick. Sorry guys I am just 9 days into learning SQL and jumping in at the deep end here. I had the fields below to pick from: