Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

CONVERT Function Date Formats - SQL Server

RSS
Modified on Fri, Jul 08, 2011, 11:02 AM by Administrator Categorized as SQL Server
When using the CONVERT function to convert a date to a text string, the CONVERT()function has a third parameter to specify the date format. The available values are listed in the table below. Note that in many cases, the difference between the format codes for a 2-digit year and a 4-digit year is 100. For example, format code 1 yields a format of MM/dd/yy, while a format code of 101 yields MM/dd/yyyy.

The CONVERT() function with the format code parameter uses the following syntax.

CONVERT(varchar(50), '2007-01-02 14:02:46.345', fmt)

FmtSampleSample + 100GenericGeneric + 100
0Jan 2 2007 2:02PMJan 2 2007 2:02PMMMM dd yyyy hh:mm:ssTTMMM dd yyyy hh:mm:ssTT
101/02/0701/02/2007MM/dd/yyMM/dd/yyyy
207.01.022007.01.02yy.MM.ddyyyy.MM.dd
302/01/0702/01/2007dd/MM/yydd/MM/yyyy
402.01.0702.01.2007dd.MM.yydd.MM.yyyy
502-01-0702-01-2007dd-MM-yydd-MM-yyyy
602 Jan 0702 Jan 2007dd MMM yydd MMM yyyy
7Jan 02, 07Jan 02, 2007MMM dd, yyMMM dd, yyyy
814:02:4614:02:46hh:mm:sshh:mm:ss
9Jan 2 2007 2:02:46:347PMJan 2 2007 2:02:46:347PMMMM dd yyyy hh:mm:ss:fffTTMMM dd yyyy hh:mm:ss:fffTT
1001-02-0701-02-2007MM-dd-yyMM-dd-yyyy
1107/01/022007/01/02yy/MM/ddyyyy/MM/dd
1207010220070102yyMMddyyyyMMdd
1302 Jan 2007 14:02:46:34702 Jan 2007 14:02:46:347dd MMM yyyy hh:mm:ss:fffdd MMM yyyy hh:mm:ss:fff
1414:02:46:34714:02:46:347hh:mm:ss:fffhh:mm:ss:fff
202007-01-02 14:02:462007-01-02 14:02:46yyyy-mm-dd hh:mm:ssyyyy-mm-dd hh:mm:ss
212007-01-02 14:02:46.3472007-01-02 14:02:46.347yyyy-mm-dd hh:mm:ss.fffyyyy-mm-dd hh:mm:ss.fff

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.