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

[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

[pbaldy]

Tools you'll need include the DateAdd(), DateValue() and TimeValue() functions.

[thebigthing313]

In addition to pbaldy's suggestions, the millisecond portion of your date+time string will throw off these functions. Trim the millisecond portion out first before going through the functions, and then concatenate them at the end again if you really need it.

[June7]

Assuming consistent structure and every record has value, try:

DateAdd("h", 6.5, DateValue(Left([fieldname], 19)) + TimeValue(Left([fieldname], 19)))