1

Let's take an example.

The DATEDIFF function has this signature:

DATEDIFF ( datepart , startdate , enddate )

datepart must by an SQL keyword. Lets say, for minute, we mai use one of those keywords: minute, mi, m.

If I am going to execute this statement

select datediff(mi,'2012-April-09','2013-April-08')

it will have the exact same effect as this one:

select datediff("mi",'2012-April-09','2013-April-08')

What is happening when those double quotes are met?

What is SQL Server going to do with what in inside double quotes?

tzortzik
  • 4,993
  • 9
  • 57
  • 88
  • What problem are you trying to solve? – Svein Fidjestøl Mar 02 '15 at 09:55
  • possible duplicate of [What is the difference between single and double quotes in SQL?](http://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql) – Heinzi Mar 02 '15 at 09:56
  • 1
    Specifying the first argument in double quotes is not documented. It happens to work (apparently it's parsed as an identifier, so `[mi]` will work as well), but don't use it. There's no guarantee this will continue to work in later versions. – Jeroen Mostert Mar 02 '15 at 10:08
  • 1
    @JeroenMostert It's unlikely to change, since it's part of the SQL standards. The `QUOTED_IDENTIFIER` setting in SQL Server [defaults to on](https://msdn.microsoft.com/en-us/library/ms174393.aspx) – TZHX Mar 02 '15 at 10:26
  • 1
    @TZHX: Quoted identifiers, yes. But `DATEDIFF` is not ANSI SQL (`EXTRACT` is), so that the first argument is an identifier isn't specified anywhere (and is surprising to me). – Jeroen Mostert Mar 02 '15 at 10:31

1 Answers1

0

SQL Server interprets the option enclosed as a string in the same way it would without it, or as if it were enclosed in other characters for that use -- like [ and ] -- and matches it against the list of valid datediff options. If it doesn't match, then it returns an error (Msg 155) at the parsing stage and the query will not run (which may result in other parts of the query not running, if it is part of a larger script) and if it does match, then it continues as normal.

TZHX
  • 5,291
  • 15
  • 47
  • 56
  • If this is the case then why does `select datediff('mi','2012-April-09','2013-April-08')` not work? Double quotes aren't string delimiters in TSQL. – Rhys Jones Mar 02 '15 at 10:15
  • 1
    Because a string is not a valid input to the function. The transition happens in the parsing, where the object in the double quotes is is evaluated as if it didn't have them. In this situation, `"mi"` and `[mi]` are equivalent. – TZHX Mar 02 '15 at 10:19