Convert DateTime to String in Stored Procedure

Stored Procedure convert date to string

In some cases, it is very hard or complicated to display the time as a string. For instance, when using repeaters, binding is done through the eval so data cannot be manipulated. For this reason it is quite easy to manipulate the time in the stored procedure and return it as a string. Below is an example of the code required to convert date to string:

CREATE PROCEDURE sp_sampleConvertDateToString

AS

SELECT Column1, Column2, CONVERT(nvarchar(100), Column3, 106) AS Column3String

FROM DummyTable

RETURN

it is also possible to return the current date instead of a column from the table, which would looks like:

CREATE PROCEDURE sp_sampleConvertDateToString

AS

SELECT *, CONVERT(nvarchar(100), GetDate(), 106) AS DateNow

FROM DummyTable

RETURN

There are various ways to display the date as string, below is a list of the mostly used options available to display the date and time.

 Date as String  Parameter  Stored Procedure Code
01/01/11  1  SELECT CONVERT(nvarchar(100), getDate(), 1)
01 Jan 11  6  SELECT CONVERT(nvarchar(100), getDate(), 6)
Jan 01, 11  7  SELECT CONVERT(nvarchar(100), getDate(), 7)
Jan 01 2011 0:00PM  100  SELECT CONVERT(nvarchar(100), getDate(), 100)
01 Jan 2011  106  SELECT CONVERT(nvarchar(100), getDate(), 106)
01/01/2011 0:00:00 PM  22  SELECT CONVERT(nvarchar(100), getDate(), 22)

One thought on “Convert DateTime to String in Stored Procedure

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s