| TeleFlow Forums http://teleflow.org/phpbb/ |
|
| MS SQL and DATE fields http://teleflow.org/phpbb/viewtopic.php?f=10&t=244 |
Page 1 of 1 |
| Author: | trace [ Fri Apr 22, 2005 9:09 am ] |
| Post subject: | |
Hello, <br> <br>I have a SQL query that I am having trouble getting to work properly: <br> <br>SELECT testID <br>FROM tblTests <br>WHERE SubmissionDeadline >= @DATE <br>AND userID = @GETID <br> <br>SubmissionDeadlines that are less than the current date are still being queried. <br> <br>I understand that SQL dates are often not compatible with TeleFlow dates. In MS SQL, the date fields are stored as 4/4/2005. The @DATE variable is formatted as YYYY/MM/DD. Is there a way to make this query work? <br> <br>Thanks! <br>Trace |
|
| Author: | Chris [ Fri Apr 22, 2005 9:30 am ] |
| Post subject: | |
You'll need to locate an MS SQL function that allows you to convert the TeleFlow date to a date format that MS SQL supports. <br> <br>From having dealt with some such issues, I can tell you that you might find it works if you try the following comparisons (test each, I'm not sure which, if any, will work in this case), as well: <br>SubmissionDeadline >= Now() <br>SubmissionDeadline >= '@DATE' <br>SubmissionDeadline >= @DATE @TIME <br>SubmissionDeadline >= '@DATE @TIME' <br> <br>Now(), in particular, is a function that is available in many databases, and all seem to do a good job of comparing/using it againt their own date/time data types. <br> <br>Failing all that, I would suggest consulting an MS SQL book for MS SQL specific syntax to use in date/time formatting. |
|
| Author: | trace [ Fri Apr 22, 2005 10:05 am ] |
| Post subject: | |
It worked. The getDate() MS SQL function worked. Didn't realize we could use the MS SQL functions themselves within the queries....yippee!! <br> <br>Thanks Chris! |
|
| Author: | admin [ Fri Apr 22, 2005 10:40 am ] |
| Post subject: | |
This MS SQL function will convert a date to: 'YYYY/MM/DD' format <br> <br>convert(char(10),a.birthdate,111) |
|
| Page 1 of 1 | All times are UTC - 8 hours [ DST ] |
| Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group http://www.phpbb.com/ |
|