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.
| Author |
Topic |
|
StressIsAWayOfLife
Starting Member
4 Posts |
 Posted - 2008-06-09 : 07:49:19
|
Hi all.I have been banging my head against a brick wall with this for a couple of days now. I am wrighting a custom front end for one of our SQLServer DB's and need to run a select agains the DateTime Field. The field Format is [DD/MM/YYYY HH:MM:SS]. I am tring to get the select to pull all the records on a set date only (keeping the time available but not selecting against it) so that the brass can see at what time on a given day an action was performed. Problem is, the way the programing software works, I can only build the SQL using string information. I have tried CONVERT and CAST both ways (converting the date to varchar and converting the string to date and also converting them both at the same time). Everything that I have tried returns an empty data set when used with the WHERE LIKE criteria (oh yeah, I'm also using LIKE % wildecards to retrive all the times against a specific date) but does populate when no filter is applied.This is where my SQL is at just now:SELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDateFROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserIDWHERE (((CONVERT(varchar(10), FileStatusLog.StatusDate,103) LIKE '%{variable}%'));This also returns an emtpy data set when the LIKE is replaced with ='{exact date entry in format: DD/MM/YYYY}', but does return the full table when the WHERE is removed and also when the LIKE is set to just a bare '%' with no other filter values.Because of the fact that the results are returned when LIKE '%' is used I am not even sure if it is the CONVERT that is causing the problem. I have even tried assigning the escape character manualy to '+' incase the / values of the date in the LIKE variable where escaping other characters. Needless to say this did not make life any better.As you may have noticed already SQL is not exactly my strong suit, but no one else here even knows what it is, never mind what it's used for, so I got droped with the task. Also - The DB was created by a third party so I have no write permissions to the DB.Any help with this would be greatly appreciated as it's gotten personal now  Thaks in advance for any assistance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-09 : 07:57:07
|
| Why convert to string at all? Can you use justSELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDateFROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserID WHERE DATEADD(d,DATEDIFF(d,0,FileStatusLog.StatusDate),0)=variable |
 |
|
|
StressIsAWayOfLife
Starting Member
4 Posts |
Posted - 2008-06-09 : 08:17:04
|
| Tried that just now using a known record value and all I get is "Incorect syntax near '2008'", tried putting it in as '14/01/2008', 14/01/2008, 14012008, #14012008# and #14/01/2008#, although with the last 2 the syntax error was neer "#". The reason that I was converting the date in the first place was bacause the pragraming tool I am using will only let me enter string values into the SQLQuery properties, I think I have just about worked my way around that one now though (been trying for anything that would work) so I'm open to pretty much any suggestion at this point Thanks for the quick reply though visakh16 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-09 : 08:31:34
|
quote:
Originally posted by StressIsAWayOfLife
Tried that just now using a known record value and all I get is "Incorect syntax near '2008'", tried putting it in as '14/01/2008', 14/01/2008, 14012008, #14012008# and #14/01/2008#, although with the last 2 the syntax error was neer "#". The reason that I was converting the date in the first place was bacause the pragraming tool I am using will only let me enter string values into the SQLQuery properties, I think I have just about worked my way around that one now though (been trying for anything that would work) so I'm open to pretty much any suggestion at this point Thanks for the quick reply though visakh16
Is you table field StatusDate datetime? if yes, you can very well pass string values likeSELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDateFROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserID WHERE DATEADD(d,DATEDIFF(d,0,FileStatusLog.StatusDate),0)='1 jan 2008' |
|
|
|
StressIsAWayOfLife
Starting Member
4 Posts |
Posted - 2008-06-09 : 09:55:32
|
| Again the Sytax error comes up. The field is set to DateTime of the format mentioned in the first post, I don't know what's wrong, if it should work it's not. I even tryed using dd in the argument just in case. Is there no way to compare it is a text field?Thanks again for the help |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-09 : 10:00:36
|
I also fixed the typo.SELECT Files.FileID, Users.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDateFROM FilesINNER JOIN FileStatusLog ON FileStatusLog.FileID = Files.FileIDINNER JOIN Users ON Users.UserID = FileStatusLog.UserIDWHERE CONVERT(CHAR(10), FileStatusLog.StatusDate, 103) LIKE '%' + @Variable + '%'
E 12°55'05.25"N 56°04'39.16" |
|
|
|
StressIsAWayOfLife
Starting Member
4 Posts |
Posted - 2008-06-09 : 10:16:35
|
| Awsome guys, got it sorted. Thanks alot for the help and input. Take care. |
|
|
|
|
|
|
|
|
Selective String Search against DateTime Field