SQL DateTime Conversion to many style (Formats)

USE AdventureWorks

SET LANGUAGE us_english

-- Convert string to date using style (format) numbers - sql dates format

-- SQL convert text to date - SQL convert string to date / datetime

SELECT convert(datetime,'15/03/18',3) -- 2018-03-15 00:00:00.000

SELECT convert(datetime,'15.03.18',4) -- 2018-03-15 00:00:00.000

-- Convert datetime to text style (format) list - sql time format

-- SQL Server without century (YY) date styles (there are exceptions!)

-- Generally adding 100 to style number results in century format CCYY / YYYY

SELECT convert(varchar,getdate())    -- Mar 15 2018 10:35AM

SELECT convert(varchar,getdate(),0)  -- Mar 15 2018 10:35AM

SELECT convert(varchar,getdate(),1)  -- 03/15/18

SELECT convert(varchar,getdate(),2)  -- 18.03.15

SELECT convert(varchar,getdate(),3)  -- 15/03/18

SELECT convert(varchar,getdate(),4)  -- 15.03.18

SELECT convert(varchar,getdate(),5)  -- 15-03-18

SELECT convert(varchar,getdate(),6)  -- 15 Mar 18

SELECT convert(varchar,getdate(),7)  -- Mar 15, 18

SELECT convert(varchar,getdate(),8)  -- 10:39:39

SELECT convert(varchar,getdate(),9)  -- Mar 15 2018 10:39:48:373AM

SELECT convert(varchar,getdate(),10) -- 03-15-18

SELECT convert(varchar,getdate(),11) -- 18/03/15

SELECT convert(varchar,getdate(),15) -- 180315

SELECT convert(varchar,getdate(),13) -- 15 Mar 2018 10:41:07:590

SELECT convert(varchar,getdate(),14) -- 10:41:25:903

SELECT convert(varchar,getdate(),20) -- 2018-03-15 10:43:56

SELECT convert(varchar,getdate(),21) -- 2018-03-15 10:44:04.950

SELECT convert(varchar,getdate(),22) -- 03/15/18 10:44:50 AM

SELECT convert(varchar,getdate(),23) -- 2018-03-15

SELECT convert(varchar,getdate(),24) -- 10:45:45

SELECT convert(varchar,getdate(),25) -- 2018-03-15 10:46:11.263

-- T-SQL with century (YYYY or CCYY) datetime styles (formats)

SELECT convert(varchar, getdate(), 100) -- Oct 23 2016 10:22AM (or PM)
SELECT convert(varchar, getdate(), 101) -- 10/23/2016

SELECT convert(varchar, getdate(), 102) -- 2016.10.23

SELECT convert(varchar, getdate(), 103) -- 23/10/2016

SELECT convert(varchar, getdate(), 104) -- 23.10.2016

SELECT convert(varchar, getdate(), 105) -- 23-10-2016

SELECT convert(varchar, getdate(), 106) -- 23 Oct 2016

SELECT convert(varchar, getdate(), 107) -- Oct 23, 2016

SELECT convert(varchar, getdate(), 108) -- 09:10:34

SELECT convert(varchar, getdate(), 109) -- Oct 23 2016 11:10:33:993AM (or PM)

SELECT convert(varchar, getdate(), 110) -- 10-23-2016

SELECT convert(varchar, getdate(), 111) -- 2016/10/23

SELECT convert(varchar, getdate(), 112) -- 20161023

SELECT convert(varchar, getdate(), 113) -- 23 Oct 2016 06:10:55:383

SELECT convert(varchar, getdate(), 114) -- 06:10:55:383(24h)

SELECT convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h)

SELECT convert(varchar, getdate(), 121) -- 2016-10-23 06:10:55.383

SELECT convert(varchar, getdate(), 126) -- 2016-10-23T06:10:55.383

GO

SQL Select

SQL SELECT Statement

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement.

Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name 
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

• table-name is the name of the table from which the information is retrieved.
• column_list includes one or more columns from which data is retrieved.
• The code within the brackets is optional.

database table student_details;

• id	first_name	last_name	age	subject	games
  100	Rahul	Sharma	10	Science	Cricket
  101	Anjali	Bhagwat	12	Maths	Football
  102	Stephen	Fleming	09	Science	Cricket
  103	Shekar	Gowda	18	Maths	Badminton
  104	Priya	Chandra	15	Economics	Chess

  NOTE: These database tables are used here for better explanation of SQL commands. In reality, the tables can have different columns and different data.
  For example, consider the table student_details. To select the first name of all the students the query would be like:
  SELECT first_name FROM student_details;
  NOTE: The commands are not case sensitive. The above SELECT statement can also be written as "select first_name from students_details;"
  You can also retrieve data from more than one column. For example, to select first name and last name of all the students.
  SELECT first_name, last_name FROM student_details;
  You can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement. We will discuss these commands in coming chapters.
  NOTE: In a SQL SELECT statement only SELECT and FROM statements are mandatory. Other clauses like WHERE, ORDER BY, GROUP BY, HAVING are optional.

  How to use expressions in SQL SELECT Statement?

  Expressions combine many arithmetic operators, they can be used in SELECT, WHERE and ORDER BY Clauses of the SQL SELECT Statement.
  Here we will explain how to use expressions in the SQL SELECT Statement. About using expressions in WHERE and ORDER BY clause, they will be explained in their respective sections.
  The operators are evaluated in a specific order of precedence, when more than one arithmetic operator is used in an expression. The order of evaluation is: parentheses, division, multiplication, addition, and subtraction. The evaluation is performed from the left to the right of the expression.
  For example: If we want to display the first and last name of an employee combined together, the SQL Select Statement would be like
  SELECT first_name + ' ' + last_name FROM employee;
  Output:
  first_name + ' ' + last_name
  ---------------------------------Rahul Sharma
  Anjali Bhagwat
  Stephen Fleming
  Shekar Gowda
  Priya Chandra
  You can also provide aliases as below.
  SELECT first_name + ' ' + last_name AS emp_name FROM employee;
  Output:
  emp_name
  -------------Rahul Sharma
  Anjali Bhagwat
  Stephen Fleming
  Shekar Gowda
  Priya Chandra