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