CODEWITHSUNDEEP
sqlsql-serverdatedatetime
B.Tot
B.Tot

Reputation: 23

LEFT Function in SQL Server returns no results

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!

Upvotes: 2

Views: 963

Answers (5)

Salman Arshad
Salman Arshad

Reputation: 272146

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

Upvotes: 3

elizabk
elizabk

Reputation: 480

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'

Upvotes: 0

Adam Wells
Adam Wells

Reputation: 581

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'

Upvotes: 0

Thom A
Thom A

Reputation: 95588

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'

Upvotes: 2

benjamin moskovits
benjamin moskovits

Reputation: 5458

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...

Upvotes: -2

Related Questions