Returning a datetime value from sql server

[access83]

Hi All,

I've a stored procedure and an input parameter for this procedure as ..

@dateAdded DateTime

I'm using VBA within Access to return this date and display it on a form. However, when I get the value only the date part is returned and not the time. For example, in the database I have 2012/04/25 11:34:12 stored but when I retrieve it with VBA and display it in a form only 25/04/2012 is displayed.

 

[access83]

Ahh post wasn't finished...I pressed enter and it posted :S

The result of the stored procedure is saved to a recordset and then I pull the date value from this recordset.

This is how I call the stored procedure..

cmd.CommandText = "sp_ViewResults"

Does anyone have any suggestions why the time isn't being returned with the date?

Let me know if there's any code you need to see, I'm not sure what to post.

Thanks in advance

 

[mdlueck]

SQL Server has more date types than Access / VBA can handle. It is necessary to update the datatype SQL Server automatically sends it to Access / VBA as so that Access / VBA can handle it without crashing.

  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
    .CommandText = "clsObjQuotesTbl_Insert"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh

    .Parameters("@quotedate").Type = adDBTimeStamp
    If Me.quotedate = vbNullString Then
      .Parameters("@quotedate").Value = Empty
    Else
      .Parameters("@quotedate").Value = Me.quotedate
    End If

    Set adoRS = .Execute()
  End With

Code snippet of sending a date to SQL Server to be stored in a SQL Server date column. First the Paramenters data type is updated, then the value is transferred to the Parameters object.

Note, the call to

.Parameters.Refresh

is what automatically maps SQL Server datatypes to Access / VBA datatypes. And that is what guesses wrong in a couple of cases. It at least guesses wrong for some date/time types, and large VARCHAR columns.

 

[RainLover]

only 25/04/2012 is displayed

Are you sure it is a MS SQL problem.

What is the Format of the Text box in the form.

 

[access83]

Hey,

I think I've done that but in a different way...

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.connectionString = ObjBEDBConnection.getADODBConnectionObj()
cn.Open
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = cn
Dim paramDate As ADODB.parameter
cmd.Parameters.Append paramDate
Set paramDate = cmd.CreateParameter("@dateAdded", adDBTimeStamp, adParamInput, 7, params(5))
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_AddRequest"
.....

Sending the date to the sql server doesn't seem to be a problem when I look at the table its stored the date and time. Only when I try and pull it out of the database and display it on a form in Access am I having issues

 

[access83]

only 25/04/2012 is displayed

Are you sure it is a MS SQL problem.

What is the Format of the Text box in the form.

Hi,

I didn't have any format on the text box. I just tried there some different ones but I only see the option for either date (general, short, long etc) or time (long, short etc). I didn't see the option for a date/time format. Is there one that I'm over looking

 

[RainLover]

Do a search in Access Help.

That will explain the various options.

I think you need to do this before anything else so as to eliminate this as a cause or accept it as a solution.

 

[access83]

Do a search in Access Help.

That will explain the various options.

I think you need to do this before anything else so as to eliminate this as a cause or accept it as a solution.

Hey,

I put in a custom format "dd/mm/yyyy h:nn:ss am/pm" and that seems to have done the trick.

Thanks RainLover and mdlueck for taking the time to help me...much appreciated

 

[RainLover]

A good reminder to go back to basics.

Happy to have helped.

 

[mdlueck]

What is the Format of the Text box in the form.

FYI: I deal with unbound text field controls on record add/edit forms. Therefor no formatting is applied via the Access DB engine. Other than a handful of currency fields, I do not have the form \ text field control set to format in a particular way.

 

[RainLover]

Michael

I am not too sure of the point you are making. I am guessing that you never thought of my solution because you don't tend to think along those lines.

Anyway, the OP got his problem solved and that is the main thing.

FWIIW I use bound form because that is what Access was designed for. Speed of construction is one advantage and not needing to know SQL is another.

If I wanted to go with unbound then I would most likely take the time to learn PHP MySQL.

So while we are on the subject, why have you chosen to go unbound when bound is much simplier.

PS

I am signing off for a while. Have to go to work in 12 hours so I should get some sleep before I leave. Will pop in from time to time if I have internet connection.

 

[mdlueck]

I am not too sure of the point you are making.

Providing the context of the technical environments I work with.

I have never dealt with an "Access + Bound Forms + Stored Procedures" configuration.

So while we are on the subject, why have you chosen to go unbound when bound is much simplier.

The client requested Access 2007 as the development tool. I am striving to make the tool work as best as possible in a Client / Server environment and have the application be VPN friendly for remote use of the application.

Anyway, the OP got his problem solved and that is the main thing.

I agree.

 

[RainLover]

Thanks for that.