Solved

Return Date value not returning correctly on SQL stored procedure in VS

Posted on 2014-04-03
19
214 Views
Last Modified: 2014-04-07
Hi,
I am executing a stored procedure in my Windows application and then putting the return value of the procedure into a label. The return value is a date.  For some reason instead of returning the date it is returning a time. (value should be 2014-04-02, value shows as 12:00am.

Not sure what I'm doing wrong in my code. The return value shows correctly when I run it in SQL.

Code that is relevant below:

 Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("DARTS.My.MySettings.ACS_TARSConnectionString").ConnectionString)
        Dim cmd As SqlCommand = conn.CreateCommand
        Dim procedure As String = "AutoCompleteLowAmtClaim"
        Dim maxdate As Date
        Dim maxdateparameter As New SqlParameter("@maxrundate", SqlDbType.Date)

        'Try running connecting to database and running stored procedure
        Try
            conn.Open()

            'Call stored procedure
            'Command text is set to stored procedure
            cmd.CommandText = "ExecProcedureLog_GetMaxDate"
            cmd.CommandType = CommandType.StoredProcedure

            'Add Parameters
            cmd.Parameters.Add(New SqlParameter("@storedprocedure", procedure))

            cmd.Parameters.Add("@maxrundate", SqlDbType.Date)
            cmd.Parameters("@maxrundate").Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            'Return Last Run Date for procedure
            Try
                maxdate = CDate((maxdateparameter.Value))
                lblrundate.Text = "Last Run Date:" & CStr(maxdate)


            Catch ex As Exception

            End Try
0
Comment
Question by:imstac73
  • 12
  • 7
19 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39975457
Can you show us your stored procedure code?

Also, following

maxdate = CDate((maxdateparameter.Value))
                lblrundate.Text = "Last Run Date:" & CStr(maxdate)

can be

lblrundate.Text = "Last Run Date: " & maxdateparameter.Value.Tostring
0
 

Author Comment

by:imstac73
ID: 39975611
ALTER PROCEDURE [dbo].[ExecProcedureLog_GetMaxDate]
      -- Add the parameters for the stored procedure here
      @storedprocedure as nvarchar(100),
      @maxrundate as date OUTPUT

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT @maxrundate = Max(executed_date) from dbo.Exec_StoredProcedure_Log
      Where SQLProcedure = @storedprocedure
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39975684
Did you try my suggestion on code change?
0
 

Author Comment

by:imstac73
ID: 39975915
Yes when I changed the code to your selection nothing came up.  The text was blank.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39977752
Just to make sure, executed_date contains the full date and time?
0
 

Author Comment

by:imstac73
ID: 39977942
No executed_date is just date.  Do I need to make it datetime?
0
 

Author Comment

by:imstac73
ID: 39978078
I tried changing to DateTime and am getting the same result.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39978170
I meant the data within the table. Is it currently storing dates as well?

Does it work as expected if you run the stored procedure in SQL Management Studio?
0
 

Author Comment

by:imstac73
ID: 39978880
I changed the datatype in the table from date to datetime.  The stored procedure returns the value as expected when I run in Management Studio.  When I run it in the Windows application it returns 00/01/001.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:imstac73
ID: 39978910
Codecruiser,
I also changed my code to your suggestion:
lblrundate.Text = "Last Run Date: " & maxdateparameter.Value.Tostring

This is not return anything.

I then changed my code to this:
 maxdate = CDate((maxdateparameter.Value))
                lblrundate.Text = CStr(maxdate)

And it returned  12:00:00 AM

In SQL the procedure returns 2014-04-04 10:56:42.190

Not sure where it is getting the former from.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39979411
Try changing your procedure to following


ALTER PROCEDURE [dbo].[ExecProcedureLog_GetMaxDate]
      -- Add the parameters for the stored procedure here
      @storedprocedure as nvarchar(100),
      @maxrundate as Varchar(50) OUTPUT

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT @maxrundate = Convert(Varchar, Max(executed_date), 103) from dbo.Exec_StoredProcedure_Log
      Where SQLProcedure = @storedprocedure

Open in new window

0
 

Author Comment

by:imstac73
ID: 39983007
Do I change the data type in my vb code as well?
0
 

Author Comment

by:imstac73
ID: 39983147
Same result: 12:00:00 am
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39983272
Can you show a screenshot of the data in your table using SQL Management Studio?
0
 

Author Comment

by:imstac73
ID: 39983407
Table data
0
 

Author Comment

by:imstac73
ID: 39983420
Also, when I change my stored procedure to your suggestion (datetime type to varchar) I get the attached results.  The month and the day are switched in the result.  The result should be 4/7/2014.
2014-04-07-10-29-54.jpg
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39983618
Change the query to


SELECT @maxrundate = Convert(Varchar, Max(executed_date), 101) from dbo.Exec_StoredProcedure_Log
      Where SQLProcedure = @storedprocedure
0
 

Author Comment

by:imstac73
ID: 39983867
Okay, that fixed the problem with the date display.  Thanks.  Still don't understand why it's not showing correctly in the Windows app.  I've done tons of searching on the web and still can't find anything that helps.
0
 

Author Comment

by:imstac73
ID: 39984495
Okay, so I decided to just do the SQL query in my code instead of using a stored procedure for it and it worked.  It's not my ideal fix because if I need to make a change I have to make it in 3 places in my windows app but it's working now.

I appreciate all your help on this.  I will give you the points since you had several suggestions for me to try.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now