SQL Server - How to print date in mm/dd/yyyy format using sql function

Asked By Svs Suresh on 24-Feb-12 05:33 AM
Hi experts,

How to print date in mm/dd/yyyy format using sql function
Somesh Yadav replied to Svs Suresh on 24-Feb-12 05:41 AM

The form you are after is listed in the books online documentation.

http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx

For example, try the following:

select convert(varchar,getDate(),120)
select convert(varchar(10),getDate(),120)
Suchit shah replied to Svs Suresh on 24-Feb-12 05:42 AM

The CONVERT() function is a general function for converting data into a new data type. The CONVERT() function can be used to display date/time data in different formats.


CONVERT(VARCHAR(10),GETDATE(),110) /// which give the output like this 11-04-2008

Svs Suresh replied to Suchit shah on 24-Feb-12 05:51 AM
Thanks but what i want is
if i gave

Select dbo.formatdatr('05-12-2011' 'mm/dd/yyyy')

i should get as 12/05/2011

in here dbo.formatdatr is a function name
dipa ahuja replied to Svs Suresh on 24-Feb-12 05:56 AM
SQL table accept the fix format but you can retrieve the format and change to any format you like in front end DateTime dt6 = DateTime.ParseExact(textBox1.Text, "HH:mm:ss", null);
string timeToInsert = dt6.ToString("HH:mm:ss");
    
 
kalpana aparnathi replied to Svs Suresh on 24-Feb-12 05:57 AM
hi,

Try this,

 
Convert(varchar,getdate(),110);

Regards,
Suchit shah replied to Svs Suresh on 24-Feb-12 05:58 AM
Below are the different format for the Convert function which you can use to get your desired output.. you just required to change the option


select * from Annual_File_Upload where Annual_File_Upload.SignatureHashValue is  null

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 function

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 - SQL Server datetime 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

-- YYYYMMDD ISO date format works at any language setting - international standard

SELECT convert(datetime, '20161023')

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

 

-- Style 126 is ISO 8601 format: international standard - works with any language setting

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 - sql server to date / datetime

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

-- 2016-01-31 00:00:00.000

-- SQL Server T-SQL string to datetime conversion without century - some exceptions

-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc

SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default

SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.

SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI

SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR

SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German

SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian

SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.

SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.

SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec

SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.

SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan

SELECT convert(datetime, '161023', 12) -- yymmdd ISO

SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt

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) ODBC can.

SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC

Sandeep Mittal replied to Svs Suresh on 24-Feb-12 11:38 PM
CONVERT function is used to format dates in different formats in Sql Server

Refer below post for the same.

http://www.w3schools.com/sql/func_convert.asp
Svs Suresh replied to Suchit shah on 25-Feb-12 01:13 AM
Thanks a lot it works perfectly
Svs Suresh replied to Sandeep Mittal on 25-Feb-12 01:14 AM
Thanks a lot
Suchit shah replied to Svs Suresh on 25-Feb-12 01:16 AM
YOU ARE WELCOME
help
Convert SQL SERVER db to SQL SERVER CE SQL Server
How can i convert a sql server database to a sql server compact database? - - SQL Server Compact Edition Discussions
Converting a MS Access database to SQL Server SQL Server
Does anyone know how to convert an MS Access database to SQL Server using SQL Server 2005? SQL Server Programming Discussions SQL Server 2005 (1) SQL Server (1) Database (1
Convert SQL Express to SQL Server SQL Server
Hi, Is it possible to convert a SQL Express database to work with SQL Server? We are currently on a deadline to get
SQL SERVER SQL SERVER to MS ACCESS. SQL Server
Looking for recommendatsion. I need to convert a SQL Server database to a MS Access database. Are there any utilities that will copy everything
Hi I want to Convert excel to sql server 2005 How to convert This Thanks In Advance Regards Samarth Patel Hi, when u transfer
convert sql server data to msacess IIS
dear sir / madam How to convert sql server data to ms access. Please help me Thanks in advance IIS ASP Discussions SQL Server 2005 (1) SQL Server (1) ASP.NET (1) Database (1) Recordset (1) Vinodkus (1) Builtin
CAST and CONVERT (Transact-SQL) SQL Server
SQL Server sample code: CAST and CONVERT (Transact-SQL) Syntax for CAST : CAST ( expression AS data_type [ (length ) ]) Syntax for CONVERT : CONVERT ( data_type [ ( length ) ] , expression
Upsizing large Access table to SQL Server SQL Server
I am using SQL Server 2005 Express and want to upsize large tables from MS Access to SQL Server. The upsizing wizard is skipping the large tables without any error. The QueryTimeout value in
how to import data from excel file into the SQL server Use DTS or SSIS You can use the SQL Server Data Transformation Services (DTS) Import Wizard or the SQL Server Import and Export Wizard to
explain the differences between oracle and sql server SQL Server is MicroSoft Product Oracle is Multinational Company am not asking about the companies, sql server