How to change value in the SQL

Question

I'm using Stored-Procedure in SQL-Server2008. The implemented code in C# are as follows:

SqlConnection con = new SqlConnection("...");

DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("sp_point", con);
da.SelectCommand = cmd;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
da.Fill(dt);
dataGridView1.DataSource = dt;

The Datetime format in my Database is based on date, hours, minutes, seconds and milliseconds. The columns shows milliseconds too. How should I modify the Code if I want to show the millisecond?

Edit:

My Stored-Procedure :

    USE [Diagnose]
GO
/****** Object:  StoredProcedure [dbo].[sp_point]    Script Date: 02/09/2017 14:04:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_point]

as
SELECT     dbo.Station.StationName, dbo.SPointState.SPointStateName, dbo.Point.PointName, dbo.PointState.PointStateName, dbo.PositionPoint.PositionPointName, 
                      dbo.RouteType.RouteTypeName, dbo.PartOfRoute.PartOfRoute AS PartOfRoute, dbo.PointPacket.PointPacketID AS PointPacketID, dbo.PointPacket.Operator AS Operator, 
                      dbo.PointPacket.BlockedPoint AS BlockedPoint, dbo.PointPacket.WarningLable AS WarningLable, dbo.PointPacket.EmergencyLock AS EmergencyLock, 
                      dbo.PointPacket.SendPointStateFromIO AS SendPointStateFromIO, dbo.PointPacket.SendPointPositionFromIO AS SendPointPositionFromIO, dbo.PointPacket.HealthSendFromIO AS HealthSendFromIO, 
                      dbo.Date.Date AS Date_Time
FROM         dbo.PointPacket INNER JOIN
                      dbo.SPointState ON dbo.PointPacket.SPointState = dbo.SPointState.SPointStateID INNER JOIN
                      dbo.Station ON dbo.PointPacket.StationCode = dbo.Station.StationID INNER JOIN
                      dbo.Date ON dbo.PointPacket.Date = dbo.Date.DateID INNER JOIN
                      dbo.Point ON dbo.PointPacket.PointCode = dbo.Point.PointID INNER JOIN
                      dbo.PointState ON dbo.PointPacket.PointState = dbo.PointState.PointStateID INNER JOIN
                      dbo.PositionPoint ON dbo.PointPacket.PositionPoint = dbo.PositionPoint.PositionPointID INNER JOIN
                      dbo.RouteType ON dbo.PointPacket.RouteType = dbo.RouteType.ID INNER JOIN
                      dbo.PartOfRoute ON dbo.PointPacket.PartOfRoute = dbo.PartOfRoute.ID

select * from PointPacket

Have you tried to run `sp_point` procedure manually? Does it returns milliseconds? If yes - have a look to `DefaultCellStyle.Format` property of column in your datagridview showing that date - probably milliseconds are excluded from column mask. — Andrey Korneyev, Feb 08 '17 at 13:09
you can return that column after convert to varchar from stored procedure . — Ronak Patel, Feb 08 '17 at 13:12
beware of the "sp_" prefix: http://stackoverflow.com/questions/20530211/avoid-naming-user-stored-procedures-sp-or-sp — Tanner, Feb 08 '17 at 13:46
I don't see milliseconds to DefaultCellStyle.Format in my datagridview — Safa, Feb 09 '17 at 09:46

score 3 · Accepted Answer · edited May 23 '17 at 12:08

3

You should change the way data is displayed in the view layer, not data (SQL procedure) or service layer (da.Fill(dt);).

You can customize how a datetime information is displayed in a grid view column as indicated here:

// keep all date/time info until seconds, included
gridView.Columns[dateColIndex].DefaultCellStyle.Format = "MM/dd/yyyy HH:mm:ss";

edited May 23 '17 at 12:08

Community

1
1

answered Feb 09 '17 at 12:00

Alexei - check Codidact

22,016
16
145
164

Thanks ,it's worked and for millisecond `MM/dd/yyyy HH:mm:ss:ff` – Safa Feb 11 '17 at 06:35

score 0 · Answer 2 · answered Feb 09 '17 at 10:45

0

Try to convert that datetime column into varchar format.

answered Feb 09 '17 at 10:45

hdkhardik

662
3
22

How to change value in the SQL

2 Answers2