I've a date and time format as mentioned below. I want to add +6:30 to it and then ex

[veneeth]

I've a date and time format as mentioned below. I want to add +6:30 to it and then extract date and time into two different columns after adding the time.

Sample data

2014-01-08 00:38:57.000
2014-06-20 12:54:34.000
2014-06-25 15:03:04.000
2014-06-25 15:03:04.000
2014-06-25 15:03:04.000
2014-06-25 15:03:04.000
2014-06-25 15:15:45.000
2014-06-25 15:15:45.000

[twinnyfo]

Have you tried using the DattAdd Function? Please show us any work you have so far, as we have no idea what any of your controls or variables may be named.

[veneeth]

I cant use the dateadd function. first we have to to convert the format to the correct format. The format what i get from the server is YYYY-MM-DD HH:MM:SS i want to convert that to DD-MM-YYYY HH:MM:SS. I'm trying to get this in access

[twinnyfo]

Again, what have you tried? I am certain that I could write some VBA to solve this problem--if it was my problem. But I have nothing to go on. Are these values in tables? How are you accessing the data?

The Conversion to different formats is very simple, once the values are converted to dates. However, we have no place to start with your problem, as you have not put forward any effort to solve the problem, nor have you provided the necessary information we need to help you.

Please review these links:

How to Ask a Question

Posting Guidelines

And then get back to us.

[Rabbit]

You said you can't use the DateAdd function. But that's not correct, you can.

[jforbes]

I assume you are getting the date given to you as a string and Access is balking at the .000 on the end for CDate() and IsDate(). i.e. IsDate("2014-01-08 00:38:57.000") returns a false.

Here is a modified version of a function I use from time to time, it might help you:

Public Function getDatePlusMinutes(ByRef sDate As String, ByRef iMinutes As Integer) As Date
    Dim iDotPos As Integer
    iDotPos = InStr(1, sDate, ".")
    If iDotPos > 0 Then
        sDate = Left(sDate, InStr(1, sDate, ".") - 1)
    End If
    If IsDate(sDate) Then
        getDatePlusMinutes = DateAdd("n", iMinutes, CDate(sDate))
    Else
        getDatePlusMinutes = vbNull
    End If
End Function

Result:
>?getDatePlusMi nutes("2014-01-08 00:38:57.000", 390)
>1/8/2014 7:08:57 AM

Then you can use format() on the result:
>?format(getDat ePlusMinutes("2 014-01-08 00:38:57.000", 390), "yyyy-dd-mm")
>2014-08-01
>?format(getDat ePlusMinutes("2 014-01-08 00:38:57.000", 390), "Long Time")
>7:08:57 AM

[veneeth]

Thanks for you help JFORBES. it's just working the way i wanted.

you were correct the date was given as a string.