Categoria: SQL

MySQL DATE_FORMAT

admin0 Comments

DATE_FORMAT(date,format) The following script uses the DATE_FORMAT() function to display different formats. We will use the NOW() function to get the current date/time: DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’) DATE_FORMAT(NOW(),’%m-%d-%Y’) DATE_FORMAT(NOW(),’%d %b %y’) DATE_FORMAT(NOW(),’%d %b %Y %T:%f’) The result would look something like this: Nov 04 2014 11:45 PM 11-04-2014 04 Nov 14 04 Nov 2014 11:45:34:243

Continue Reading

SQL Server, Common Table Expressions

admin0 Comments

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A CTE can be used to: Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions. Substitute for a …

Continue Reading

Recursive Queries using Common Table Expressions (CTE) in SQL Server

admin0 Comments

Given the example , hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. Common Table Expression is just one of those T-SQL enhancements available for SQL Server 2005. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused. Syntax Sample (from Root nodes to Leaf  noes) Sample (From Leaf nodes to Root nodes), where u must specifie the leaves to include/use. !Importante …

Continue Reading