What's the most pain-free way to get a date and time out of this field? 2014040104274500 1

[keun]

I believe this is yyyymmddhhnnss00 - I can do it with a function for each date/time part and then another function to put them together but I wonder if there is a quicker way...

2014102923031500
2014112605110500
2014123002252300

Thanks!


I joined this forum in 2005. I am still a hack.

 

[MajP]

Not sure what the definition of quicker means here. I would do what you say and make two functions to use in a query.

Public Function GetDateFromString(varDate As Variant) As variant
  Dim theYear As Integer
  Dim theMonth As Integer
  Dim theDay As Integer
  If IsNumeric(varDate) Then
    theYear = Left(varDate, 4)
    theMonth = Mid(varDate, 5, 2)
    theDay = Mid(varDate, 7, 2)
    GetDateFromString = DateSerial(theYear, theMonth, theDay)
  End If
End Function
Public Function GetTimeFromString(varDate As Variant) As variant
  Dim theHour As Integer
  Dim theMinutes As Integer
  Dim theSeconds As Integer
  If IsNumeric(varDate) Then
    theHour = Mid(varDate, 9, 2)
    theMinutes = Mid(varDate, 11, 2)
    theSeconds = Mid(varDate, 13, 2)
    GetTimeFromString = TimeSerial(theHour, theMinutes, theSeconds)
  End If
End Function

SELECT tblData.strDate, getDateFromString([strDate]) AS TheDate, getTimeFromString([strDate]) AS TheTime
FROM tblData;

[TT]
strDate	         TheDate	     TheTime
2014123002252300	12/30/2014    2:25:23 AM
2014102923031500	10/29/2014    11:03:15 PM
2014112605110500	11/26/2014    5:11:05 AM
	[/tt]

 

[keun]

Thanks, this is super helpful. I have not used DateSerial before so thanks especially for that!

 

[MajP]

Another valuable function is the Cdate function. So you could do something like

Public Function GetDateFromString(varDate As Variant) As variant
  Dim theYear As string
  Dim theMonth As string
  Dim theDay As string
  If IsNumeric(varDate) Then
    theYear = Left(varDate, 4)
    theMonth = Mid(varDate, 5, 2)
    theDay = Mid(varDate, 7, 2)
    GetDateFromString = cdate(theDay & "/" & themonth & "/" & theYear)
  End If
End Function

Cdate can convert a string that looks like a date into a date. Handles lots of formats even strings that look like time.

 

[strongm]

Or ...

[blue]Public Function GetDateFromString(varDate As String) As Date
    GetDateFromString = CDate(Format$(Left$(varDate, 14), "#### ## ## ##:##:##"))
End Function[/blue]

 

[Andrzejek]

Don’t you just love when an acre of (very nice and easy to read, BTW) code becomes a ‘one liner’?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

Don’t you just love when an acre of (very nice and easy to read, BTW) code becomes a ‘one liner’

Not most of the time.
Duane's example is fine, it is clear and efficient. However, in general I would say that is not the case that shorter is better, especially in VBA. Do not confuse short code with efficiency, readability, maintainability, portability, scalabiltiy, and debugability.

The amount of lines of code often has little to do with what is compiled and how efficient it runs. On this site I see people post a ton of code that they cannot debug, noone can understand, and it can never be reused. Often they try to eat the elephant at once without breaking it into pieces.

I also recommend that any function called by an Access query should be in this form.

Public Function somefunction(variantArgument As Variant) As variant
  'First always check that a null did not get passed.
  'use isnull, isnumeric, etc
  If IsNumeric(variantArgument) Then
    'Other error checking argument validation
    'your code here
    'return a variant or string
  End If
End Function

Because it is highly likely that a query can contain a null, I always make the arguments variants. The returntype can be a string or variant but not a numeric or date. This can be a real pain if not accounted for. If the return type is numeric or a date you will return a default value and not a null. At least with a string you return an empty string so that is fine.

So assume I have a table of 8k records and have 20 records with a null for the date, doing this
Public Function GetDateFromString(varDate As String)
will cause a lot of problems. Even if the current query requires a value, there may be a later date when you want to use that function for queries with nulls.

I could have wrote the original code as a one liner, but I would never write it that way especially if sharing with someone.

Public Function GetDateFromString(varDate As Variant) As variant
    GetDateFromString = DateSerial(left(varDate,4),midVardate(5,2),midVardate(7,2)
End Function

It is harder to debug and harder to read and easier to make a mistake. The local variables are not needed, but the readability outweighs any performance hit.
Most of the VBA code I write is far longer than it needs to be. I often spell out default properties and methods and most of the time try to fully qualify objects. May be longer but most of my code can be shared with little or no comments to explain it. VBA is a really sloppy language so the more specificity often is better.


My favorite are when people ask why this does not work

current db.execute (some huge sql string with embedded iif functions, other functions, tons of string and date formatting)

 

[dhookom]

Duane's example is fine

The coder is actually strongm and I'm not sure if his true identity is also "Duane".

I agree that shorter is not always better. Coding styles and recommendations could be another entire thread or maybe even a forum. I like the idea of variants, error handling, and comments. I often fall into the trap of making assumptions about OP postings where they reveal only a small set or single value for sample data. When you provide a solution that meets their requirements, the gotchas are revealed as the thread grows and grows.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Sorry Duane. I new it was one of the usual smart guys.

 

[Andrzejek]

MajP,
I see your points and have to agree with you.
I especially like the expression of 'debugability'


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[keun]

I have MajP's code and stronm's code both working. I chose to use strongm's because it combines the date/time and I ultimately want to subtract 6 hours from these date/times.

How do I combine MajP's TheDate and TheTime to create one field that I can subtract 6 hours from?

I tried:

DateTimeMerge: [TheDate]+[TheTime]

but I get datetime with no space in between and then DateAdd("h",-6,[DateTimeMerge]) gets me #ERROR

 

[keun]

nvmind - got it:

DateTimeMerge: [TheDate] & " " & [TheTime]

derp

 

[MajP]

I would probably do it the oppositte way. Return the complete date and time then work with that. Then format the results to show your date part and your time part. The format function returns a string, and that makes it hard to manipulate it as a date.

In access and vba the date data type is really a date and time. Even if you see just a date part or see just a time part behind the scenes there is both a date part and time part. One of those parts may equal zero. What you see is always just a formatting applied to the date. What is nice is you can format it in many ways to include showing just the date part or just the time part. You are always better off working with a combined date and time and then formatting it to show what you want.

So the date
4/19/2015 11:11:01 PM
Is actually stored as
42113.9659837963
Where the integer part tells you the date. It is 42113 days since the base date of Dec 31 1899.
It is .9659837963 of an additional day or 23 hours, 11 minutes and 1 second.

So get your date and time, especially if you are going to do any manipulation to it. You can use Strongman's to do that

Public Function GetDateAndTime(varDate As Variant) As Variant
  If IsNumeric(varDate) Then
    GetDateAndTime = CDate(Format$(Left$(varDate, 14), "#### ## ## ##:##:##"))
  End If
End Function

Then do your additions subtractions.
Then just format the solution to show your date and time.

The formatting function is extremely powerful and you can format your datetime in hundreds of ways. To demo

Public Sub FormatDate()
  Dim varDate As Date
  varDate = GetDateAndTime("2015041923110100")
  Debug.Print Format(varDate, "mm/dd/yyyy")
  Debug.Print Format(varDate, "HH:MM:SS am/pm")
  Debug.Print Format(varDate, "ddd dd, mmm, yyyy")
  Debug.Print Format(varDate, "dddd dd, mmmm, yyyy")
End Sub

Here is you output
04/19/2015
11:11:01 pm
Sun 19, Apr, 2015
Sunday 19, April, 2015

In truth there is no way to store just a date or just a time.
so if this is your stored date time
42113.9659837963

Normally 42113.0 display as a date only
But that is really 04/19/2015 12:00:00 AM

.9659837963 will normally display as just a time but it is
12/31/1899 11:11:01 PM

 

[keun]

Well OK! Thanks.

 

[strongm]

> I like the idea of variants

I'm against the overuse of variants, and I only use them when they are useful. I much prefer strong typed languages, and variants take us back to weak typing and can mask all sorts of errors. This is not the same as saying they should not be used, though.

 

[MajP]

I only use a variant in this specific case where an access function is used by a query. Even if the plan is to make the field required and have a default value, there is still a high likelihood of someday passing a null. Such as an outer join. If this is a big query with a couple of nulls in it, it can lock up the application especially if you distribute this as an accde.

 

[strongm]

As I said: when they are useful