Solved

Convert date to numeric

Posted on 2010-11-16
15
567 Views
Last Modified: 2012-06-27
Hi Experts

I have following function which is use to convert dates to numeric value such as 11-16-2010 to 20101611. However I am getting errors possibly because of  nulls in the field. I need to have conversion keeping null values intact.

Function ConvertDateToNumeric(pDate As Date) As Long

    Dim LYear As Integer
    Dim LMth As Integer
    Dim LDay As Integer

    'Extract the year, month, and day values from the date parameter called pDate
    LYear = (DatePart("yyyy", pDate)
    LMth = (DatePart("m", pDate)
    LDay = (DatePart("d", pDate)
   
     'Format new number as a yyyymmdd value
     
   ConvertDateToNumeric = CLng(CStr(LYear) & Right("00" & CStr(LMth), 2) & Right("00" & CStr(LDay), 2))
     
End Function

Thanks

0
Comment
Question by:drivers
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34150745
You need to change pDate to Variant to accept Null

if IsNull(pDate) then
ConvertDateToNumeric = pDate
else
ConvertDateToNumeric = Year(pDate)*10000+Month(pDate)+100+Day(pDate)
end if
0
 
LVL 1

Author Comment

by:drivers
ID: 34150947
Hi

I am getting "invalid use of null" error

trying this

Function ConvertDateToNumeric(pDate As Variant) As Long
 
If IsNull(pDate) Then
ConvertDateToNumeric = pDate
Else
ConvertDateToNumeric = Year(pDate) * 10000 + Month(pDate) + 100 + Day(pDate)
End If
   
End Function

thanks
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34151136
If you want nulls to be returnable, then the function must return Variant, not Long
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 75
ID: 34151621
This is all you need to do this conversion:

Public Function ConvertDateToNumeric(pDate As Variant) As Variant
    If Not IsDate(pDate) Then Exit Function
    ConvertDateToNumeric = Split(pDate, "-")(2) & Split(pDate, "-")(1) & Split(pDate, "-")(0)
End Function

mx
0
 
LVL 1

Author Comment

by:drivers
ID: 34151742
Thanks mx

getting "subscript out of range" error

0
 
LVL 1

Author Comment

by:drivers
ID: 34151812
cyberkiwi

changed to variant and receiving "type mistmatch" error

Function ConvertDateToNumeric(pDate As Variant) As Variant

If IsNull(pDate) Then
ConvertDateToNumeric = pDate
Else
ConvertDateToNumeric = Year(pDate) * 10000 + Month(pDate) + 100 + Day(pDate)
End If

any suggestions?

End Function

thanks
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 34151948
drivers,

How are you using it?
I create a table with 2 fields, one of them datetime.
Table Test1, dt date/time, id autonumber

Insert 2 records, one that is null, and one that is a valid date

I put this function in a module
Function ConvertDateToNumeric(pDate As Variant) As Variant
If IsNull(pDate) Then
ConvertDateToNumeric = pDate
Else
ConvertDateToNumeric = Year(pDate) * 10000 + Month(pDate) + 100 + Day(pDate)
End If
End Function

Open in new window

I create a query against the table:
SELECT *, ConvertDateToNumeric(dt) from Test1

Open in new window

And it all runs as expected.

What problem are you getting?
0
 
LVL 1

Author Comment

by:drivers
ID: 34151980
cyberkiwi

No actually 2 tables.

Tbl1.Datem = date/timeformat  (source)  value: 11/16/2010 or null
Tbl2.Datem = Long integer       (destination)  value: 20101116 or null

I am calling this function in an append query.

thanks

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34151995
I still get no error

I created table Test2, id autonumber, intval Long
I used this update query

Insert into Test2(intval)
SELECT ConvertDateToNumeric(dt) from Test1
0
 
LVL 1

Author Comment

by:drivers
ID: 34152012
hmm.. then it could be related to any other field being used in the query. I'll double check and get back to you... thanks for quick responses.
0
 
LVL 75
ID: 34152225
drivers:
What format are your dates being passed in. In your Q you show 11-16-2010  dashes
Above you show 11/16/2010 - slashes

"getting "subscript out of range" error"
What was actually passed in pDate ?  

mx
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34152250
pDate is a column from a table, so it is indeterminate what format it will be when implicitly cast to string in Split()
On my machine, inspecting a date value gives a string equivalent of d/m/yyyy.  But it could be anything, probably dependent on regional settings.
Split is a bad idea imho
0
 
LVL 75
ID: 34152411
ok ... one more time:

Public Function ConvertDateToNumeric(pDate As Variant) As Variant
    If Not IsDate(pDate) Then Exit Function
    pDate = Format(pDate, "yyyy-mm-dd")
    ConvertDateToNumeric = Split(pDate, "-")(0) & Split(pDate, "-")(1) & Split(pDate, "-")(2)
End Function

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34153441
Here is how to achieve this.

/gustav
Public Function ConvertDateToNumeric(ByVal varDate As Variant) As Variant

  Dim varNum As Variant

  If IsDate(varDate) Then
    varNum = Int(Format(varDate, "yyyymmdd"))
  Else
    varNum = Null
  End If

  ConvertDateToNumeric = varNum

End Function

Open in new window

0
 
LVL 1

Author Comment

by:drivers
ID: 34155194
Thanks everyone but cyberkiwi's solution worked for me.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question