Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Extracting Year From Date Using Instr Or Mid

I have a date field in database. I have to extract it from the database, convert it into a string using Str function and then get the year out of that string. The format for storing is mm/dd/yyyy.
Please help in formulating a logic.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Extracting Month And Year From A Date
I have a date field like 25/02/2002 'dd/mm/yyyy' ; I would like to extract the month and year so I can build a select statment on them. e.g.

If month is 02 then ...
Select * from table where month = 02
End if

if year is 2002 then
Select * from table where year = 2002

The only problem is that my date field in database is one field which is call_date and it is datefield like 'dd/mm/yyyy'.

How can I make the select statment search for only month or only year in that field?

Thank for all of your for your help.

Extracting The Date/month/year From Recordset

I have recordset which has record type date. How can i get the date , month and year seperated from the xx/xx/xxxx.

Lets say i get


How can i get the date from 02. I need to do this in VB code not in the quary.

Also if someone can mention how it can also be done in a quary. Thats would be great. I am still learning and want to know syntax.

Thanks in advance

Instr - Extracting Links From Webpage.
This is what I have right now...
Public Function GetLinksFrom(strHtml As String) As String()

Dim strLinks() As String

If (strHtml <> "") Then

ReDim strLinks(0)

Dim lngPos_Body As Long, lngPos_aHref As Long
Dim lngPos_aHrefEnd As Long, lngHTML_len As Long
Dim lngPos_Search As Long

' Store length of the HTML...
lngHTML_Len = Len(strHtml)

' Search for the <body> tag...
lngPos_Body = InStr(LCase$(strHtml), "<body")

' We found the <body> tag...
If (lngPos_Body <> 0) Then

lngPos_Search = 0

' Loop until the end of this HTML document...
Do Until (lngPos_aHrefEnd >= lngHTML_len)

' Search for a link...
lngPos_aHref = InStr(lngPos_Body + lngPos_Search, LCase$(strHtml), "href=" & Chr(34))

' If we found a link...
If (lngPos_aHref <> 0) Then

' Search for the end of this link...
lngPos_aHrefEnd = InStr(lngPos_aHref + 7, strHtml, Chr(34))

' We found the end of this link...
If (lngPos_aHrefEnd <> 0) Then

strLinks(UBound(strLinks)) = Mid$(strHtml, lngPos_aHref + 6, lngPos_aHrefEnd - lngPos_aHref - 6)

ReDim Preserve strLinks(UBound(strLinks) + 1)

lngPos_Search = lngPos_aHrefEnd + 1


End If


' If empty upper boundary...
If (strLinks(UBound(strLinks)) = vbNullString) Then

' Remove it then...
ReDim Preserve strLinks(UBound(strLinks) - 1)

End If

' Done with this loop... so exit it...

Exit Do

End If


' Assign links array to
' the array you want...
GetLinksFrom = strLinks

End If

End If

End Function
There is some error in it. I go to my website and it doesn't return all of the links. I think it is just something minor but who knows! Is there anything you can find wrong with it?

Help With Year To Date && Month To Date Stats
I have an Access dB that my frontend is populating. I have created pre-defined Excel templates for report generating. Now each line-entry into the dB is date-stamped, what I need is how to go about calculating the Month-to-Date, and Year-to-Date stats from the daily data in the dB.

I have no issues with printing or transfering data, I just need a nudge as to how to go about summing certain columns of data going back to the 1st of the month and 1st of the year.

Any suggestions?

Date's Year
I have a date value and i need to extract from it the year.

a= 11/11/1990

and a_year must be 1990

How can i do that?


And how can I select the entries that are between two date values, in a SQL string?

what does this code do in VB?

VB Code:

How To Get The Year From A Given Date
Can someone show me how I can get the year only from a given date. A date in the field say SecondYear from second YearYear Table may be stored as 24/02/2007. The I need to use only 2007. How do i get that. Can someone show me.

Adding 100 Year To A Date?
Hi there,

I am writing a valdiation application for a document workflow system.
I hae to generate a retention duraition fr every document. This si the time the document should be kept physicaly in store. But i can't find how i should add 12 months to an existing date. for example the retention periode is 12 month. When the document is scanned the retention duration should be current date + 12 months.

Anyone ????

thanks in advanced


Changing Date And Year?
Is there a way that I can changed the date or year of the system clock using a vb program? I hope this is simple... Thanks in advance!

How To Get The Week No In A Year Given The Date
i would like to know , what functions that need to be used in order to get the week no in a year given the date..

i tried using the week(date) function but it does not seem to there any other way i can solve this problem..tahnk you..

regards pragash..

How To Retrieve The Year Value Of A Date ?
I use txtyear.text = year(recordset("someday")), but there is a compile error.

Actual Date Of The Year
I know the DayofYear function returns the number of the day in the year. Is there any way to reverse that?

Ex: Enter 32 and you should get 2/1/02 returned.


How To Get The Year To Date Automatically
hi how do i get the year to date automatically?
example if the date today is sept. 8, 2005 i need to get the year to date: jan.01, 2004 - sept.8, 2005

please help..


Date: Day, Month And Year
im writing a program that needs to create recipts for payments made to the user. i wanted to create a number system that is based on the date since its a number system that cant ever repeat itself and the user can find out when the payment was made just by looking at the number.

eg: reciept 1, 5/09/2005 = 01050905 'first reciept for the day
eg: reciept 2, 5/09/2005 = 02050905 'second reciept for the day

now i know that the date function can get the date in the format that your computers setting are on, so:

Code:dim DateN, Day As String, Month As String, Year As String

DateN = date
DateN = Format(DateN, "Short Date")     'just in case the users computers settings are different

How do i Split the date into day, month and year to assign them to their corresponding string?


Signature -----------------------------------------------------------------------------------------------------Code:Private Sub Form_Load() 'Print to from, code my problems!
     Form1.Print "CanT SleeP!..... MusT PrograM!!!" & vbCrLf & "I need coffee, WhErE iS mY CoFfEe?!"
End Sub 'All things come to and end

Get Last Day Of Last Year (Date Problem)
just nly know how to get 1st day of month
by using dateadd ("d",-day(date)+1,date)

but how to get last day of last year and last day of this month ??

How To Get The Year From System Date
My question for all you VB'ers is as follows
I want to get the year from the system date so I can specify it something like below. I am using VB 6
Year is specified fromt he user and I want it to be between 1990 and the year in question

   if 1990 <= Year <= SystemDate.Year then

Thanks to all who try to help

Convert A Date To Day Of The Year (12/31 To 365)
I was wondering how to take a user inputted date (like 12/31/2008) and convert that to the day of the year

i also want to convert the current system date to the day of the year

if anyone could help with that i would appreciate it

Remove Month Or Year From Date?
Is there a simple way to remove/add one or more months/years to/from the current date? To remove/add days are simple (Yesterday = Date - 1), but the months/years??

I Need To Integrate Year, Month, Date
Let's say that I want to assign the current date from the PC to a variable. How do I achieve this? (YYYY-MM-DD)


Knowledge is the rule

Calculating Year Of Age From Date Of Birth???
I've Access database with children information such as name and their data of births.

I want to use VB DataReport to report these children's info. except their data of birth, instead of reporting DOB, I want to report their ages from today date

e.g: Name-->John Doe, DOB-->02/02/2000
Then on DataReport, it doesn't give 02/02/2000 but giving "4 years old" or etc...

I don't know if any functions can do in DataReport. Please help

Need Last Year's Date Based On A Run Parameter
I have a column in Crystal with the Transaction Date. I have set the TRXDATE = {?RunDate}. Now I need this same date (in the same report), but for last year. How do I do that?

How To Calculate Year To Date Values
I am developing a Payroll Program in VB 6. I want the program to calculate Year to Date (YTD) values for Basic salary, Gross Salary, Tax, Net Pay and other deductions from gross salary. I donot know how to go about it.

Please let somebody be kind enough to help me out.

Thank you.


Date, Month And Year From The TxtDate..
Dear Friends,

How can I do the following? I Have the controls : txtDate, txtMonth, txtYear and cmdSearch on the form.

1) txtDate (It holds the current date)

2) txtMonth (Should retrieve the month from the txtDate - What is the code to get month from the txtDate?)

3) 2) txtYear (Should retrieve the Year from the txtDate - What is the code to get Year from the txtDate?)

4) cmdSearch (When it is pressed, it should show all the records pertaining to the month & year displayed in the txtMonth, txtYear respectively)

Using MS Access Database
Table Name is : Expenses

Fields are:

Date (Data Type is : Date/Type)
Amount (Data Type is : Currency)

So, what is the code for cmdSearch button? I need the same format without any changes. Hope to get the answer.

Thanks in advance.


Determine Date Out Of Week And Year

Is there a function, or is it difficult to make a function, that receives 3 parameters (weeknumber, year and day) and determines the date belonging to that day. For example I want to know what date the friday in week 23 in 2003 will be. I pass these parameters to the function and it returns: "June 30".

Does anyone has suggestions an making such a thing?

Query: Find Date In Year

This seems like it should be very easy (and maybe it is, but I am not getting it). I have an access table that contains a date field. I want to select all records where the date is in a certain year. I am going to default it to the current year but allow the user to pick other dates via combobox. I have tried a bunch of things ("in" statements, "like", "between" ... but haven't got it quite right. Any ideas?


Finding Out The Year Week From Date
I'm trying to find out in which week of the year is a given date
and the week's first Monday's date


How To Format The Date Into Form Of 'day-mon-year'
good day to all !!
i am using vb 6.0 and oracle 9i.
i am using the dtpicker to display the date
how to i format the dtpicker date value into form of day-mon-year so i can insert the record to oracle database

thank you

Incrrementig Number With Date Of Year

i am in need of an urgent code for cheking number which is autogenerated along with date
i am accepting the current date and incrementing the number like 3-2004,4-2004,5-2004 so on
now i need the number to restart when the date is changed to 2005
as 1-2005,2-2005, so on. iam able to generate the nuber auto and can take the year part
but not able to restart when the date is
changed to 1-1-toany newyear
i am inputting this number along with the date in a textbox
if any body knows please help

Yahoo! India Matrimony: Find your life partneronline.

Can't Extract Year, Month, Day Out Of A Date
Hi, due to the different local setting (Date setting) around the world I have to manually create a standard date format for our online database system.(ASP) We prefer to use YYYY/MM/DD. But the hosting server is MM/DD/YYYY.

It works find if I just want to print out the date

    <td><%=ProjectDate%> </td>

but when I tried to do
    <td><%=year(ProjectDate)&"/"&month(projectdate)&"/"&day(projectdate)%> </td>

it gives me the following error message

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
/ZAO/App/Project/Invoice.asp, line 204

However I can do this without a problem


I have no idea what I did wrong. I have even tried to use Cdate().

the following code works fine

    <td><%=Cdate(ProjectDate)%> </td>

however when I add year(), it gives me the same error. (I test it with Year() only this time)

    <td><%=Year(Cdate(ProjectDate))%> </td>

What's wrong with my code? or is there a better way to use a standard date format without doing all these complicated manual format.

Thanks in advance

How To Add Days/month/year To A Given Date
How can I add a number in day/month/year to a date.

(URGENT)(VB6)seperate Year From Date
hon'ble gurus,
I use vb6,MsAccess,ADO. Is it possible to seperate the Years(from a date type column) from a table(Access2002) and get required data through sql? Say, I have 5 dates(dd/mm/yy format) like:20/05/06,28/09/06,15/07/06,11/02/06,05/09/07. If I want to have recordcount of 2006 then will I find 4 and recordcount of 2007 then 5? If possible, please give me hints.

Week From Date (Financial Year)
Hi Guys.

Im trying to run a peice of code to do the following

Get the week number from a cell where 31/03/XX is the first day of the year.

And only goes up to 52 weeks then resets back to week 1 on the 53'rd week.

ANy help would be much obliged.


Edited by - Big Al Inc on 5/8/2007 7:56:42 AM

Excel Macro - Incrementing A Date By One Year
Hello -

I have a bunch of records in an excel document (about 6000) and I would like to know how to write a macro that will calculate a new date for each record that occurs exactly one year after the date listed for each record in the spreadsheet. I'm quite new to VB and I can find a function in excel that specifically addresses my problem.

Thanks for your help,


Retrieving Year, Month From Date Variable
Good Day,

I am trying to do a trivial operation but cannot figure out how to do it.

Here is my code :

Dim TempDate as Date

TempDate = "2005-09-22"

TempMonth = ???(TempDate)
TempYear = ???(TempDate)

I want to retrieve the Year and Month from the Date variable.
Do I have to code a seek function or is there a simple function that can retrieve these values from Date Variables ?

Many Thanks

Evaluating The Date And Adding One Year And Subtracting One Day
I did a search and couldn't come up with an exact answer...

I put together a string:

Dim evalDate, evalYear, midYear
evalYear = InStrRev("" & Date & "", "/")
midYear = Mid$("" & Date & "", evalYear + 1)

evalDate = "" & cmbDay & "/" & cmbMonth & "/" & midYear & ""
(evalDate should now have the form 1/14/2003, depending upon what the comboboxes hold for a value).

What I'm trying to do is take evalDate and add 1 year and minus 1 day. Now, I know there has to be an easier way instead of writing 100 if statements to subtract the day, and if it is 1, subtract one from the month also. All help is greatly appreciated!



Reformat Year From Short Date For Query
I have a VB6 form with bound ADODB dc which needs to query (adcmdText sql SELECT)an Access 2002 db table date field in order to match a user input textbox entry (TxtCtrtYear) on the form and then display matches. The Access date field (Start_Date) is formatted as "short date" as is the display text box. Both of these need to remain in that format, so I just need to reformat(?) the year value from the db date field in order to make the query work. I'd appreciate any help w/ the syntax. Thanks. --Ed

Here's my query code:

SELECT * FROM Contract WHERE Start_Date = '" & TxtCtrtYear.Text & "'

HAPPY NEW YEAR ALL!!!...Have You Noticed The Date Today 01/01/01.
01/01/01 Must mean this is the actual millenium as the date has reset?

Extract Just The Month Or Year From A Record With Date 4/5/00
Can anyone help me with the code using VB6 and using SQL code to:

the user selects from a drop down box the month from one drop down box and the year from another drop down box


cboMonth leaves you with text, example "January". I think I would then need to turn this into "1" somehow. the Year is formatted as 2000 but I need it to query the database as "00"

How do I take what the user selects in a form and query the database date for just the month and year; doing nothing with the day.

The report for example is run once a month. If they select March. They need to get a count of how many records were inputted for the month of March. I use the field Date_Opened to query this as it is a required field. So when they select March for the month and 2000 for the year or 00 I need it to go to the database select Date_Opened and look for all records beginning in that field with "3" and then query the year portion and find only records that are from the cboYear field. So although I have a day, I need to do nothing with that field.

Anybody have any good ideas on how I could do this with code or the SQL code to do this,

thanks in advance

Calculating A Date From Week Number && Year
I need to be able to calculate the date of every Monday depending on what week number/year it is.

For example is it possible to calculate the date (e.g. 20/10/2003) of a given day (e.g. Monday) when you only know the week number (e.g. 43) and the year (e.g. 2003)

If anyone can point me in the right direction, your help would be greatly appreciated.

Many thanks in advance


Converting Day Of Year Into Standard Date Format
I am using Access 2000 VBA to pull data from AS/400 tables.  The Dates on the AS400 are 101350 (cyyddd)Referred in our documents as Julian date.
c = Century, yy = Year, and ddd = day of year.  This example needs to convert to 12/16/01.  Can anyone help me make this conversion?

How Datetimepicker Set Date Formate Day-month-year
asslam oalakum my dear groug members
     i facing the problem that datetimepicker search date like 13-2-2005 if day is greatetr then 12 then it search correct formate as define in the query.but if the day is lees or equal to 12 then it can not serach according to the query formate.
show result in the revrse formate like month-day-year. same problem with access database.
                           I want the search alwase in the formate of dd-mm-yyyy.
please tell me the solution of this problem.
      thanks for your interest

Return Of Financial Year In Date Input
I am using VB6 with Access2000. My main table contains these fields.
Gear Name, date of failure, financial year.
Now my datareports contains the year format in financial year pattern, i.e., from Apr to Mar.
So i want that the financial year field should automatically updated after entering the date in the date of failure field as per the value of the date.
Like "22/02/2004" will give 2003 - 2004 and "22/05/2004" will give 2004 - 2005.

Compair A Number With The Week Of Year Of A Certain Date

i want to check of a number is lower then the week of the year of the current date.
How do you do this?

Problem With Leap Year In Access Date Field

I attempted to update a record in an Access database through my vb program.  However, when the pointer gets to the record where a leap year is in the date, I get a type mismatch error.  I could not understand why I got this error with only this record and not the others.  Then I went into Access and attempted to manually enter the date (02/29/1999).  When I attempt to move to another record, I get, "The value you entered is not valid for this field."

Is there any way around this problem?

Thanks for any help.

While Passing Date For The Year 2002 Report Is Shown And Not Displayed For 2003 Help Pls!
Hi freinds,
                           My form looks like this, it will have two textbox for passing date , a combo box for Selecting the Product Name, and a command button to show the report and some option buttons. name of the Optionbutton is optIwise.
Everything works correct only for 2002 and not for 2003. I don't know what's the problem. when i see in the database the records are there for this year , I temporarily put a MSH Flex Grid and try to display records for this year and last year but in the my original project, the records are not shown in the report only for this year. I also refer the date passing through the break point, everything is going correct, but atlast, the output for the year 2003 is not coming.

I am using Sql version 6 as the Back End

In strSelectionFormula i am using the date format like this, Format(fmd, "YYYY,M,D"), if I change this format, it shows error, Only in this format I am able to run the program, but this format is working for the last year 2002. I think it is the default format to pass date to crystal report, if it is wrong suggest me.

ST030100.ST03015 is the Date field and ST030100.ST03018 is the Product name displayed in combo box

Dim strSelectionFormula As String
Dim fmd As Date
Dim tod As Date
Dim t As Date

Private Sub Form_Activate()
On Error Resume Next

     Text1.Text = "01" & Format(Date,"MM/YY")
     Text2.Text = Format(Date, "DD/MM/YY")

    CrystalReport1.Connect = "DSN=scalaDB;UID=scala;PWD=scala"

    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set rspt = New ADODB.Recordset

While Not rspt.EOF
        cmbProd.AddItem rspt!ST03018
        cmbProd.AddItem "ALL"
End Sub

Private Sub Command1_Click()

        fmd = Text1.Text
        tod = Text2.Text

If Not optIwise.Value Then Exit Sub
              CrystalReport1.ReportFileName = App.Path & "/product.rpt"

If cmbProd.ListIndex = cmbProd.ListCount - 1 Then
            strSelectionFormula = "{ST030100.ST03015} in Date(" & _
            Format(fmd, "YYYY,M,D") & ") to Date(" & _
            Format(tod, "YYYY,M,D") & ")"
            CrystalReport1.ReplaceSelectionFormula (strSelectionFormula)
            CrystalReport1.Action = 1
           With CrystalReport1
  strSelectionFormula = "{ST030100.ST03015} in Date(" & _
  Format(fmd, "YYYY,M,D") & ") to Date(" & _
  Format(tod, "YYYY,M,D") & ")"
  CrystalReport1.ReplaceSelectionFormula (strSelectionFormula)
  CrystalReport1.GroupSelectionFormula = "{ST030100.ST03018}='" & cmbProd.Text & "'"
  CrystalReport1.Action = 1
           End With
   CrystalReport1.GroupSelectionFormula = " "
End If
End Sub

Kindly reply me and suggest me how to solve it, if its possible correct it.

Thank you very much,


<B><font color="#008000">HAVE A NICE DAY !</font></B>

Edited by - chs on 1/8/2003 1:04:48 AM

Extracting Date
I am trying to create a simple (so far) calendar, i need to extract date from the system and set the calander at form load. I cant figure out how to read just the day or just the month and send it to the calendar props.. Thanks guys for any help.

Extracting Month From Date
I been trying to extract the month from a date I entered in a Text box but I can't. All I can do was to extract the month only if I get the system's date and display it in the Text box. But when entering my own date, that is a problem. Please help!

Extracting A File Date
Hi all,

I have some files that are named like this:


How can I extract the date (20041103) from this? I am using scripting object to do stuff with files.


Extracting The DD Portion From A Date Cell
Good Day,

I have a certain cell with todays date and I would like to extract from it the DD portion, preferably as a number.
How can I do that?

Thanks a lot,

Copyright 2005-08, All rights reserved