SQL bug

Question

SQL bug

fenm

SSC Enthusiast

Points: 119
More actions
October 24, 2022 at 12:34 pm

#4103591

Hello,
I am trying to run a query but it does not like the date format in two of the strings.
This query works on other colleagues laptops so I am wondering if it is a bug with the laptop.
the problematic string is copied just below:
[DatesInYear].date_1 >= '2021-11-01 00:00:00.000' AND
[DatesInYear].date_1 <= '2021-11-30 00:00:00.000' AND

if i remove the strings above the query runs fine.

the whole query is copied below:

-- This query provides results for each link specified in the links2 table
USE INRIX
IF Object_Id('tempdb..#tmpLinks') is Not Null
DROP TABLE #tmpLinks
SELECT
TM.link_id, Links.Dir, links.[roadClassi],
CASE
--CHANGE TIME IN THE NUMBERS BELOW THIS LINE
when (TM.time_per >= 68 AND TM.time_per <= 71) then '17:00 to 18:00'
--CHANGE TIME IN THE NUMBERS ABOVE THIS LINE
end
as [period],
CAST(links.[length] AS FLOAT) /1000 AS LinkLengthKM,
SUM((CAST(av_jt AS FLOAT)) * no_of_obs)/SUM(no_of_obs) AS averageTimeSecs,
SUM(no_of_obs) AS sampleSize,
CASE WHEN SUM(no_of_obs) = 1 THEN 0
ELSE (ABS(SUM(sum_sq_jt) - SQUARE(SUM(av_jt * no_of_obs))/SUM(no_of_obs))/(SUM(no_of_obs) - 1)) /10000
END
AS linkTimeVarianceSecs
INTO #tmpLinks
--CHANGE YEAR IN THE VIEW / MONTH IN THE TABLE BELOW THIS LINE
FROM ix202111 TM
--CHANGE YEAR IN THE VIEW / MONTH IN THE TABLE ABOVE THIS LINE
INNER JOIN OS_HighwayNetwork_GM_Plus10k_2021_INRIX links ON links.unique_id = TM.link_id
--Enter your meaningful table name in the line below
INNER JOIN [trafficmasterlinks].[dbo].[RegentRoad2021] links2 ON links2.unique_id = TM.link_id
--Enter your meaningful table name in the line above
INNER JOIN DatesInYear ON DatesInYear.date_1 = TM.date_1
WHERE
-- 0 BELOW MEANS BANK HOLS AREN'T INCLUDED, 1 MEANS THEY ARE
DatesInYear.bankHol = 0 AND
-- 0 BELOW MEANS SCHOOL HOLS AREN'T INCLUDED, 1 MEANS THEY ARE
DatesInYear.schoolHoliday = 0 AND
-- CHANGE DATE RANGE START BELOW THIS LINE
[DatesInYear].date_1 >= '2021-11-01 00:00:00.000' AND
[DatesInYear].date_1 <= '2021-11-30 00:00:00.000' AND
--CHANGE DATE RANGE END ABOVE THIS LINE
veh_cls IN(1,2,3,4) and
--CHANGE TIME IN THE NUMBERS BELOW THIS LINE
(time_per >= 68 and time_per <= 71) and
--CHANGE TIME IN THE NUMBERS ABOVE THIS LINE
--CHANGE DAYS IN WEEK BELOW, 1 - 6 IS MON - FRI
datesInYear.[DayOfWeek] > 1 and
datesInYear.[DayOfWeek] < 6
GROUP BY
TM.link_id,
Links.Dir,
links.[length],
links.[roadClassi],
--[Route],
CASE
--CHANGE TIME IN THE NUMBERS BELOW THIS LINE
when (TM.time_per >= 68 AND TM.time_per <= 71) then '17:00 to 18:00'
--CHANGE TIME IN THE NUMBERS ABOVE THIS LINE
end
Select
[period],
[link_id],
[Dir],
[roadClassi],
[averageTimeSecs],
SUM(sampleSize) as [Sample],
LinkLengthKM * 1000 as length_meters,
SUM(LinkLengthKM) / SUM(CAST(averageTimeSecs AS FLOAT)/3600) AS speedKPH,
(SUM(LinkLengthKM) / SUM(CAST(averageTimeSecs AS FLOAT)/3600)) * 0.6214 AS SpeedMPH,
[linkTimeVarianceSecs]
from #tmpLinks
Group by
[Period],
[link_id],
[Dir],
[roadClassi],
[averageTimeSecs],
[LinkLengthKM],
[linkTimeVarianceSecs]
Order by
[Link_id],
[Period]

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What is the error message you receive?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

fenm SSC Enthusiast Points: 119 More actions · Answer 2

Msg 242, Level 16, State 3, Line 8

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The statement has been terminated.

(0 rows affected)

Completion time: 2022-10-24T13:41:09.3645115+01:00

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

OK, what is the datatype of [DatesInYear].date_1?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

fenm SSC Enthusiast Points: 119 More actions · Answer 4

this is the data I am interrogating, thousands of entried but I have copied one for info:

link_id link_ref network date_1 time_per veh_cls data_source no_of_obs av_jt sum_sq_jt id

4000000013232751B 1727255 8 2021-11-11 00:00:00.000 44 2 6 3 16.966 872.25825 223383510

Thom A SSC Guru Points: 99130 More actions · Answer 5

Have you tried using an unambiguous date format? '2021-11-01 00:00:00.000' is ambiguous with datetime. Try '2021-11-01T00:00:00.000' or just '20211101'.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

fenm SSC Enthusiast Points: 119 More actions · Answer 6

date_1 has data type as datetime and system type as datetime

fenm SSC Enthusiast Points: 119 More actions · Answer 7

i am running it now, will keep you posted once it returns results

fenm SSC Enthusiast Points: 119 More actions · Answer 8

fenm

SSC Enthusiast

Points: 119

October 24, 2022 at 12:54 pm

#4103601

it worked, brilliant, thank you very much!!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

Thom A wrote:

Have you tried using an unambiguous date format? '2021-11-01 00:00:00.000' is ambiguous with datetime. Try '2021-11-01T00:00:00.000' or just '20211101'.

Well spotted!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 10

Phil Parkin wrote:

Thom A wrote:
Have you tried using an unambiguous date format? '2021-11-01 00:00:00.000' is ambiguous with datetime. Try '2021-11-01T00:00:00.000' or just '20211101'.
Well spotted!

Not being American teaches you quickly that the format yyyy-MM-dd is probably one of the worst to use in SQL Server, as its behaviour differs both based on the language setting of the LOGIN/USER and the data type. >_<

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

fenm SSC Enthusiast Points: 119 More actions · Answer 11

Thom A wrote:

Phil Parkin wrote:
Thom A wrote:
Have you tried using an unambiguous date format? '2021-11-01 00:00:00.000' is ambiguous with datetime. Try '2021-11-01T00:00:00.000' or just '20211101'.
Well spotted!
Not being American teaches you quickly that the format yyyy-MM-dd is probably one of the worst to use in SQL Server, as its behaviour differs both based on the language setting of the LOGIN/USER and the data type. >_<

how can i change the language setting of the login/user?

Thom A SSC Guru Points: 99130 More actions · Answer 12

fenm wrote:

how can i change the language setting of the login/user?

ALTER LOGIN YourLogin WITH DEFAULT_LANGUAGE = BRITISH;

Obviously changing the name of the LOGIN and language you need appropriately.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ScottPletcher SSC Guru Points: 101098 More actions · Answer 13

You can also use:

'20211130 00:00:00.000'

The only special chars you need are in the time, no - no T.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".