?
Solved

Return year only from datetime field

Posted on 2008-11-03
17
Medium Priority
?
415 Views
Last Modified: 2008-11-03
I'm hoping there is an easy way I can return year only or trim a datetime field to year only. I am selecting all from a table, creating a datatable and looping through the rows to find all annual reports in the table.

Dim dr As DataRow
        For Each dr In dscomm.Rows
            reportYear += dr("ReportYear")
        Next

This is an example of the data that is returned:
8/30/2002 7:24:00 AM10/1/2003 4:28:03 PM9/24/2004 4:30:51 PM

What I need to do is get the years only and then link those years to another page which I can handle, but I can't figure out a method to just return year?
0
Comment
Question by:flukester
  • 7
  • 6
  • 2
  • +2
17 Comments
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22870922
Try this:

Dim dr As DataRow
        For Each dr In dscomm.Rows
            Dim reportData as datetime = Ctype(dr("ReportYear"),DateTime)
            reportYear += reportData.Year
        Next

Open in new window

0
 
LVL 1

Expert Comment

by:Nullable
ID: 22870934
use similar code  :

DateTime.parse (dr("ReportYear").ToString()).Year;
0
 
LVL 7

Expert Comment

by:FER_G
ID: 22870981
You just can get it, if you use the YEAR scalar function of SQL;
SQL statement...
"Select year(date_field), date_field, another_field from table"

FerG
Saludos.
--
Ing. Fernando D. Giletta
San Fco. Cba. Argentina.


0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 22

Expert Comment

by:prairiedog
ID: 22871053
Use SQL YEAR function. Change your sql select query to something like this:
Select YEAR(theDateTimeFieldName) As ReportYear From YourTable
0
 

Author Comment

by:flukester
ID: 22871058
Both codes work, but it seems they are adding the years together instead of giving me each year. The outcome of both scenarios is 6009
Years are 2002,2003, 2004?
0
 
LVL 22

Expert Comment

by:prairiedog
ID: 22871060
Arghhhhhh, fingers are not working fast enough.
0
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22871091
It's because this line:

 reportYear += reportData.Year

The "+" sign means that you are adding to.
0
 

Author Comment

by:flukester
ID: 22871113
That's what I thought but

 For Each dr In dscomm.Rows
            Dim reportData As DateTime = CType(dr("ReportYear"), DateTime)
            reportYear = reportData.Year
        Next

only returns the last record.
0
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22871130
Yes, because it overwrites reportYear every time it enters the loop, giving you the last record value as a result.
Do you need all the year in separate strings?
0
 

Author Comment

by:flukester
ID: 22871155
Juan, yes I need to show all of them on the page.
0
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22871164
then you could use something like this, it will give you a list containing all the years:

Dim reportYear As List(Of String)
For Each dr In dscomm.Rows
    Dim reportData As DateTime = CType(dr("ReportYear"), DateTime)
    reportYear.Add(reportData.Year)
Next

Open in new window

0
 

Author Comment

by:flukester
ID: 22871772
Juan, I apologize, I'm still not getting it. I added the import statement for System.Collections.Generic and still get an error:

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 85:         For Each dr In dscomm.Rows
Line 86:             Dim reportData As DateTime = CType(dr("ReportYear"), DateTime)
Line 87:             reportYear.Add(reportData.Year) 'highlighted as error
Line 88:         Next

 
the table doesn't have any null values. What am I missing?
0
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22871813
Oops, I'm sorry, it should be like this (missed the New keyword):

Dim reportYear As New List(Of String)
For Each dr In dscomm.Rows
    Dim reportData As DateTime = CType(dr("ReportYear"), DateTime)
    reportYear.Add(reportData.Year)
Next

Open in new window

0
 

Author Comment

by:flukester
ID: 22871833
I tried that as well and the output on the page is:

System.Collections.Generic.List`1[System.String]

Public variable up top Public reportYear As New List(Of String)

Then below

Dim dr As DataRow
        For Each dr In dscomm.Rows
            Dim reportData As DateTime = CType(dr("ReportYear"), DateTime)
            reportYear.Add(reportData.Year)
        Next

0
 
LVL 20

Accepted Solution

by:
Juan_Barrera earned 2000 total points
ID: 22871887
Ah, how d oyou want to output it to the page?
Anyway, try this code (check the value of the "outputToDisplay" string after the loop is finished)
It's not the best way to do it, but it will give you an example, then you need to modify it according to your needsL

 Dim reportYear As New List(Of String)
        For Each dr In dscomm.Rows
            Dim reportData As DateTime = CType(dr("ReportYear"), DateTime)
            reportYear.Add(reportData.Year)
        Next
        Dim output As New StringBuilder
        For i As Integer = 0 To reportYear.Count - 1
            output.Append(reportYear(i))
            output.Append(","c)
        Next
        'String ready to display
        Dim outputToDisplay = output.ToString() 

Open in new window

0
 

Author Comment

by:flukester
ID: 22871933
Okay, tested the value of outputToDisplay and all the years show up separated by a comma. I'll tweak it for my page display now. Thank you very much for walking me through this and your quick responses!
0
 
LVL 20

Expert Comment

by:Juan_Barrera
ID: 22871984
No worries, I'm glad it works!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

807 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