MS SQL DateTime convert examples

As I'm always searching the web with datetime conversions in SQL, I thought I'd post them here finally for reference. The function convert is used here, also found on MSDN.
The beneath functions are for string to datetime conversions.

For datetime to string conversions use varchar instead, e.g.:
SELECT convert(varchar, datetimeField, 105)

-- MSSQL Server string to date conversion - datetime string format sql server -- MSSQL string to datetime conversion - convert char to date sql server SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM) SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000 -- Without century (yy) string date conversion - convert string to datetime SELECT convert(datetime, 'Oct 23 12 11:01AM',     0) -- mon dd yy hh:mmAM (or PM) SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000    -- Convert string to datetime sql - convert string to date sql - sql dates format -- T-SQL convert string to datetime - SQL Server convert string to date SELECT convert(datetime, '10/23/2016',          101) -- mm/dd/yyyy SELECT convert(datetime, '2016.10.23',          102) -- yyyy.mm.dd SELECT convert(datetime, '23/10/2016',          103) -- dd/mm/yyyy SELECT convert(datetime, '23.10.2016',          104) -- dd.mm.yyyy SELECT convert(datetime, '23-10-2016',          105) -- dd-mm-yyyy -- mon types are nondeterministic conversions, dependent on language setting SELECT convert(datetime, '23 OCT 2016',         106) -- dd mon yyyy SELECT convert(datetime, 'Oct 23, 2016',        107) -- mon dd, yyyy -- 2016-10-23 00:00:00.000 SELECT convert(datetime, '20:10:44',            108) -- hh:mm:ss -- 1900-01-01 20:10:44.000 -- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109) -- 2016-10-23 11:02:44.013 SELECT convert(datetime, '10-23-2016',          110) -- mm-dd-yyyy SELECT convert(datetime, '2016/10/23',          111) -- yyyy/mm/dd SELECT convert(datetime, '20161023',            112) -- yyyymmdd -- 2016-10-23 00:00:00.000 SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm -- 2016-10-23 11:02:07.577 SELECT convert(datetime, '20:10:25:300',             114) -- hh:mm:ss:mmm(24h) -- 1900-01-01 20:10:25.300 SELECT convert(datetime, '2016-10-23 20:44:11',      120) -- yyyy-mm-dd hh:mm:ss(24h) -- 2016-10-23 20:44:11.000 SELECT convert(datetime, '2016-10-23 20:44:11.500',  121) -- yyyy-mm-dd hh:mm:ss.mmm -- 2016-10-23 20:44:11.500 SELECT convert(datetime, '2008-10-23T18:52:47.513',  126) -- yyyy-mm-ddThh:mm:ss.mmm -- 2008-10-23 18:52:47.513 -- Convert DDMMYYYY format to datetime SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105) -- 2016-01-31 00:00:00.000 -- SQL string to datetime conversion without century - some exceptions SELECT convert(datetime, '10/23/16',          1)                  -- mm/dd/yy SELECT convert(datetime, '16.10.23',          2)                  -- yy.mm.dd SELECT convert(datetime, '23/10/16',          3)                  -- dd/mm/yy SELECT convert(datetime, '23.10.16',          4)                  -- dd.mm.yy SELECT convert(datetime, '23-10-16',          5)                  -- dd-mm-yy SELECT convert(datetime, '23 OCT 16',         6)                  -- dd mon yy SELECT convert(datetime, 'Oct 23, 16',        7)                  -- mon dd, yy SELECT convert(datetime, '20:10:44',          8)                  -- hh:mm:ss SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) SELECT convert(datetime, '10-23-16',          10)                 -- mm-dd-yy SELECT convert(datetime, '16/10/23',          11)                 -- yy/mm/dd SELECT convert(datetime, '161023',            12)                 -- yymmdd SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13)        -- dd mon yy hh:mm:ss:mmm SELECT convert(datetime, '20:10:25:300',        14)           -- hh:mm:ss:mmm(24h) SELECT convert(datetime, '2016-10-23 20:44:11',20)            -- yyyy-mm-dd hh:mm:ss(24h) SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)       -- yyyy-mm-dd hh:mm:ss.mmm

MS TechEd NA 2012 - Keynote day 1

Yesterday the Microsoft TechEd North America started with the day 1 Keynote, that is,  if you skip the Pre-Con's that were already given on sunday.

I've followed the live stream of the Keynote and have put some points together, you can watch the whole video here: Keynote day 1. The video starts automatically at 1:29:40 with Satya Nadella.

For myself Amir Netz was amazing with his PowerView demo!

@1:29:40

Satya Nadella - President Server and Tools

• Virtualization

• Workload > sharing of resources

• Powershell > automate anything
• 2400 cmdlets

 @1:46:50

Jeff Woolsey - Principal Program Manager

Windows Server Virtualization

• Server 2012 / HyperV, some numbers:
• 320 logical processors per server
• 4 TB per server
• 64 virtual processors per vm
• 64 TB per virtual disk
• 64 nodes, 4k VM's
• IOMeter: 985.000 I/O per sec from 1 virtual machine

• Network Virtualization 

@1:59:50

Satya Nadella

• Video of introduction of  Windows Server 2012

@2:03:20

Mark Russinovich - Windows Azure

• Infrastructure as a service
• Easy creation of new VM's

@2:16:10

Satya Nadella

• Modern datacenter
• Server 2012 / Azure

• Modern apps (lifecycle)
• Personal
• Social (sharing)
• Intelligent
• Tools (VS2012) 

@2:22:00

Scott Guthrie - Corp. VP  Azure App Platform

• VS 2012: ASP.Net MVC 4 project
• Mobile plug-in for testing
• Very funny part @2:31:00
• Integration with TFS to Azure website
• Automative build and run unit-test, when positive deploy to Azure

@2:32:35

Jason Zander - Corp. VP Visual Studio

• Metro style apps
• VS LigthSwitch
• Odata

• Additional support
• jQuery
• iPad emulator

• Agile development
• System Center closely integrated with VS to debug and adjust problems and switch from developer to IT-pro.
• @2:47:45 demo video of ING Direct Australia

@2:49:15

Satya Nadella

• Big Data

@2:53:20

Amir Netz - SQL Server (Power View demo)

Technical Fellow

• Analyse (sentiment of) tweets to predict movie revenues and needed allocation of theaters
• 12.4 mln tweets of movies
• 1st week sales
• 2nd week sales

• Manage brands (awareness of actors)
• Sentiment of brand
• Effect / impact of events on brands (like oscar nominations)
• Small brands have major effect
• Established brands (Brad Pitt) have small effect

@3:04:00

Satya Nadella

• Summarisation & Conclusion

Power Pivot Security Architecture Diagram

This morning (thanks to my colleague Bas) I came across a nice diagram of an end-to-end view of all the related components of PowerPivot (for Excel and for SharePoint) for SQL Server 2008 R2.

Included in this diagram are:

• Service Accounts
• SharePoint Databases
• Security Hardening
• Automatic Data Refresh
• User Identity Flow
• PowerPivot Permissions Levels

The original document (in Microsoft Visio, PDF, or XPS format) can be downloaded from the MS Technet site (link).

Microsoft TechEd Europe 2012

I'm proud to announce I am one of the already 2.5k+ attendees at the TechEd Europe in Amsterdam next month!

I allready put up my agenda at europe.msteched.com, to attend at least some of the sessions of the following speakers:

- Kasper de Jonge

- Marco Russo

- Alberto Ferrari

- Jen Stirrup

See you all at the TechEd!

Performance difference between DISTINCT and GROUP BY

I was wondering if the following two queries differ in performance when used in SQL Server.

select DISTINCT persNrDV, volgnrDienstverband from medewerker

select persNrDV, volgnrDienstverband from medewerker 
GROUP BY persNrDV, volgnrDienstverband

The goal of both of the above queries is to produce a list of distinct employee numbers and employment numbers from the employee table. The first query uses SELECT DISTINCT to accomplish this task, and the second query uses GROUP BY.

I ran these queries with the Execution Plan option turned on, I found that not only the results are identical, but the Execution Plans are also identical. The SQL Server Query Optimizer has the ability to decipher each query, determining what the ultimate results are, and to produce the most efficient Execution Plan possible.

So my conclusion is that there is no performance advantage using one form of the query over the other.