CODEWITHSUNDEEP
sql-server
Randy Minder
Randy Minder

Reputation: 48442

Strange syntax in a Where clause

The following is a simplied version of a query that a reporting tool is sending to our database. I have never seen this syntax before in the Where clause. Can someone tell me what the brackets are doing? And, I assume the 'd' acts as a date cast?

Select
    ch.ContainerID, 
    ch.WorkItemHistoryEventTypeEnumID,
    ch.EventTime,
    ch.ContainerBinName,
    ch.WorkItemSerialNumber,
    ch.Closed
From Wip.vwContainerHistory ch
Where   
   ch.EventTime >= {d '2010-08-09'}

Upvotes: 5

Views: 142

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

This is an ODBC escape sequence for a date type. See http://msdn.microsoft.com/en-us/library/ms187819.aspx

  • d = date only
  • t = time only
  • ts = timestamp (time + date)

Upvotes: 3

Pavel Morshenyuk
Pavel Morshenyuk

Reputation: 11471

See "Supported String Literal Formats for datetime" section in MSDN datetime article.

Your {d 'XXXX-XX-XX'} is ODBC datetime format. ODBC timestamp escape sequences are of the format: { literal_type 'constant_value' }:

literal_type specifies the type of the escape sequence. Timestamps have three literal_type specifiers:

  • d = date only
  • t = time only
  • ts = timestamp (time + date)

'constant_value' is the value of the escape sequence. constant_value must follow these formats for each literal_type.

d > yyyy-mm-dd  
t > hh:mm:ss[.fff]  
ts > yyyy-mm-dd hh:mm:ss[.fff]

Upvotes: 10

Related Questions