• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Return year only from datetime field

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
flukester
Asked:
flukester
  • 7
  • 6
  • 2
  • +2
1 Solution
 
Juan_BarreraCommented:
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
 
NullableCommented:
use similar code  :

DateTime.parse (dr("ReportYear").ToString()).Year;
0
 
FER_GCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
prairiedogCommented:
Use SQL YEAR function. Change your sql select query to something like this:
Select YEAR(theDateTimeFieldName) As ReportYear From YourTable
0
 
flukesterAuthor Commented:
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
 
prairiedogCommented:
Arghhhhhh, fingers are not working fast enough.
0
 
Juan_BarreraCommented:
It's because this line:

 reportYear += reportData.Year

The "+" sign means that you are adding to.
0
 
flukesterAuthor Commented:
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
 
Juan_BarreraCommented:
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
 
flukesterAuthor Commented:
Juan, yes I need to show all of them on the page.
0
 
Juan_BarreraCommented:
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
 
flukesterAuthor Commented:
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
 
Juan_BarreraCommented:
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
 
flukesterAuthor Commented:
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
 
Juan_BarreraCommented:
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
 
flukesterAuthor Commented:
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
 
Juan_BarreraCommented:
No worries, I'm glad it works!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now