CODEWITHSUNDEEP
datems-accessformat
Kashif
Kashif

Reputation: 4632

How to change/format just the time part of date in non-american format?

I have a date/time field in my CSV file in d/m/yyy h:nn:ss AM format.

CSV is linked in MS Access and I want to convert the above field to dd/m/yy hh:nn format.

Sounds easy, but when I try Format([Date Processed], "dd/m/yy hh:nn" in my sql, it switches the dd and m. So 12/4/2015 (4-Dec-2015) becomes 12/4/15 (12-April-2105)

I do not want to play with my computer's date format as it may break my application. How can I do this within my SQL?

Upvotes: 1

Views: 87

Answers (2)

Fadi
Fadi

Reputation: 3322

Try: Format(CDbl([Date Processed]), "dd/m/yy hh:nn")

Upvotes: 0

Gustav
Gustav

Reputation: 55841

You can use Split in a simple function to shuffle the date parts:

Public Function ReFormat(ByVal Date1 As String) As String

    Dim DateParts   As Variant
    Dim DatePart    As String
    Dim TimePart    As String

    DatePart = Split(Date1, " ", 2)(0)
    TimePart = Split(Date1, " ", 2)(1)
    DateParts = Split(DatePart, "/")

    If UBound(DateParts) = 2 Then
        ReFormat = DateParts(1) & "/" & DateParts(0) & "/" & Format(DateParts(2) Mod 100, "00") & " " & TimePart
    End If

End Function

Upvotes: 1

Related Questions