138

I have picked up someone's code, the following is a part of a WHERE clause. What does the double colon indicate here?

b.date_completed >  a.dc::date + INTERVAL '1 DAY 7:20:00'
fra_pero
  • 113
  • 5
Pat
  • 1,381
  • 2
  • 8
  • 3
  • 6
    from [PostgreSQL 9.4.5 Documentation - Chapter 4. SQL Syntax - 4.2.9. Type Casts](http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS) – Mark Mikofski Nov 06 '15 at 05:41
  • 3
    More comprehensive: https://stackoverflow.com/questions/13676816/postgres-data-type-cast/13676871#13676871 – Erwin Brandstetter Mar 22 '19 at 15:08

4 Answers4

97

It varies based on RDBMS, but if I guess right, that's PostgreSQL, in which case the :: converts a.dc to a date type of date.

In other flavors...

In MS SQL Server 2000:

For built-in user-defined functions that return a table, the function name must be specified with a leading double colon (::) to distinguish it from user-defined functions that are not built-in. It also must be specified as a one-part name with no database or owner qualifications. For example: SELECT * FROM ::fn_helpcollations() b.. For built-in user-defined functions that return a scalar value, the function name must be specified as a one-part name (do not specify database or owner). Do not specify a leading double colon (::).

In MS SQL Server 2005:

Double-colons are no longer required for UDFs that return a table.

However...

Double-colons are required in SQL Server 2005 when granting permissions on schemas, certificates, endpoints, and a few other securables.

As well as...

When using User-Defined Types, static methods of the type must be called using the double-colon syntax.

Sources: BOL and Kalen Delaney's Blog

Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
  • 1
    I came across this syntax used with a UDF in the SQL 2008 MCTS practice exam, it's not mentioned anywhere in the book! Thanks for explaining – Sophia Oct 17 '11 at 15:39
47

In this case, it is a cast to a date type. :: is a type cast that can also be represented as CAST(expression AS type).

Michael Dean
  • 1,506
  • 9
  • 11
  • If you know that for sure then maybe you know what RDBMS this script is for? – Andriy M Apr 22 '11 at 19:10
  • Looks like PostgreSQL to me. Do you know of any alternatives that would have the same syntax? – Michael Dean Apr 22 '11 at 19:15
  • I know of some RDBMSes that support the double colon notation, and I know of some others that feature the `INTERVAL` support. This is the first piece of code I've ever seen that combines both. I was just curious. Thanks for the reply. – Andriy M Apr 22 '11 at 19:27
38

It is a CAST operation(cast to a date type).

Example:

SELECT now()::timestamp(0);

Is equivalent to:

SELECT 
    CAST (now() AS timestamp(0));

They both result in casting now() to timestamp in the following format: YYYY-MM-DD HH:MM:SS

techkuz
  • 3,608
  • 5
  • 34
  • 62
3

It is probably a cast, converting a.dc to type date.

IBM Informix Dynamic Server (IDS) would work that way - but the INTERVAL notation at the end is not valid for IDS, so presumably this is in fact another DBMS (probably PostgreSQL).

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278