[dba-SQLServer] [AccessD] SPAM-LOW: Re: [dba-VB] C# Date conversion going in to a stored procedure

Stuart McLachlan stuart at lexacorp.com.pg
Fri Apr 9 08:09:33 CDT 2010


Are PL_DteTimeStart	PL_DteTimeEnd  set up as DateTime or SmallDateTime fields.  If the 
latter, they can only store times to the nearest minute.

-- 
Stuart

On 9 Apr 2010 at 8:29, jwcolby wrote:

> I have sent it in as a string (varchar(100), and I have sent it in as a date time.  Basically in 
> order to check on the format, I immediately send it right back out as an output parameter coming 
> back from the SP.  AFAICT it is converted somewhere in the interface between C# and SQL Server.  It 
> is a date in the format MMM DD YYYY hh mm AM/PM as soon as I examine it inside of SQl Server (in the 
> stored procedure).
> 
> The seconds are gone!  Nothing that I have tried inside of the stored procedure allows me to see the 
> seconds, or rather I can format it to display seconds but they are always 00.  The second 
> information is lost in the trip over to the stored procedure.
> 
> This is the C# side where I set up the parameters:
> 
> sCmdLog.Parameters.Add(new SqlParameter("@DteTimeStart", SqlDbType.DateTime));
> sCmdLog.Parameters["@DteTimeStart"].Value = pSPStart;//.ToString("MM/dd/yyyy HH:mm:ss");
> 
> sCmdLog.Parameters.Add(new SqlParameter("@DteTimeEnd", SqlDbType.DateTime));
> sCmdLog.Parameters["@DteTimeEnd"].Value = pSPEnd;//.ToString("MM/dd/yyyy HH:mm:ss");
> 
> 
> This is the SP side:
> 
> ALTER PROCEDURE [dbo].[_sp_LogProcess]
> 	-- Add the parameters for the stored procedure here
> 	@DBName varchar(50), @TblName varchar(50),
> 	@SPName varchar(50),
> 	@Process varchar(50), @Memo varchar(4000),
> 	@ErrIntOut int, @ErrStrOut varchar(4000),
> 	@RecsAffected int,
> 	@DteTimeStart datetime,
> 	@DteTimeEnd datetime,
> 	@ErrorDesc varchar(4000) output,
> 	@ErrorNo int output,
> 	@SQLStatement varchar(4000) output
> 
> The @DteTimeStart and @DteTimeEnd are the variables of interest.
> 
> The following is what I am using to generate the SQL statement that appends a record into the log table:
> 
> 		SELECT @SQL = 'INSERT INTO [_aDataMaster].[dbo].[tblProcessLog]
>             ([PL_DBName]
>             ,[PL_TblName]
>             ,[PL_StoredProcName]
>             ,[PL_Process]
>             ,[PL_Memo]
>             ,[PL_ErrInt]
>             ,[PL_ErrStr]
>             ,[PL_DteProc]
>             ,[PL_RecsAffected]
>             ,[PL_DteTimeStart]
>             ,[PL_DteTimeEnd])
> 			SELECT '''
> 			 + @DBName + ''' as PL_DBName, '''
> 			 + @TblName + ''' as PL_TblName, '''
> 			 + @SPName + ''' AS PL_StoredProcName, '''
> 			 + @Process + ''' as PL_Process, '''
> 			 + @Memo + ''' as PL_Memo, '
> 			 + cast(@ErrIntOut as varchar) + ' as PL_ErrInt, '''
> 			 + @ErrStrOut + ''' as PL_ErrStr, '''
> 			 + cast(getdate() as varchar) + ''' as PL_DteProc, '''
> 			 + CAST(@RecsAffected as varchar) + ''' AS PL_RecsAddected, '''
> 			 + cast(@DteTimeStart as varchar) + ''' AS PL_DteTimeStart, '''
> 			 + cast(@DteTimeEnd as varchar) + ''' AS PL_DteTimeEnd'
> 
> 
> 
> The following is the record stored by the process:
> 
> 
> PL_ID	PL_DBName	PL_TblName	PL_Process	PL_Memo	PL_ErrInt	PL_ErrStr	PL_DteProc	PL_MS2Process 
> PL_RecsAffected	PL_StoredProcName	PL_DteTimeStart	PL_DteTimeEnd
> 553	PSM11211_test	No TblName specified	Accuzip Export		0	Success	2010-04-08 17:24:00.000	NULL	0 
> _aDataMaster.dbo.sp_AZOut_BCPOutOneFile	2010-04-08 17:24:00.000	2010-04-08 17:24:00.000
> 
> I have tried every combination I could think of and it is just stripping the seconds each and every 
> time.  I have passed in pure varchar at both ends.  I have passed in DateTime at both ends.  I have 
> looked at the data in the param.value back in C# and it shows the seconds portion.  I look in the SP 
>   IMMEDIATELY below the function declaration line and the seconds are gone!
> 
> I am baffled.
> 
> I NEED the seconds part.  I am trying to time how long my other SPs takes to execute, and the start 
> / end times are what is being passed in to this SP to be logged in the table.  The whole logging 
> process is ALMOST useless if I cannot capture the timing data.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Gustav Brock wrote:
> > Hi John
> > 
> > Why not declare the parameter as Date? That will accept values with seconds and milliseconds.
> > 
> > /gustav
> > 
> >>>> jwcolby at colbyconsulting.com 08-04-2010 22:44 >>>
> > I have an issue where I am sending in a date from C# to a stored procedure in SQL Server.  I am 
> > looking at the data on the C# side, clear down into the parameter object.value and the data is a 
> > string which looks like: "12/22/2010 14:23:01".  When it gets into the Varchar(100) on the SQL 
> > Server side (in the stored procedure) the seconds have been stripped off.  I NEED the seconds.  It 
> > appears that SQL Server is "helpfully" noticing that the string is a date and doing a conversion for 
> > me, stripping the seconds in the process.
> > 
> > I have passed the date in as a string, as an actual date and so forth and in all cases, SQL Server 
> > strips off the seconds.
> > 
> > What do I need to do to cause SQL Server to stop "being helpful" and leave my seconds alone?
> > 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list