I am using the following variables date values varchar(8) in the below query with CTE.
declare @startdate as varchar(8)
declare @enddate as varchar(8)
set @startdate = '04/01/16' ---format mm/dd/yy
set @enddate = '04/11/16' ---format mm/dd/yy
With RM_DATASET
as
(
select progid, rmloggeddate from tab_ccsnetrm
where ProgID <> 70
and (progid = 71)
and rmloggeddate >= CONVERT(datetime, @startdate, 1)
and rmloggeddate < CONVERT(datetime, dateadd(day, 1,@enddate), 1)
)
select * from RM_DATASET
when i pass date values without variables then is working fine.
getting this error msg:
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
You would be much better advised to use a DATE datatype (or DATETIME).
String dates are ambiguous. Yes you can convert them with parameters that ensure that d-m-y or m-d-y style is assumed, but I would suggest that you convert to DATE outside the query, not inside it, as I think there is a better chance that the optimiser will choose a more efficient query plan, and also there is no chance that something, somewhere, uses the VARCHAR date @variable assuming that it is a DATE datatype - in particular because you are naming you variable @xxxDate when in fact it is a VARCHAR containing a String value (which may very well not be a valid date)
declare @startdate as DATE
declare @enddate as DATE
set @startdate = CONVERT(datetime, '04/01/16', 1) ---format mm/dd/yy
set @enddate = CONVERT(datetime, '04/11/16', 1) ---format mm/dd/yy
With RM_DATASET
as
(
select progid, rmloggeddate from tab_ccsnetrm
where ProgID <> 70
and (progid = 71)
and rmloggeddate >= @startdate
and rmloggeddate < dateadd(day, 1,@enddate) --- or just: @enddate + 1
Being as old as the hills I've never had to use ";" ... except in FRONT of one of those newfangled WITH statements in a CTE
Every language I have ever used has needed some sort of statement terminator, so I suppose I should not be surprised that SQL would prefer them. Trouble is, not having had to use them in the past is now an annoyance when I have to. For example, if I want to add a line to the ORDER BY on a SELECT I have to "move" the ";" - or I have to use a coding style where I put the ";" at the start of each statement, or on a line by itself, both of which looks a bit weird
So I suppose I will avoid using them until I have to ...
... although ... I wonder if the Parser is faster when it has a statement terminator present, instead of having to "back up" when it detects that the statement has naturally terminated?
100% agree with that ... I had made the assumption (possibly wrongly!!) that the @startdate etc. were actually coming from an APP/similar that was presenting in mm/dd/yy format, and as such needed to be converted in SQL - which happens of course - and were only show as CONSTs here for the sake of a worked example.
But in the event that the date is originated as a constant, in SQL, it should definitely be formatted "yyyymmdd" and then no ambiguity worries regardless of server locale settings and the language of the currently logged in user ... and all those GotChas just waiting in the wings to bite someone
I'm still concerned that the string-dates are getting into the actual query WHERE clause, because I feel very strongly they should be typed ass date / datetime by that point, not still lurking around as string dates.
