
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) |