Date format problem

I am using a view which brings purchase related data from my OLTP.

I am using the same view for the source of my production system (which has a mm/dd/yy format) and for my test machine (which has a dd-Mmm-yy format).

By formats I mean theformatsdefinedvia theRegional settings.

The problem is that on the test machine, the dates are giving problems and a date such as today's (i.e. 10, March, 2005) is coming in as 03-October, 2005) - notice that this is due to the data format difference i.e. 03/10/05 versus 10-Mar-05 (or 10/03/05).

Any ideas how I can use the same view but for my test machine I do some sort of conversion to correctly change the format.

Many TIAHi all

I've faced the same issue here, our company uses DD/MM/YYYY date format and one of the computers we had uses MM/DD/YYYY.
to come around this problem we had to think like MS-SQL server. our application must use YYYY-MM-DD so we wrote a function in VB to read the current user date format and change the dates to SQL server format.

to use this function just pass the date and the result will be YYYY-MM-DD.
you can use this function before inserting, updateting or deleting data in VB.


please have fun.:D



Public Function fDate(ByVal xDateIn As String) As String
Dim xReg As New Registry
Dim xRegDateFormat As String
Dim xRegDateSeperator As String
Dim xDay, xMonth, xYear As String
Dim xTmp As Integer
Dim xTmpStr As String


If xDateIn = "" Then Exit Function

xReg.GetKeyValue HKEY_CURRENT_USER, "Control Panel\INTERNATIONAL", "SSHORTDATE", xRegDateFormat
xReg.GetKeyValue HKEY_CURRENT_USER, "Control Panel\INTERNATIONAL", "SDATE", xRegDateSeperator

xDateIn = Trim(Str(CDate(xDateIn)))

xRegDateFormat = UCase(xRegDateFormat)

If InStr(1, xRegDateFormat, "DD") <> 0 Then
Else
xTmp = InStr(1, xRegDateFormat, "D")
xRegDateFormat = Left(xRegDateFormat, xTmp) + "D" + Right(xRegDateFormat, Len(xRegDateFormat) - xTmp)
End If

If InStr(1, xRegDateFormat, "MM") <> 0 Then
Else
xTmp = InStr(1, xRegDateFormat, "M")
xRegDateFormat = Left(xRegDateFormat, xTmp) + "M" + Right(xRegDateFormat, Len(xRegDateFormat) - xTmp)
End If

If InStr(1, xRegDateFormat, "YYYY") <> 0 Then
Else
xTmp = InStr(1, xRegDateFormat, "YY")
xRegDateFormat = Left(xRegDateFormat, xTmp) + "YY" + Right(xRegDateFormat, Len(xRegDateFormat) - xTmp)
End If

'step 01
Select Case Left(xRegDateFormat, 1)
Case "D"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xDay = Left(xDateIn, xTmp - 1)
If xDay < 10 And Len(xDay) = 1 Then
xDay = "0" & xDay
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "M"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xMonth = Left(xDateIn, xTmp - 1)
If xMonth < 10 And Len(xMonth) = 1 Then
xMonth = "0" & xMonth
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "Y"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xYear = Left(xDateIn, xTmp - 1)
If xYear < 100 Then
xYear = "20" & xYear
xDateIn = "20" & xDateIn
xTmp = xTmp + 1
End If
End Select
xDateIn = Mid(xDateIn, xTmp + 1)
xRegDateFormat = Mid(xRegDateFormat, xTmp + 1)

'step 02
Select Case Left(xRegDateFormat, 1)
Case "D"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xDay = Left(xDateIn, xTmp - 1)
If xDay < 10 And Len(xDay) = 1 Then
xDay = "0" & xDay
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "M"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xMonth = Left(xDateIn, xTmp - 1)
If xMonth < 10 And Len(xMonth) = 1 Then
xMonth = "0" & xMonth
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "Y"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xYear = Left(xDateIn, xTmp - 1)
If xYear < 100 Then
xYear = "20" & xYear
xDateIn = "20" & xDateIn
xTmp = xTmp + 1
End If
End Select
xDateIn = Mid(xDateIn, xTmp + 1)
xRegDateFormat = Mid(xRegDateFormat, xTmp + 1)

'step 03
Select Case UCase(Left(xRegDateFormat, 1))
Case "D"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xDay = xDateIn
If xDay < "10" And Len(xDay) = 1 Then xDay = "0" & xDay
Case "M"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xMonth = xDateIn
If xMonth < "10" And Len(xMonth) = 1 Then xMonth = "0" & xMonth
Case "Y"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xYear = xDateIn
If xYear < "100" Then xYear = "20" & xYear
End Select

'fDate = xDay + "/" + xMonth + "/" + xYear
fDate = xYear + "/" + xMonth + "/" + xDay

End Function|||Thanks but there's got to be a simpler solution that this :(|||yes, the simple solution is to use the CONVERT function|||The other simple solution is just to format the date when it is sent through initially.|||yes, the simple solution is to use the CONVERT function

Wish I got paid by the line...