August 4, 2005 at 6:57 am
What was strange was i did a ISDATE and some of the values came back as 0's and some 1's.
The data is inserted into the database using an xml package, there is actually already a dateTime field within it. I thought it wouldn't work carrying out a search on the dates without using wildcards. E.g 10/07/2005*
However it seems to be working. The dates in this column are in the format "08/06/2005 15:44:00", which doesn't actually represent any of the date formats, apart from slightly resembling number 130 (dd mon yyyy hh:mi:ss:mmmAM).
I'm going to tidy up everything (as i have so many different versions of everything now after testing) i've done and see if it still works. I'll post back if it doesn't work. I think i'm going to spend the next week working with this SQL Server date stuff - looks like there's a hell of a lot of interesting things to learn here.
Thanks for helping everyone, much appreciated.
Dave
August 4, 2005 at 7:07 am
A DateTime column holds just that (as a floating point numeric). It can be displayed in various ways but it is not held like that. You can't do a wildcard search on a DateTime because it is not a string. Searching on '30/06/2005*' is searching on 38531.0000* which is meaningless (well, a syntax error!).
Do remember the time (decimal) part when searching on DateTimes. Searching using a pure date only will return records for midnight.
To reemphasise, internally, DateTimes are numbers. They are displayed as strings to be user-friendly but trying to manipulate them as strings is both inefficient and causes all sorts of troubles with formatting and regional settings.
August 4, 2005 at 7:24 am
The only problem i'm noticing now is....
If if put start date = 01/06/2005 and end date as 30/06/2005, then it only includes dates upto the 29/06/2005.
To get the 30th June i need to put the end date as 01/07/2005 (but obviously the 1st July will not be included in the search)
I'm not sure why this is though
August 4, 2005 at 7:27 am
See my previous post. You are dealing with a DateTIME. By entering a pure date, you are saying that the time is midnight (at the start of the day) so you are effectively excluding that day.
You can use the DATEADD() function to adjust the input dates.
August 4, 2005 at 8:06 am
So just to change my code completely, the working code is now....
SELECT C.category, C.enquiryDescription, C.resolution, C.startDateTime, C.endDateTime, C.totalDateDiff, C.dateOnlyStart, C.dateOnlyEnd, C.MF_SR_ID
FROM myForms.myUser.CallLogRevenuesBACKUP20050802 C
WHERE (C.MF_SR_CTS Between ? And ?)
This requires the dateAdd though, would you be able to help me with this one please, i'm not too sure here
Thanks
Dave
August 4, 2005 at 8:13 am
SELECT C.category, C.enquiryDescription, C.resolution, C.startDateTime, C.endDateTime, C.totalDateDiff, C.dateOnlyStart, C.dateOnlyEnd, C.MF_SR_ID
FROM myForms.myUser.CallLogRevenuesBACKUP20050802 C
WHERE (C.MF_SR_CTS Between ? And DATEADD(ms, 86397, ?))
This adds all but 3 milliseconds to the day (minimum time resolution is 3ms).
In case you don't have Books Online, the possible time periods (first parameter) are :
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
The number may be positive or negative.
August 4, 2005 at 8:50 am
Try this out...
-- drop table #TestTimes
CREATE TABLE #TestTimes (UID INT Identity(1,1) NOT NULL,
CreateDate DateTime NOT NULL) ON 'PRIMARY'
DECLARE @Today DateTime,
@i int
SET @Today = GetDate()
SET @i = 1
WHILE @i < 11
BEGIN
INSERT INTO #TestTimes
(CreateDate)
VALUES(@Today)
BEGIN
WAITFOR DELAY '000:00:02' -- 2 second delay
SET @Today = DateAdd(hh, @i, DateAdd(dd,@i, GetDate())) -- ADD 1 Day and 1 Hour to @Today
END
SET @i = @i + 1
END
-- first query is really asking
-- between 2005-08-04 00:00:00.000 AND 2005-08-13 00:00:00.000
-- so we miss out on the value 2005-08-13 18:19:02.073
SELECT *
from #TestTimes
WHERE CreateDate BETWEEN '8/4/2005' and '8/13/2005'
-- but if we compare all rows to midnite of that day, we get all the rows..
-- BETWEEN is inclusive, so we will now get all 10 rows
SELECT *
from #TestTimes
WHERE DATEADD(d,DATEDIFF(d,0, CreateDate ),0) BETWEEN '8/4/2005' and '8/13/2005'
-- or find any rows Created today...
SELECT *
from #TestTimes
WHERE DATEADD(d,DATEDIFF(d,0, CreateDate ),0) = DATEADD(d,DATEDIFF(d,0, GetDate() ),0)
August 4, 2005 at 8:59 am
I'm trying out this code stewart, but it doesn't seem to add the day onto the end date...
SELECT C.category, C.enquiryDescription, C.resolution, C.startDateTime, C.endDateTime, C.totalDateDiff, C.dateOnlyStart, C.dateOnlyEnd, C.MF_SR_ID
FROM myForms.myUser.CallLogRevenuesBACKUP20050802 C
WHERE (C.MF_SR_CTS Between ? And DATEADD(ms, 86397, ?))
I really don't know why this is
Thanks
Dave
August 4, 2005 at 9:04 am
I think we're into debugging here. Can you write a script to put the passed parameters into a test table so that you can be sure of what's coming in and what effect the DATEADD() is having? As far as I can see, it should be working but I have no exoerience of MS Query so I easily could be missing something.
August 4, 2005 at 9:11 am
I'll have to actually head off, but i will have a look into that tomorrow. But how do i go about doing this script.
Thank you for helping me though, i do feel as if i am getting there
Dave
August 4, 2005 at 10:14 am
Something like :
CREATE TABLE dbo.Test (Param1 DateTime NULL, Param2 DateTime NULL)
INSERT INTO dbo.Test (Param1, Param2) VALUES (?, ?)
This assumes that your parameters are DateTime data type. If they are strings, you need to modify the table definition accordingly. (It may be that your problem now is a data type mismatch).
August 9, 2005 at 2:39 am
Hi, sorry for not getting back to you sooner.
I placed in the following code.....
CREATE TABLE dbo.Test (Param1 DateTime NULL, Param2 DateTime NULL)
INSERT INTO dbo.Test (Param1, Param2) VALUES (01/07/2005, 01/07/2005)
and got the following values in the table.
01/01/1900 and 01/01/1900
But this doesn't seem right does it?
Thanks
David
August 9, 2005 at 2:44 am
A masterly understatement! 🙂
It looks like its to do with MS Query but I can't help you there. If you don't get a reply from someone else in this thread, it might be worth starting a new one on 'Using Parameters in MS Query'.
Good luck!
August 9, 2005 at 2:51 am
Stewart
I just wanted to say thanks for all your help.
At least i can get the system working by adding a day to the end date if the worst comes to the worst. But i'll see if anyone knows anything about this and keep working on it.
Thanks again mate
Dave
August 9, 2005 at 4:35 am
Stewart
I was just in the middle of writing out a post when i decided to work out how many miliseconds were in a day. The answer is 86400000. So the code you provided me with actually worked. You added 86397 miliseconds.
So the working code is now....
SELECT C.category, C.enquiryDescription, C.resolution, C.startDateTime, C.endDateTime, C.totalDateDiff, C.dateOnlyStart, C.dateOnlyEnd, C.MF_SR_ID
FROM Int.myData.CallLogRevenuesBACKUP20050802 C
WHERE (C.MF_SR_CTS Between ? And DATEADD(ms,86400010,?))
So its all working good now.
Cheers mate
Dave
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply