MSSQL - Date Format


Web Reference : www.sql-server-helper.com

Standard Date Formats
Date Format SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)		 SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
HH:MM:SS SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
MM-DD-YY SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
DD Mon YYYY HH:MM:SS:MMM(24h) 1 SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM

Popular posts from this blog

SAP CPI - Loop Process Call with SuccessFactor (oData V2)

Image
This example will show you how to fetch data from SuccessFactor (oData V2 Message Protocal) with a specific page size. For example, if your data has 10,000 total records and each record consists of 20 child nodes (the result size is around 10MB), you can fetch data by 500 records per request by using Loop Process Call. # My integration process consists of 1. Content Modifier " Initial Data " which I declare property "fetchData". 2. Loop Process Call " Loop Fetch Data " which calls Local Integration Process " Fetch Data From SF ". 3. Groovy Script which writes data from property "fetchData" in an attachment format. This example will focus on step 2 - Loop Process Call & Local Integration Process. # Local Integration Process "Fetch Data from SF".  How does it work? 1. Use Request Reply to call SuccessFactor to get data 500 records per request. 2. Use Groovy Script to get some fields and return in XML format. 3. Use XML to CS...
Read more

Setting IntelliJ IDEA to run Groovy Script

Image
  1. Download Groovy SDK From https://groovy.apache.org/download.html or a specific version at  https://groovy.jfrog.io/artifactory/dist-release-local/groovy-zips/ Ex. Version 3.0.8 visit at https://groovy.jfrog.io/artifactory/dist-release-local/groovy-zips/apache-groovy-binary-3.0.8.zip 2. Unzip your download file. You can store it in the directory you need. 3. Open IntelliJ IDEA Application. 4. Create a new Project. In this step, you can create New Project and follow the default configuration or you can create an Empty Project and use a manual configuration. In this document will choose the Empty Project name “Groovy-Script”. 5. Right click on the project name and choose “Open Modul Settings” 6. The Project Structure window will be open. Choose Module in the left panel. On the right panel, please make sure your setting is the same as the below picture. -      Module SDK is OpenJDK or up to your java version on your computer -      G...
Read more

C# - BASE64 to Image

public void ConvertBase64ToImage(String strBASE64) {      String _outputFile = @"c:\temp\test.jpg";      try      {         Byte[] _imageBytes = Convert.FromBase64String(strBASE64);         MemoryStream ms = new MemoryStream(_imageBytes, 0, _imageBytes.Length);         // Convert byte[] to Image         ms.Write(_imageBytes, 0, _imageBytes.Length);         Image _image = Image.FromStream(ms, true);         _image.Save(_outputFile);         MessageBox.Show("save image success");      }      catch (Exception ex)      {      ...
Read more