Solved

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

Posted on 2014-04-03
19
220 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 51
Searching a barcode number within a string. 7 24
Need SSIS project 2 23
Related to SQL Query 5 19
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

740 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