Double colon (::) notation in SQL
SqlPostgresqlCastingSql Problem Overview
Have picked up someone's code and this is a part of a where clause, anyone know what the double colon indicates?
b.date_completed > a.dc::date + INTERVAL '1 DAY 7:20:00'
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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).
Solution 3 - Sql
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
Solution 4 - Sql
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).