| Author |
Topic |
|
Cwm
Starting Member
39 Posts |
 Posted - 2009-01-24 : 17:52:25
|
| Hi, I am having issues with using the Between keyword....Here is my sp..sp_Create$Loading$Log$File ( @DateIn datetime, @DateOut datetime )AS select distinct c.TicketNumber, c.ProductName, convert (nvarchar, l.ArrivalTime, 108) as "Arrival Time", convert(nvarchar, c.DateIn, 108) as "Scaled In", convert(nvarchar, c.DateOut, 108) as "Scaled Out", c.OrderNumber from [CompletedOrders] c inner join [LoadSchedule] l on l.OrderNumber = c.OrderNumber where DateIn between @DateIn and @DateOut order by c.TicketNumber RETURNWhen I run this it works ok but when i check the table its missing a couple orders..This is what I get:TicketNumber ProductName Arrival Time Scaled In Scaled Out OrderNumber -------------------------------------------------- -------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ----------- 0015081 WESTERN WAFFLES CANOLA OIL 10:00:00 11:45:00 12:56:00 602476 0015084 SOYA OIL RB 11:00:00 13:32:00 14:41:00 602032 0015085 CANOLA NON HYDRO 16:00:00 12:58:00 14:56:00 601735 0015086 CANOLA OIL RB 14:00:00 14:14:00 15:41:00 602002 I am missing a couple orders that are between this date and time23/01/2009 11:00 AM -- 23/01/2009 9:00 PM.usually i send the datein and dateout as strings instead, but i still get the same orders and am missing some orders.Any idea on what i am doing wrong like why i am missing some orders? or is there a better way to select all order between certain date and times?At the beginning of a shift i take the log in time and at the end of the hift i tke the log out time and pass those both to get all orders between those times..ThanksChris |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Cwm
Starting Member
39 Posts |
Posted - 2009-01-24 : 20:49:20
|
| Hi tkizer, At the moment I can't show what the missing datetimes are from the missing rows because that data is at my work, I do have a mock copy of it at home with me which the data isn't up to date as of yet, but i do have the csv files to update it, i will do that within the next hour and post.ThanksChris |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-25 : 04:41:22
|
| also try changing where condition like thiswhere DateIn between @DateIn and DATEADD(ms,-1,DATEADD(dd,1,@DateOut)) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-25 : 08:05:54
|
Visakh, time resulution is 3.333 ms. 1 ms less will not make a difference.Run this to see of there is a difference.SELECT DATEADD(ms, -1, getdate()), getdate()
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-25 : 12:19:52
|
| yup...that makes sense. then its better to use >= and < as Tara suggested. |
|
|
|
Cwm
Starting Member
39 Posts |
Posted - 2009-01-26 : 17:56:01
|
| Hi tkizer,I tried your suggestion and it worked perfectly. Could you explain what I was doing wrong with the in between statement? and why your suggestion worked?ThanksChris |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Cwm
Starting Member
39 Posts |
Posted - 2009-01-26 : 18:31:02
|
| Hi tkizer,Thanks for the reply. I understand what you are saying, its nice to know why yours worked and mine wasn't.Thanks againChris |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Cwm
Starting Member
39 Posts |
Posted - 2009-01-26 : 18:38:59
|
| Hi I have another question if you don't mid, its off of this topic, i had posted a new topic but since i have you at the moment would you answer this?How do import an xml file into sql using transact?Thanks for all your helpChris |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Cwm
Starting Member
39 Posts |
Posted - 2009-01-26 : 18:52:52
|
| O.k tkizer,Thanks :)Chris |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-26 : 23:52:35
|
| see thishttp://msdn.microsoft.com/en-us/library/ms191184.aspx |
|
|
|
|
I need help with Between statement