How to retrieve only years from date time picker

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 9089 Views - Last Post: 26 August 2009 - 01:45 PM Rate Topic: -----

#1 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

How to retrieve only years from date time picker

Post icon  Posted 23 August 2009 - 07:57 PM

Hi, I am now want to retrieve only year from my table(summary),field name(dates). So i am facing the problem on how to display the year in combo Box. my sql statement as below. Thanks

If i put SELECT DATES, that means day,month and year will display. So, what should i put after SELECT.
con.Open()

			sql = "SELECT dates FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"
			da = New OleDb.OleDbDataAdapter(sql, con)
			da.Fill(ds, "summary")

			con.Close()
			maxrow = ds.Tables("summary").Rows.Count

			For x = 0 To maxrow - 1

				cbYear.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))
				cbYear1.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))

			Next x



Is This A Good Question/Topic? 0
  • +

Replies To: How to retrieve only years from date time picker

#2 piman314   User is offline

  • D.I.C Head
  • member icon

Reputation: 32
  • View blog
  • Posts: 169
  • Joined: 07-August 09

Re: How to retrieve only years from date time picker

Posted 23 August 2009 - 09:31 PM

how is the data in the dates field formatted? Is it mm/dd/yyyy or mm-dd-yyyy or something else?
Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: How to retrieve only years from date time picker

Posted 23 August 2009 - 09:40 PM

If you're using SQL Server check out DATEPART (or DATEPART for Access)

sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"


Was This Post Helpful? 0
  • +
  • -

#4 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: How to retrieve only years from date time picker

Posted 23 August 2009 - 10:34 PM

View Postpiman314, on 23 Aug, 2009 - 08:31 PM, said:

how is the data in the dates field formatted? Is it mm/dd/yyyy or mm-dd-yyyy or something else?





View Postpiman314, on 23 Aug, 2009 - 08:31 PM, said:

how is the data in the dates field formatted? Is it mm/dd/yyyy or mm-dd-yyyy or something else?

The format is mm/dd/yyyy
Thanks for reply.

This post has been edited by chanlichin: 23 August 2009 - 10:38 PM

Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: How to retrieve only years from date time picker

Posted 23 August 2009 - 10:37 PM

Well what you tried isnt even what I showed you. What I showed was

SQL Server
sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"



Or

Microsoft Access
sql = "SELECT DATEPART("yyyy",dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"


Was This Post Helpful? 0
  • +
  • -

#6 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: How to retrieve only years from date time picker

Posted 23 August 2009 - 10:45 PM

View PostPsychoCoder, on 23 Aug, 2009 - 08:40 PM, said:

If you're using SQL Server check out DATEPART (or DATEPART for Access)

sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"



i am using database MS Access. So i just change to
sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"


But msg come out "No value given for one or more required parameters".
Izzit is the problem occur below.
For x = 0 To maxrow - 1

				cbYear.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))
				cbYear1.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))

			Next x


Was This Post Helpful? 0
  • +
  • -

#7 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: How to retrieve only years from date time picker

Posted 23 August 2009 - 11:17 PM

View PostPsychoCoder, on 23 Aug, 2009 - 09:37 PM, said:

Well what you tried isnt even what I showed you. What I showed was

SQL Server
sql = "SELECT DATEPART(yyyy,dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"



Or

Microsoft Access
sql = "SELECT DATEPART("yyyy",dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"




I just add "" between yyyy but come out error "End of statement expected".
May i know what happen?Thanks
Was This Post Helpful? 0
  • +
  • -

#8 DataPriest   User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 57
  • Joined: 29-April 09

Re: How to retrieve only years from date time picker

Posted 24 August 2009 - 02:45 AM

Try using:

sql = "SELECT DATEPART('yyyy', dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"

Was This Post Helpful? 0
  • +
  • -

#9 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: How to retrieve only years from date time picker

Posted 24 August 2009 - 05:25 AM

View PostDataPriest, on 24 Aug, 2009 - 01:45 AM, said:

Try using:

sql = "SELECT DATEPART('yyyy', dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"


The combo box still can not display the year,really dun know what to do already....
Was This Post Helpful? 0
  • +
  • -

#10 DataPriest   User is offline

  • D.I.C Head

Reputation: 20
  • View blog
  • Posts: 57
  • Joined: 29-April 09

Re: How to retrieve only years from date time picker

Posted 24 August 2009 - 05:40 AM

View Postchanlichin, on 23 Aug, 2009 - 06:57 PM, said:

Hi, I am now want to retrieve only year from my table(summary),field name(dates). So i am facing the problem on how to display the year in combo Box. my sql statement as below. Thanks

If i put SELECT DATES, that means day,month and year will display. So, what should i put after SELECT.
con.Open()

			sql = "SELECT dates FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"
			da = New OleDb.OleDbDataAdapter(sql, con)
			da.Fill(ds, "summary")

			con.Close()
			maxrow = ds.Tables("summary").Rows.Count

			For x = 0 To maxrow - 1

				cbYear.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))
				cbYear1.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))

			Next x



Try using:

		For x = 0 To maxrow - 1

			cbYear.Items.Add(Year(ds.Tables("summary").Rows(x).Item("dates")))
			cbYear1.Items.Add(Year(ds.Tables("summary").Rows(x).Item("dates")))

		Next x

Was This Post Helpful? 0
  • +
  • -

#11 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: How to retrieve only years from date time picker

Posted 24 August 2009 - 06:01 AM

View PostDataPriest, on 24 Aug, 2009 - 04:40 AM, said:

View Postchanlichin, on 23 Aug, 2009 - 06:57 PM, said:

Hi, I am now want to retrieve only year from my table(summary),field name(dates). So i am facing the problem on how to display the year in combo Box. my sql statement as below. Thanks

If i put SELECT DATES, that means day,month and year will display. So, what should i put after SELECT.
con.Open()

			sql = "SELECT dates FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'"
			da = New OleDb.OleDbDataAdapter(sql, con)
			da.Fill(ds, "summary")

			con.Close()
			maxrow = ds.Tables("summary").Rows.Count

			For x = 0 To maxrow - 1

				cbYear.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))
				cbYear1.Items.Add(ds.Tables("summary").Rows(x).Item("dates"))

			Next x



Try using:

		For x = 0 To maxrow - 1

			cbYear.Items.Add(Year(ds.Tables("summary").Rows(x).Item("dates")))
			cbYear1.Items.Add(Year(ds.Tables("summary").Rows(x).Item("dates")))

		Next x


Hi,DataPriest, it has the error "
Class 'System.Windows.Forms.Label' cannot be indexed because it has no default property." Year was higlighted
Was This Post Helpful? 0
  • +
  • -

#12 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: How to retrieve only years from date time picker

Posted 24 August 2009 - 06:15 AM

hi,

since you are using a for loop,

you could do this (if your field is not dbnull)

 For x = 0 To maxrow - 1
				cbYear.Items.Add(cdate(ds.Tables("summary").Rows(x).Item("dates")).year)
				cbYear1.Items.Add(cdate(ds.Tables("summary").Rows(x).Item("dates").year)
			Next x


Was This Post Helpful? 0
  • +
  • -

#13 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: How to retrieve only years from date time picker

Posted 24 August 2009 - 07:34 AM

View Postmotcom, on 24 Aug, 2009 - 05:15 AM, said:

hi,

since you are using a for loop,

you could do this (if your field is not dbnull)

 For x = 0 To maxrow - 1
				cbYear.Items.Add(cdate(ds.Tables("summary").Rows(x).Item("dates")).year)
				cbYear1.Items.Add(cdate(ds.Tables("summary").Rows(x).Item("dates").year)
			Next x


Yes, i can display the year already. But how should i do if i dun want the year be repeated?
Was This Post Helpful? 0
  • +
  • -

#14 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: How to retrieve only years from date time picker

Posted 24 August 2009 - 07:51 AM

Try this for your sql statement

sql = "SELECT DISTINCT(DATEPART("yyyy",dates)) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'" 


Was This Post Helpful? 0
  • +
  • -

#15 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: How to retrieve only years from date time picker

Posted 24 August 2009 - 05:38 PM

View Postmotcom, on 24 Aug, 2009 - 06:51 AM, said:

Try this for your sql statement

sql = "SELECT DISTINCT(DATEPART("yyyy",dates)) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "'" 



I try it already. But have the error "End of statement expected". What is the problem actually?Thanks...
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2