Datetime Filter Problem 1

[iaresean]

Hey all I have a column (SysEntryDate) that has dates stored in a text field in the format: dd-mm-yyyy hh:mm:ss
(e.g. 1-3-2006 12:2:23 )

I am trying to filter all records that occur in a given month and year, e.g. below:

SELECT COUNT(TheName) AS [Count], MONTH(CAST(SysEntryDate AS datetime)) AS SysMonth, 
   YEAR(CAST(SysEntryDate AS datetime)) AS SysYear
FROM dbo.Request
WHERE SysMonth = "3" AND SysYear = "2006"
GROUP BY TheName, MONTH(CAST(SysEntryDate AS datetime)), YEAR(CAST(SysEntryDate AS datetime))

Unfortunately I keep getting arithmetic overflow errors with the datetime conversions.

So, I thought, f-it. I will use a regular expression then to do my bidding since I know the format of the text field. But lo and behold it looks like SQL Server 2000 doesn't support Regular Expressions! :-(

Any recommendations on getting around this number?

Any and all help is GREATLY appreciated!

Sean.

 

[bborissov]

You can't use fields that you get from SELECT in WHERE clause.
Also the resulting fields are integers not strings.
You could do:

SELECT COUNT(TheName) AS [Count], MONTH(CAST(SysEntryDate AS datetime)) AS SysMonth,
   YEAR(CAST(SysEntryDate AS datetime)) AS SysYear
FROM dbo.Request
WHERE MONTH(CAST(SysEntryDate AS datetime)) = 3 AND
      YEAR(CAST(SysEntryDate AS datetime))  = 2006
GROUP BY TheName, MONTH(CAST(SysEntryDate AS datetime)), YEAR(CAST(SysEntryDate AS datetime))

or

SELECT COUNT(TheName) AS [Count], MONTH(CAST(SysEntryDate AS datetime)) AS SysMonth,
   YEAR(CAST(SysEntryDate AS datetime)) AS SysYear
FROM dbo.Request
HAVING SysMonth = 3 AND SysYear = 2006
GROUP BY TheName, MONTH(CAST(SysEntryDate AS datetime)), YEAR(CAST(SysEntryDate AS datetime))

Also I am not sure that this selects will works anyway, becuase TheName field is not include in SELECT but the GROUP BY requires at least one field that is in SELECT (but you could try)

Borislav Borissov

 

[iaresean]

My bad, I merely threw in the where clause as an illustration of me getting a specific month/year. But even if I remove the where/having clause it still gives me an arthimetic overflow error.

I know this type of select should work because I have done the same thing with another field/table and it worked fine.

Any and all help will be greatly appreciated.

Sean.

 

[bertrandkis]

Your problem might be some records with date formats that SQL cannot convert into a proper datetime format. If you are sure that the entries in the SysEntryDate are consistent 1-3-2006 12:2:23, all you need is to extract the date part and then cast it to a datetime. You can use string functions like this.
Assuming that the space exists between the date and the time

SELECT COUNT(TheName) AS [Count], MONTH(CAST(Left(SysEntryDate ,Patindex('% %',SysEntryDate )) as datetime )) AS SysMonth,
YEAR(CAST(Left(SysEntryDate ,Patindex('% %',SysEntryDate )) as datetime )) AS SysYear
FROM dbo.Request
Try this and let us know if you get any joy.
For information about the left and Patindex function check sql Books online.
BertrandKis.

 

[iaresean]

It was a good shot, I really thought that would be the answer, but unfortunately it still gave me errors. I ran the "Left(SysEntryDate ,Patindex('% %',SysEntryDate ))" part to see the records it returned and it did just return the date part.

I noticed some records had a slight different format like:
01-03-2006
1-3-2006
02-04-2006
2-04-2006

But shouldn't this still work, cause they are all MM-DD-YYYY. Can SQL Server really be so 'stick-up-the' about this?

I think I am going to have to use the PATINDEX with the combination of SUBSTRING to work something up here.

Any and all help is greatly appreciated.

Sean.

 

[bertrandkis]

I am disappointed that it did not work.Any way, the challenge now is to find the records that are causing the conversion to fail. To do that we have to proceed by elimination and test the data one partition at a time.
For example you may select all records where the date is 02-04-2006, SELCT * INTO #MYTEMPTABLE FROM dbo.Request
WHERE SYSENTRYDATE Like'%04-2006%' store that in a temporary table then,run the SELECT COUNT(TheName) AS [Count], MONTH(CAST(Left(SysEntryDate ,Patindex('% %',SysEntryDate )) as datetime )) AS SysMonth,
YEAR(CAST(Left(SysEntryDate ,Patindex('% %',SysEntryDate )) as datetime )) AS SysYear
FROM #MyTempTable
against the temp table and see what will happen, if it works fine,you use an other selection criteria, create a temp table and test the script again. You may evantually norrow dow the problem to a set of records and from there you may find the light.
Good luck.If you need more clarification, I am still on line and I am checking the thread.

 

[bborissov]

I have one question, Why you keep this in text field?
Why you didn't use DateTime?

Borislav Borissov

 

[iaresean]

bborissov - Trust me, if I designed the Database I would have, unfortunately I have been given the daunting task of fixing a system that someelse designed and that has been used for over 3 years now. :-(

bertrandkis - Thanks for the new comment, I agree that it may be an approach, but it is certainly a time consuming one. I decided to go for a dirty fix and I produced the following code that seems to do the query fine.

It isn't the cleanest piece of code I have written, and certainly not one I am proud of, but it does the job.

SELECT COUNT(TheName) AS [Count],
 SUBSTRING(SysEntryDate, PATINDEX('%[0-9][-][0-9]%', SysEntryDate) + 2,
 PATINDEX('%[0-9][-][0-9][0-9][0-9][0-9]%', SysEntryDate) - 
 PATINDEX('%[0-9][-][0-9]%', SysEntryDate) - 1) As SysMonth, 
 SUBSTRING(SysEntryDate, PATINDEX('%[0-9][-][0-9][0-9][0-9][0-9]%', SysEntryDate) + 2, 
 (PATINDEX('%[0-9][0-9][0-9][0-9][ ]%', SysEntryDate) + 4) - 
 PATINDEX('%[0-9][-][0-9][0-9][0-9][0-9]%', SysEntryDate) - 1) As SysYear
FROM dbo.Request

Thanks for your interest in my problem, much appreciated. I still think that first solution you gave me should have worked though, wierd.

Thanks again.

Sean.

 

[SQLBill]

Start your SELECT with:

SET DATEFORMAT DMY

That should take care of it.

SET DATEFORMAT tells SQL Server how to interpret dates that are not in a DATETIME/SMALLDATETIME format. It's not needed when the full year comes first or if the month is spelled out as there rarely can be any confusion then.

-SQLBill

Posting advice: FAQ481-4875

 

[bertrandkis]

Well I am glad that you have taken my idea one step further. As long as it works, the code is good. You deserve my *. Good work.