LEFT Function in SQL Server returns no results

Question

I am trying to place data corresponding to a certain month into a temp table from an SQL database.

DROP TABLE
    #ComPAIR_Alliance_Table
SELECT
    IMOno
    ,period
    ,[service]
    ,alliances
INTO
    #ComPAIR_Alliance_Table
FROM
    com_COMPAIR.dbo.Data_BlueWaterCapacity_US_2
WHERE
    LEFT(period, 7) = '2015-03'

SELECT
     *
FROM #ComPAIR_Alliance_Table

The period field is in the following format: 2015-03-29 00:00:00.000

However, my code just returns an empty temp table with the right column names but no rows (even though I know for sure rows with that date exist in my table). I have already made sure that the period column is indeed a string by using is.numeric.

Could someone please help me out with what the problem could be?

Thanks!

Is period a string column? or a date/time column? Note that your query is non-SARGable. — Luis Cazares, Feb 21 '19 at 20:34
I also tried using YEAR(period) = 2019 AND MONTH(period) = 09 but this did not work either — B.Tot, Feb 21 '19 at 20:36
You don;t check the datatype using ISNUMERIC. You look at table structure or like `SELECT sql_variant_property(period, 'basetype')` — Salman A, Feb 21 '19 at 20:39

Salman A · Answer 1 · 2019-02-22T07:59:55.273

3

If it is a date/datetime/datetime2 then you can compare it with 2015-03 like:

WHERE period >= '2015-03-01'
AND   preiod <  DATEADD(MONTH, 1, '2015-03-01')

In case there is confusion:

The above will match all March 2015 dates such as 2015-03-31, 2015-03-31 23:59:59 and 2015-03-31 23:59:59.9999999
The above is sargable: the DATEADD part does not depend on the table rows

edited Feb 22 '19 at 07:59

answered Feb 21 '19 at 20:36

Salman A

262,204
82
430
521

And now this has 2 down votes? I'd really like to hear why. If someone does have something to add here, please do share. :) – Thom A Feb 21 '19 at 21:43

score 2 · Accepted Answer · answered Feb 21 '19 at 20:36

2

Guessing Period is a date. If it is, stop treating it like a varchar, it isn't one. If you want values from March 2015 then do:

WHERE period >= '20150301'
  AND period < '20150401'

answered Feb 21 '19 at 20:36

Thom A

88,727
11
45
75

1

Why did this get a downvote? It's the same solution as Salman.A's, which were both posted at the same time. Could someone explain? – Thom A Feb 21 '19 at 21:10

score 0 · Answer 3 · answered Feb 21 '19 at 20:39

LEFT is doing some weird stuff, because LEFT causes an implicit cast to String from Date. You can see this question for more information, but you're getting exactly what you told SQL to get - a join on rows where the left 7 characters of period equal '2015-03' which will not happen, since you're liking comparing against something like 'Jan 01'

score 0 · Answer 4 · answered Feb 21 '19 at 21:04

0

The LEFT function needs to implicitly convert your datetime column to a varchar value to do it's work. SQL Server is choosing the varchar format of the date based on it's internationalization settings. On my server, its Mar 29 2015 12:00AM, and LEFT yields Mar 29. That's why it's not equal to 2015-03.

You should treat your column as a datetime and then perform the comparison using a valid datetime comparison, like this :

WHERE period BETWEEN '1/1/2015' AND '1/31/2015'

answered Feb 21 '19 at 21:04

elizabk

480
2
11

While your answer can lead to right result, it still a locale dependent and relies on language settings. Such format is a safer alternative: `WHERE period BETWEEN '20150101' AND '20150131'` – Alexander Volok Feb 22 '19 at 08:44

score -2 · Answer 5 · answered Feb 21 '19 at 20:34

-2

the date is stored as a date type. You may want to try

where convert(varchar(20), period,121)

which would convert it to string...

answered Feb 21 '19 at 20:34

benjamin moskovits

5,261
1
12
22

2

And prevent any use of an index? That's a pretty bad idea. – Luis Cazares Feb 21 '19 at 20:35

LEFT Function in SQL Server returns no results

5 Answers5