CODEWITHSUNDEEP
c#asp.netsql-server
Dharmendra Kumar Singh
Dharmendra Kumar Singh

Reputation: 2991

Time Format in SQL

I m storing datetime from a calendar extendar and storing it in the database.The Format of the datetime is Format="dddd, MMMM dd, yyyy". Then i m displaying this Datetime with other field in a grid view and naming this field as 'CalendarDate'. Currently the CalendarDate in the grid is displaying like "6/29/2012 10:42:35 AM".

I want that the Calendar date will display Date like this:-"6/29/2012 10:42 AM". Only seconds will be removed.Please advise me that how i do this.

The Stored Procedure which i m using now is like this:-

Create procedure St_Proc_GetUserReportforCurrentDayTask 
@userID int 
as            
    Begin            
     set NoCount on;            
     DECLARE @TODAY DATE              
     SET @TODAY = CONVERT(VARCHAR(10), GETDATE(), 111)            
     select Production.CalendarDate as Date, 
            RegionAndProjectInfo.RegionProjectName as Region , 
            County.CountyName as County, 
            WorkType.WorkTypeName as WorkType, 
            Task.TaskName as Task,
            Production.VolumeProcessed as 'Volumes Processed', 
            Production.TimeSpent as 'Duration (HH:MM)' 
     from Production             
     inner join RegionAndProjectInfo            
     on            
     RegionAndProjectInfo.RegionProjectID=Production.RegionProjectID            
     inner join County            
     on             
     County.CountyID=Production.CountyID            
     inner join WorkType            
     on            
     WorkType.WorkTypeID=Production.WorkTypeID            
     inner join Task            
     on            
     Task.TaskID=Production.TaskID            
     where Production.UserID=@userID and CalendarDate >= @TODAY            
    End

Upvotes: 0

Views: 239

Answers (3)

bhupendra patel
bhupendra patel

Reputation: 3179

I would suggest you do it on Front end, using DateTime.Tostring() http://www.geekzilla.co.uk/View00FF7904-B510-468C-A2C8-F859AA20581F.htm . SQL Server should be used mainly for Storing the data. The formatting for display should be left to UI you use. This way you can make better use of SQL Server.

Upvotes: 1

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

Use dataformatstring="{0:g}" in Datagridview for this datafield.

<asp:BoundField HeaderText="CalendarDate" DataField="MyDateColumn"  DataFormatString="{0:g}" >

Upvotes: 1

Gerrie Schenck
Gerrie Schenck

Reputation: 22368

Do your own formatting, by taking out date parts, converting them to varchar, and then stringing them together, for example:

    select
convert(varchar(2), (select DATEPART(month, GETDATE())))
+ '/' +
convert(varchar(2), (select DATEPART(month, GETDATE())))
+ '/' +
convert(varchar(4), (select DATEPART(year, GETDATE())))

As a side note: normally your UI is responsible for formatting a date. Why not return a DateTime object and let the UI handle this?

Upvotes: 0

Related Questions