Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    163

    Question Unanswered: 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 TIA

  2. #2
    Join Date
    Oct 2003
    Location
    Jordan
    Posts
    28
    Hi 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.








    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
    Firas arramli
    Systems Analyst

  3. #3
    Join Date
    Oct 2003
    Posts
    163
    Thanks but there's got to be a simpler solution that this

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, the simple solution is to use the CONVERT function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    The other simple solution is just to format the date when it is sent through initially.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    yes, the simple solution is to use the CONVERT function

    Wish I got paid by the line...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •