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

Hi John

I think you need to use Convert in place of Cast - and use style 120:

  http://msdn.microsoft.com/en-us/library/ms187928.aspx 

/gustav


>>> jwcolby at colbyconsulting.com 09-04-2010 14:29 >>>
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?