0

I am trying to convert from a varchar field in TSQL to Date -

The error I'm getting is conversion of varchar to date in US m/d/y mm/dd/yy format resulted in an index out of range

This is my query 

select *
from test_revision2
where test_revision2.location ='Downsview' AND
      not exists (select 1
                  from test_bookings2
                  WHERE ((CONVERT(DateTime, startDate, 103) <= CONVERT(DateTime, '11/31/2017 00:00:01', 103)) 
AND (CONVERT(DateTime, endDate, 103) >= CONVERT(DateTime, '11/30/2017 00:00:01', 103))  and
                        test_bookings2.wsid = test_revision2.wsid));
Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
rahulchawla
  • 170
  • 1
  • 3
  • 20
  • 1
    Is there a reason why you are converting the dates on your WHERE clause? – Isaiah3015 Sep 07 '17 at 14:10
  • 3
    The 103 format is `dd/mm/yyyy`, and as far as I know, there's no month number 30 – Lamak Sep 07 '17 at 14:10
  • Thanks for the help @lanak changed it to 101 and realized theres no Nov 31 and it worked – rahulchawla Sep 07 '17 at 14:15
  • 1
    What is startDate and endDate dataype? If it's a datetime, you can eliminate CONVERT in the left side of your conditions (ie startDate<= CONVERT... – etsa Sep 07 '17 at 14:29

0 Answers0