Converting datetime to Desired format?
-
I want to display DateTime in the DataGrid in the Format "Wednesday,November 30,2005" stored in the sql server datbase as smalldatetime. How to Convert it in the above format from smaldatetime.Query reqired. Thnx in Advance
-
I want to display DateTime in the DataGrid in the Format "Wednesday,November 30,2005" stored in the sql server datbase as smalldatetime. How to Convert it in the above format from smaldatetime.Query reqired. Thnx in Advance
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create FUNCTION dbo.udf_FormatDate (@date datetime, @format varchar(50)) RETURNS VARCHAR(50) AS BEGIN ------------------------------------------------------------------------------------- -- -- Name: FormatDate -- -- Purpose: Mimics the VB Format routine for dates -- -- Parameters: -- @date - Date, Date to be formatted -- @format - String, Template to format the date to -- -- Returns: String, Date formatted to user requested template -- -- Notes: -- 1. Time information is not accounted for in this routine -- -- 2. @format accepts the following values for each section of the date. -- Day -- dddd - Full Day Name -- ddd - Abbreviated Day Name -- dd - Zero Padded Day Number -- d - Day Number -- -- Month -- mmmm - Full Month Name -- mmm - Abbreviated Month Name -- mm - Zero Padded Month Number -- m - Month Number -- -- Year -- yyyy - Full 4 digit year -- yy - 2 digit year -- -- 3. Any unexpected characters will be returned in the string -- -- 4. Assumes database was set up with case-insensitive collation -- -- Example Usage: -- All examples use the following date 2003-07-13 00:00:00 -- -- 'dddd, mmmm dd, yyyy' --> Sunday, July 13, 2003 -- 'mmddyyyy' --> 07132003 -- 'm-d-yy' --> 7-13-03 -- 'mm/dd/yyyy' --> 07/13/2003 -- ------------------------------------------------------------------------------------- DECLARE @pos AS INTEGER DECLARE @char AS VARCHAR(1) -- -- Replace Year -- SET @pos = CHARINDEX('yyyy', @format) WHILE @pos > 0 BEGIN SET @format = STUFF(@format, @pos, 4, DATENAME(yyyy, @date)) --PRINT @format SET @pos = CHARINDEX('yyyy', @format) END SET @pos = CHARINDEX('yy', @format) WHILE @pos > 0 BEGIN SET @format = STUFF(@format, @pos, 2, RIGHT(DATENAME(yyyy, @date) ,2)) --PRINT @format SET @pos = CHARINDEX('yy', @format) END -- -- Replace Month -- SET @pos = CHARINDEX('mmmm', @format) WHILE @pos > 0 BEGIN SET @format = STUFF(@format, @pos, 4, DATENAME(month, @date)) --PRINT @format SET @pos = CHARINDEX('mmmm', @format) END SET @pos = CHARINDEX('mmm', @format) WHILE @pos > 0 BEGIN SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(month, @date), 3)) --PRINT @format SET @pos = CHARINDEX('mmm', @format) END SET @pos = CHARINDEX('mm', @format) WHILE @pos > 0 BEGIN SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + CAST(DATEPART(month, @d -
I want to display DateTime in the DataGrid in the Format "Wednesday,November 30,2005" stored in the sql server datbase as smalldatetime. How to Convert it in the above format from smaldatetime.Query reqired. Thnx in Advance
In the
DataGrid? An ASP.NETDataGrid? o a Windows FormsDataGrid? In any case, you can set aFormatorFormatExpressionproperty for the column (in your app, not in the database). Set the format to "D" to use the long date pattern. See here[^] -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005