INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

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

(OP)
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.

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

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

CODE

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 

CODE -->

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

CODE -->


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


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

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

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

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

CODE -->

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.

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

Or ...

CODE

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

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

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.

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

Quote:

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.

CODE

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
'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.

CODE

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)

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

Quote (MajP)

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.

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

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

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

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.

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

(OP)
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:

CODE -->

DateTimeMerge: [TheDate]+[TheTime]

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

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

(OP)
nvmind - got it:

CODE -->

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

derp

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

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

CODE

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

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

CODE

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

(OP)
Well OK! Thanks.

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

> 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.

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

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.

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

As I said: when they are useful

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!