Inserting dates in dd/mm/yyyy format

[Amir]

Hi,

I have a system which uses dd/mm/yyyy date format with AccessXP and SQL
Server 7.
As I've understood by far, the SQL Server always stores dates in mm/dd/yyyy
format, so that in order to insert dates into it, I'm using the following
method. Could you please take a look and see if it's OK?

I'm using the following stored procedure:
CREATE PROCEDURE dbo.AddRecord @DateToInsert varchar(25)
AS

DECLARE @ConvertedDateToInsert smalldatetime

SET @ConvertedDateToInsert = CONVERT(smalldatetime,@DateToInsert,103)

INSERT INTO Table1 (ID_DATE)
VALUES (@ConvertedDateToInsert)

RETURN
GO

=======================
This procedure is called from a form in which there is a text box and a
control button with the following properties:
Text Box properties:
Control Name: txtDateToAdd
Input Mask: 00/00/0000\ 00:00:00;0;_
Format: General Date

=======================
'Command Button code:
Private Sub cmdAddRecord_Click()
Dim strSQL As String

strSQL = "EXEC addrecord " & Quotes(Me.txtDateToAdd.Value)

DoCmd.RunSQL strSQL, True
Me.Requery
Me.Refresh

End Sub

'Quotes function code:
Function Quotes(strOriginalText As String) As String
Quotes = Chr(39) & Replace(strOriginalText, Chr(39), String(2, Chr(39)))
& Chr(39)
End Function

=======================

Does it seem OK to you?

Kind Regards,
Amir.

 

[giorgio rancati]

Hi Amir,

I prefer to use a datetime ISO format because independents to the computer's
and server's localization

----
CREATE PROCEDURE dbo.AddRecord @DateToInsert Datetime
AS

INSERT INTO Table1 (ID_DATE)
VALUES (@DateToInsert )

RETURN
----

'Command Button code:
Private Sub cmdAddRecord_Click()
Dim strSQL As String

strSQL = "EXEC addrecord " + QuotesDate(Me.txtDateToAdd.Value)

DoCmd.RunSQL strSQL, True
Me.Requery
Me.Refresh

End Sub

'QuotesDate function code:
Function QuotesDate(dtOriginalDate As Variant) As String

If IsNull(dtOriginalDate) Then
QuotesDate = "NULL"
Exit Function
End If

Dim strResult As String
strResult = Format(dtOriginalDate, "yyyymmdd hh:nn:ss")
QuotesDate = "'" + Replace(strResult, ".", ":") + "'"

End Function

 

[Amir]

OK,

Thank you very much!

Regards,
Amir.

Hi Amir,

I prefer to use a datetime ISO format because independents to the
computer's
and server's localization

----
CREATE PROCEDURE dbo.AddRecord @DateToInsert Datetime
AS

INSERT INTO Table1 (ID_DATE)
VALUES (@DateToInsert )

RETURN
----

'Command Button code:
Private Sub cmdAddRecord_Click()
Dim strSQL As String

strSQL = "EXEC addrecord " + QuotesDate(Me.txtDateToAdd.Value)

DoCmd.RunSQL strSQL, True
Me.Requery
Me.Refresh

End Sub

'QuotesDate function code:
Function QuotesDate(dtOriginalDate As Variant) As String

If IsNull(dtOriginalDate) Then
QuotesDate = "NULL"
Exit Function
End If

Dim strResult As String
strResult = Format(dtOriginalDate, "yyyymmdd hh:nn:ss")
QuotesDate = "'" + Replace(strResult, ".", ":") + "'"

End Function
----


bye
--
Giorgio Rancati
[Office Access MVP]

Hi,

I have a system which uses dd/mm/yyyy date format with AccessXP and SQL
Server 7.
As I've understood by far, the SQL Server always stores dates in mm/dd/yyyy
format, so that in order to insert dates into it, I'm using the following
method. Could you please take a look and see if it's OK?

I'm using the following stored procedure: {CUT]

Does it seem OK to you?

Kind Regards,
Amir.