DateTime Field query... - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

DateTime Field query...

Author

Topic

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-09-24 : 09:33:51

I have upsized an Access database to SQL 2000.

I am now in the process of sorting out all my SQL Queries so that they will work on SQL 2000.... one im having particular problem with is:

SELECT * FROM Diary, DropDownDiaryCategory, DropDownDiaryInOut
WHERE Diary.DiaryInOut = DropDownDiaryInOut.DDDiaryInOutID
AND Diary.DiaryCategory = DropDownDiaryCategory.DiaryCategoryID
AND DiaryType = 1

AND DiaryDate = 17/09/2007

ORDER BY DiaryTime ASC, DiaryNote ASC

This works fine in ms access, HOWEVER it does not work in SQL 2000..

any ideas whats going on?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 09:35:58

SELECT * FROM Diary
inner join DropDownDiaryCategory ON Diary.DiaryCategory = DropDownDiaryCategory.DiaryCategoryID
inner join DropDownDiaryInOut ON Diary.DiaryInOut = DropDownDiaryInOut.DDDiaryInOutID
WHERE DiaryType = 1

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-24 : 09:39:10

quote:
Originally posted by Peso

SELECT * FROM Diary
inner join DropDownDiaryCategory ON Diary.DiaryCategory = DropDownDiaryCategory.DiaryCategoryID
inner join DropDownDiaryInOut ON Diary.DiaryInOut = DropDownDiaryInOut.DDDiaryInOutID
WHERE DiaryType = 1 and Diary.DiaryDate = '20070917'

E 12°55'05.25"
N 56°04'39.16"

Madhivanan

Failing to plan is Planning to fail

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-09-24 : 09:45:33

its a date issue...
is there any UDF i could wrap my date around - i pass this in dynamically in my ADO string.

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-09-24 : 09:51:37

perhaps by using cast? any ideas?

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 09:58:29

AND DiaryDate = 17/09/2007

won't work. That matches the date "17 divided by 9 divided by 2007", which is clearly not what you want!

If you can quote the date

AND DiaryDate = '17/09/2007'

then you can "force" SQL Server to consider all such dates as DMY, if that would help? (use SET DATEFORMAT DMY)

Or you can use

CONVERT(datetime, '17/09/2007', 103)

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-24 : 13:51:38

quote:
Originally posted by alexjamesbrown

its a date issue...
is there any UDF i could wrap my date around - i pass this in dynamically in my ADO string.

Do not do this! use parameters, never concatenate date strings with sql code to execute them -- that is why you are experiencing problems.

simply declare your commandtext property with a @DateTime parameter placeholder and set the parameter before executing the command. Now, you will have the correct data type and you don't have to worry about delimiting or date formatting, and you are safe from sql injection as well.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources