August 17, 2021 at 9:34 pm
Hi Gurus,
I am just trying to convert data type on a sql script.
----From this format to
and convert(date,a.myDate) between '2020-11-08' and '2020-11-16'
---- To this format (need help on this one with correct syntax)
and a.myDate = convert (date, between '2020-11-08' and '2020-11-16')
--It works when I don't use 'between' !
and a.myDate = convert (date, '2020-11-08')
Thanks
August 17, 2021 at 9:59 pm
If they're dates, use something like >= @StartDate AND < @EndDate.
No need to do all that expensive conversion.
August 17, 2021 at 10:28 pm
If they're dates, use something like >= @StartDate AND < @EndDate.
No need to do all that expensive conversion.
Agreed
I am an AWS Certified Security Cloud Architect @ iKooru
You can connect with me on Linkedin
Hit me up if you want to do a new product launch
August 18, 2021 at 4:41 am
CONVERT works on a single value at a time. Thus, the query should be more like:
and a.myDate BETWEEN CONVERT(date, '20201108') AND CONVERT(date, '20201116')
The strings will have to be converted to date one way or another: there's no more overhead to doing it yourself rather than letting SQL do it. Besides, the conversion of just two literal strings to a date is such low overhead you wouldn't even be able to measure the time it took to do it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 18, 2021 at 5:41 am
August 18, 2021 at 6:35 pm
Be careful with dates. Expression
>= @StartDate AND < @EndDate --- this will miss all rows within @end date
>= @StartDate AND <= @EndDate --- this will also miss entire @EndDate if it is declared as datetime
will return @StartDate, but not @EndDate. Even <= @EndDate may miss all rows on @EndDate, if your @EndDate is really DateTime.
If your @Dates are actually datetime type, you may want to write something like this:
>= @StartDate AND< @EndDate + 1
Adding 1 takes care of hours, minutes, seconds etc.
Zidar's Theorem: The best code is no code at all...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply