SQL query 1

[Cpreston]

Hi

I have the following code which runs fine as a query in SQL but when I try and insert it into Excel I get the following message "The conversion of a varchar data type to a smalldatetime datatype resulted in and out of range value"

SELECT JourneyHeader.JourneyDate, Vehicle.Name, JourneyHeader.DriverName, JourneyHeader.VehicleName,
JourneyHeader.JourneyNumber, JourneyHeader.TotalVolume, JourneyLine.DropNumber, [148-vwOrderHeadCP].DeliveryAddress,
Customer.Name, Customer.County, Customer.PostCode
FROM (((JourneyHeader INNER JOIN JourneyLine ON JourneyHeader.JourneyID = JourneyLine.JourneyID)
INNER JOIN Vehicle ON JourneyHeader.VehicleID = Vehicle.VehicleID)
INNER JOIN [148-vwOrderHeadCP] ON JourneyLine.OrderID = [148-vwOrderHeadCP].OrderID)
INNER JOIN Customer ON [148-vwOrderHeadCP].CustomerID = Customer.CustomerID--WHERE (((JourneyHeader.JourneyDate)=#6/23/2015#));
where (((JourneyHeader.JourneyDate between '2015-06-23 00:00:00' and '2015-06-23 23:59:59')))


I have tried to go into the MSquery from Excel to change the way the dates are asked for but it wont show graphically.

So is there a way for SQL to prompt for 2 dates in the code and if so how

many thanks

 

[gmmastros]

Are you always querying for just one date, but you want any rows for that date regardless of time?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Ideally I would like it to ask for example, between 2 dates, but not sure how to do that in SQL.

Thansk

 

[gmmastros]

What do you get when you run this?

Select Distinct Language From sys.syslogins

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

US_English

 

[gmmastros]

Have you tried using a stored procedure instead?

Create the procedure like this:

Create Procedure dbo.GetJourneyDataForDateRange
	@StartDate SmallDateTime
	@EndDate SmallDateTime
AS
Begin

SELECT  JourneyHeader.JourneyDate, 
        Vehicle.Name, 
        JourneyHeader.DriverName, 
        JourneyHeader.VehicleName, 
        JourneyHeader.JourneyNumber, 
        JourneyHeader.TotalVolume, 
        JourneyLine.DropNumber, 
        [148-vwOrderHeadCP].DeliveryAddress, 
        Customer.Name, 
        Customer.County, 
        Customer.PostCode
FROM    JourneyHeader 
        INNER JOIN JourneyLine 
          ON JourneyHeader.JourneyID = JourneyLine.JourneyID
        INNER JOIN Vehicle 
          ON JourneyHeader.VehicleID = Vehicle.VehicleID
        INNER JOIN [148-vwOrderHeadCP] 
          ON JourneyLine.OrderID = [148-vwOrderHeadCP].OrderID
        INNER JOIN Customer 
          ON [148-vwOrderHeadCP].CustomerID = Customer.CustomerID
WHERE   JourneyHeader.JourneyDate >= @StartDate
        and JourneyHeader.JourneyDate < DateAdd(Day, 1, @EndDate)

End

Then call it from Excel like this:

Exec dbo.GetJourneyDataForDateRange '2015-06-23','2015-06-23'

When you run it like this, you should get all values from the table for that date, regardless of the time.

Notice how I changed the "between" condition to multiple conditions. Since your error message has "smalldatetime" in it, I assume that JourneyHeader.JourneyDate is a small date time data type. The end range of the between has time of 23:59:59. Because this is a small date time, you may get incorrect data. Let me explain...

Small Date Time does not store seconds. So, when you use a time that includes seconds, it is rounded to minute. Proof:

Declare @Test SmallDateTime

Set @Test =  '2015-06-23 23:59:59'
Select @Test, 'With 59 seconds'

Set @Test =  '2015-06-23 23:59:29'
Select @Test, 'With 29 seconds'

If the data type is DateTime, there is similar weirdness with milliseconds (actually... fractional seconds).

The cleanest way to handle this is to separate this in to 2 conditions. So... if you want to get all data for 2015-06-23, then do it like this...

Where DateColumn >= '2015-06-23'
      And DateColumn < '2015-06-24'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Hi

Thanks for the reply. I have created the SP and tried calling it within Excel. I renamed it slightly.

I have the below as command text in the Excel SQL connection property.

Exec dbo."148-GetJourneyDataForDateRange" '2015-03-10','2015-03-10'

It is not pulling data through though, I have done a select query to see if there is data for this date and there is, but I cannot get it to come through in Excel. Any ideas please

Thanks

 

[Cpreston]

Hi

Must have been my connection, I have it working now.

Thanks