Double colon (::) notation in SQL

what does * mean in sql
sql colon
what does mean in sql
what is s in sql
sql mean
sql
sign in sql
sql colon meaning

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'

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

Documentation: 7.3: SQL Syntax, Double colon operator is used in postgresql for type casting, for example: SELECT last_login::DATE FROM person WHERE login='foo';. Sql-formatter in its​  Double-dot notation follows this format: <NPS_DatabaseName>..<NPS_ObjectName> The two dots indicate that the schema name is not specified. How such a statement is interpreted depends on the setting of the SQL_COMPAT global variable: When SQL_COMPAT='NPS', the statement is interpreted as: <SchemaName>.<ObjectName>

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).

fixed formatting of double colon operator (used in postgresql and , If you are new to SQL programming, here is a handy reference guide for character operators, syntax, and more. String literals are in 'single quotes' and “​double quotes” denote identifiers, like table and column names. postgres double colon ; postgres :: :: I tried the following searches in the postgres docs search button . double colon ; double colon cast :: This was almost embarrassing to ask on SO, but I figured Google will hopefully see this answer for other people in the future.

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

SQL Cheat Sheet and Query Syntax| Sisense, Microsoft SQL Server articles, forums and blogs for database to some systax usages for SQL Server that use a double colon for something. Though you might not guess it from the GRANT syntax diagram, the old-school They have sent me an SQL statement that joins tables between the two databases. There are places where they have used a double dot notation. I have never seen this before. Please see the SQL statement below (this is not the statement they sent me): select * from database2..Person The statement above is run from database1. Why does it have two dots?

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).

What the heck is :: ? - SQL Server Forums, Such a method might be invoked like this: laserdisc: ; length (vl.movie_info, v2.​movie_info) The double-colon notation (::) is used in SQL:1999 to specify that  MATLAB - Colon Notation - The colon(:) is one of the most useful operator in MATLAB. It is used to create vectors, subscript arrays, and specify for iterations.

https://social.msdn.microsoft.com/Forums/sqlserver, Whenever you see an address that uses double-colon notation, it won't be instantly obvious how many and which Os are missing. Expanding an address that  Languages making the former choice often use a colon-equals (:=) or ≔ to denote their assignment operator. Languages making the latter choice often use a double equals sign (==) to denote their boolean equality operator. Ironically, this answer is now above Wikipedia when searching for colon equals operator.

Advanced SQL:1999: Understanding Object-Relational and Other , Default Supported SQL syntax - Summary; Syntax - Details delimited identifiers (inside double quotes) are case sensitive Note This function assumes that the SQL statements in the file are separated by a semi-colon+newline combination  Things coming out of the database layer have standardized display formats; dates in SQL use ISO 8601 (yyyy-mm-dd HH:MM:ss.sss), and floating-point numbers use the ANSI/ISO standard E notation.

TCP/IP Foundations, SQL Server uses '+' as a string concatenation operator, for instance. (Of course, the better solution would have been for the poster to simply run the query without the count() to see what it did in the first place instead of posting here.)

Comments
  • from PostgreSQL 9.4.5 Documentation - Chapter 4. SQL Syntax - 4.2.9. Type Casts
  • More comprehensive: stackoverflow.com/questions/13676816/postgres-data-type-cast/…
  • 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
  • If you know that for sure then maybe you know what RDBMS this script is for?
  • Looks like PostgreSQL to me. Do you know of any alternatives that would have the same syntax?
  • 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.