Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Double colon (::) notation in SQL

Tags:

sql

casting

postgresql

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'
like image 809
Pat Avatar asked Apr 22 '11 18:04

Pat

People also ask

Why do we use :: in SQL?

The SQL IN condition (sometimes called the IN operator) allows you to easily test if an expression matches any value in a list of values. It is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Is there double in SQL?

Double TypeDouble types are used when we are not certain of the behavior of our data. The input data is interpreted as floating point integer values. Some data may take more digits to the right of the decimal point. While the storage size of the decimal type is variable, the double type takes 8 bytes storage size.

What is {} in SQL query?

@mario, "The curly braces are for complex variable expressions. They are interpreted by PHP, not by the SQL interface ".

1 Answers

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

like image 91
Michael Fredrickson Avatar answered Sep 27 '22 15:09

Michael Fredrickson
Sign in to Comment

Related questions

Add a column to a table with a default value equal to the value of an existing column
Insert Update stored proc on SQL Server
MYSQL Dump only certain rows
What does "select count(1) from table_name" on any database tables mean?
SQL LEFT JOIN Subquery Alias
MySQL syntax for Join Update
Add Foreign Key relationship between two Databases
Restrict varchar() column to specific values?
Select where count of one field is greater than one
How can I select rows with most recent timestamp for each key value?
SQL standard to escape column names?
SQL set values of one column equal to values of another column in the same table
PostgreSQL unnest() with element number
Can I have H2 autocreate a schema in an in-memory database?
How to create relationships in MySQL
Read SQL Table into C# DataTable
How to get max value of a column using Entity Framework?
Query grants for a table in postgres
Arithmetic overflow error converting numeric to data type numeric
How to SUM two fields within an SQL query

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!