473,232 Members | 1,472 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,232 developers and data experts.

Working with Date and Time

Guido Geurs
767 Expert 512MB
These are some examples how to format Date and Time data.
Attached are doc's and other example, explaining how to work with Date and Time data.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub Form_Load()
  4. Dim MYDATE As Date
  5. Dim MYTIME As Date
  6. Dim MYDATETIME As Date
  7. Dim TEXTSTRING As String
  8.    With Me
  9.       .Caption = "Working with date and time_5. Formating"
  10.       .AutoRedraw = True
  11.       .Height = 13000
  12.       .Width = 10000
  13.       .Top = 0
  14.       .Left = 0
  15.    End With
  16.    MYDATE = "9/6/2004" ' weekday= thursday
  17.    MYTIME = "2:6:9"
  18.    MYDATETIME = "9/6/2004 2:6:9" ' weekday= thursday
  19.    Print "Format Function"
  20. ' User-Defined Date/Time Formats
  21.    Print "User-Defined Date/Time Formats"
  22. ' DATE
  23.    Print "== Date =="
  24.    Print "Format(MYDATE, "; """"; "dd/mm/yy"; """"; ") !! Date separator !! ==>"; _
  25.         Tab(80); Format(MYDATE, "dd/mm/yy")
  26.    Print "Format(MYDATETIME, "; """"; "dd-mm-yy"; """"; ") !! other characters may be used !! ==>"; _
  27.         Tab(80); Format(MYDATETIME, "dd-mm-yy")
  28.    Print "Format(MYDATE, "; """"; "d"; """"; ") !! number without a leading zero !! ==>"; _
  29.         Tab(80); Format(MYDATE, "d")
  30.    Print "Format(MYDATE, "; """"; "dd"; """"; ") !! number with a leading zero !! ==>"; _
  31.          Tab(80); Format(MYDATE, "dd")
  32.    Print "Format(MYDATE, "; """"; "ddd"; """"; ") !! abbreviation !! ==>"; _
  33.          Tab(80); Format(MYDATE, "ddd")
  34.    Print "Format(MYDATE, "; """"; "dddd"; """"; ") !! full name !! ==>"; _
  35.          Tab(80); Format(MYDATE, "dddd")
  36.    Print "Format(MYDATE, "; """"; "ddddd"; """"; ") !! complete date !! ==>"; _
  37.          Tab(80); Format(MYDATE, "ddddd")
  38.    Print "Format(MYDATE, "; """"; "dddddd"; """"; ") !! serial number as a complete date !! ==>"; _
  39.          Tab(80); Format(MYDATE, "dddddd")
  40.    Print "Format(MYDATE, "; """"; "w"; """"; ") !! day of the week as a number (first day of week: Sunday) !! ==>"; _
  41.          Tab(100); Format(MYDATE, "w")
  42.    Print "Format(MYDATE, "; """"; "w"; """"; ", vbThursday) !! day of the week as a number (first day of week: Thursday) !! ==>"; _
  43.          Tab(100); Format(MYDATE, "w", vbThursday)
  44.    Print "Format(MYDATE, "; """"; "ww"; """"; ") !! week of the year as a number (first week: with 1 Jan) !! ==>"; _
  45.          Tab(100); Format(MYDATE, "ww")
  46.    Print "Format(MYDATE, "; """"; "ww"; """"; ", , vbFirstFullWeek) !! first week: first full week !! ==>"; _
  47.          Tab(100); Format(MYDATE, "ww", , vbFirstFullWeek)
  48.    Print "Format(MYDATE, "; """"; "m"; """"; ") !! month as a number without a leading zero !! ==>"; _
  49.          Tab(80); Format(MYDATE, "m")
  50.    Print "Format(MYDATE, "; """"; "mm"; """"; ") !! month as a number with a leading zero !! ==>"; _
  51.          Tab(80); Format(MYDATE, "mm")
  52.    Print "Format(MYDATE, "; """"; "mmm"; """"; ") !! month as an abbreviation !! ==>"; _
  53.          Tab(80); Format(MYDATE, "mmm")
  54.    Print "Format(MYDATE, "; """"; "mmmm"; """"; ") !! month as a full month name !! ==>"; _
  55.          Tab(80); Format(MYDATE, "mmmm")
  56.    Print "Format(MYDATE, "; """"; "q"; """"; ") !! quarter of the year as a number !! ==>"; _
  57.          Tab(80); Format(MYDATE, "q")
  58.    Print "Format(MYDATE, "; """"; "y"; """"; ") !! day of the year as a number !! ==>"; _
  59.          Tab(80); Format(MYDATE, "y")
  60.    Print "Format(MYDATE, "; """"; "yy"; """"; ") !! year as a 2-digit numbe  !! ==>"; _
  61.          Tab(80); Format(MYDATE, "yy")
  62.    Print "Format(MYDATE, "; """"; "yyyy"; """"; ") !! year as a 4-digit number !! ==>"; _
  63.          Tab(80); Format(MYDATE, "yyyy")
  64. ' Time
  65.    Print "== Time =="
  66.    Print "Format(MYTIME, "; """"; "hh-nn-ss"; """"; ") !! Time separator !! ==>"; _
  67.         Tab(80); Format(MYTIME, "hh-nn-ss")
  68.    Print "Format(MYDATETIME, "; """"; "hh.nn.ss"; """"; ") !! Time separator !! ==>"; _
  69.         Tab(80); Format(MYDATETIME, "hh.nn.ss")
  70.    Print "Format(MYTIME, "; """"; "h"; """"; ") !! hour as a number without leading zeros !! ==>"; _
  71.         Tab(80); Format(MYTIME, "h")
  72.    Print "Format(MYTIME, "; """"; "hh"; """"; ") !! hour as a number with leading zeros !! ==>"; _
  73.         Tab(80); Format(MYTIME, "hh")
  74.    Print "Format(MYTIME, "; """"; "n"; """"; ") !! minute as a number without leading zeros !! ==>"; _
  75.         Tab(80); Format(MYTIME, "n")
  76.    Print "Format(MYTIME, "; """"; "nn"; """"; ") !! minute as a number with leading zeros !! ==>"; _
  77.         Tab(80); Format(MYTIME, "nn")
  78.    Print "Format(MYTIME, "; """"; "s"; """"; ") !! second as a number without leading zeros !! ==>"; _
  79.         Tab(80); Format(MYTIME, "s")
  80.    Print "Format(MYTIME, "; """"; "ss"; """"; ") !! second as a number with leading zeros !! ==>"; _
  81.         Tab(80); Format(MYTIME, "ss")
  82.    Print "Format(MYTIME, "; """"; "ttttt"; """"; ") !! complete time !! ==>"; _
  83.         Tab(80); Format(MYTIME, "ttttt")
  84.    Print "Format(MYTIME, "; """"; "h.n.s AM/PM"; """"; ") !! 12-hour clock and 'AM' before noon; 'PM' between noon and 11:59 P.M !! ==>"; _
  85.         Tab(110); Format(MYTIME, "h.n.s AM/PM")
  86.    Print "Format(MYTIME, "; """"; "h.n.s am/pm"; """"; ") !! 12-hour clock and 'am' before noon; 'pm' between noon and 11:59 P.M !! ==>"; _
  87.         Tab(110); Format(MYTIME, "h.n.s am/pm")
  88.    Print "Format(MYTIME, "; """"; "h.n.s A/P"; """"; ") !! 12-hour clock and 'A' before noon; 'P' between noon and 11:59 P.M !! ==>"; _
  89.         Tab(110); Format(MYTIME, "h.n.s A/P")
  90.    Print "Format(MYTIME, "; """"; "h.n.s a/p"; """"; ") !! 12-hour clock and 'a' before noon; 'p' between noon and 11:59 P.M !! ==>"; _
  91.         Tab(110); Format(MYTIME, "h.n.s a/p")
  92.    Print "Format(MYTIME, "; """"; "h.n.s AMPM"; """"; ") !! 12-hour clock, AM and PM string literal as defined by your system !! ==>"; _
  93.         Tab(110); Format(MYTIME, "h.n.s AMPM")
  94. ' Date and Time
  95.    Print "== Date and Time =="
  96.    Print "Format(MYDATETIME, "; """"; "c"; """"; ") !! date as ddddd and the time as ttttt !! ==>"; _
  97.         Tab(80); Format(MYDATETIME, "c")
  98.    Print "Format(MYDATETIME, "; """"; "dd-mm-yy h.n.s AM/PM"; """"; ") !! mix !! ==>"; _
  99.         Tab(80); Format(MYDATETIME, "dd-mm-yy h.n.s AM/PM")
  100.    Print "============================================"
  101. ' Predefined date and time format names
  102.    Print "Predefined date and time format names"
  103. ' DATE
  104.    Print "== Date =="
  105.    Print "Format(MYDATETIME, "; """"; "General Date"; """"; ") !! is determined by your system settings !! ==>"; _
  106.         Tab(110); Format(MYDATETIME, "General Date")
  107.    Print "Format(MYDATETIME, "; """"; "Long Date"; """"; ") !! according to your system's long date format !! ==>"; _
  108.         Tab(110); Format(MYDATETIME, "Long Date")
  109.    Print "Format(MYDATETIME, "; """"; "Medium Date"; """"; ") !! appropriate for the language version of the host application !! ==>"; _
  110.         Tab(110); Format(MYDATETIME, "Medium Date")
  111.    Print "Format(MYDATETIME, "; """"; "Short Date"; """"; ") !! using system's short date format !! ==>"; _
  112.         Tab(110); Format(MYDATETIME, "Short Date")
  113. ' Time
  114.    Print "== Time =="
  115.    Print "Format(MYDATETIME, "; """"; "Long Time"; """"; ") !! system's long time format !! ==>"; _
  116.         Tab(110); Format(MYDATETIME, "Long Time")
  117.    Print "Format(MYDATETIME, "; """"; "Medium Time"; """"; ") !! in 12-hour format using hours and minutes and the system AM/PM  !! ==>"; _
  118.         Tab(110); Format(MYDATETIME, "Medium Time")
  119.    Print "Format(MYDATETIME, "; """"; "Short Time"; """"; ") !! 24-hour format !! ==>"; _
  120.         Tab(110); Format(MYDATETIME, "Short Time")
  121.    Print "##########################################################"
  122.    Print "FormatDateTime Function"
  123. ' User-Defined Date/Time Formats
  124.    Print "User-Defined Date/Time Formats"
  125. ' DATE
  126.    Print "== Date =="
  127.    Print "FormatDateTime(MYDATETIME, vbGeneralDate) !! is determined by your system settings !! ==>"; _
  128.         Tab(110); FormatDateTime(MYDATETIME, vbGeneralDate)
  129.    Print "FormatDateTime(MYDATETIME, vbLongDate) !! according to your system's long date format !! ==>"; _
  130.         Tab(110); FormatDateTime(MYDATETIME, vbLongDate)
  131.    Print "FormatDateTime(MYDATETIME, vbShortDate) !! using system's short date format !! ==>"; _
  132.         Tab(110); FormatDateTime(MYDATETIME, vbShortDate)
  133. ' Time
  134.    Print "== Time =="
  135.    Print "FormatDateTime(MYDATETIME, vbLongTime) !! system's long time format !! ==>"; _
  136.         Tab(110); FormatDateTime(MYDATETIME, vbLongTime)
  137.    Print "FormatDateTime(MYDATETIME, vbShortTime) !! 24-hour format !! ==>"; _
  138.         Tab(110); FormatDateTime(MYDATETIME, vbShortTime)
  139. End Sub
Attached Files
File Type: zip Working with date and time_v1.1.2.zip (144.1 KB, 176 views)
Jun 16 '11 #1
0 3929

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: jason | last post by:
I need to deposit and empty dummy value into a date/time field within Access using INSERT. However, I pick up a data type error if I attempt to insert a NULL value for this particular date time...
21
by: Javier | last post by:
Hi Ive a routine that will read date and times in a vector of strings ie: 30/02/2005 19:20 In some moment Ill need to check if there are in vector a date like current date with a time...
1
by: E. Liepins | last post by:
I am working on a vehicle database. We track when a vehicle is borrowed and when it is returned. We also track the number of kilometres travelled on a particular trip. There are several tables:...
1
by: seash | last post by:
H when i execute this query, my query is crashing throwing an exception "cant convert char datatype to datetime type format my query is "insert into mytable(mydate) values ('"+ varmydate+ "')...
7
by: Jerome | last post by:
Hallo, I know a lot has already been told about date/time fields in a database but still confuses me, specif when dealing with SQLserver(Express). It seems that sqlserver only accepts the date in...
17
by: Franc Zabkar | last post by:
My D-Link DSL-302G modem/router has a real-time clock whose settings are volatile. To avoid hand keying the date/time via the modem's JS interface, I wonder if there is a way to copy the JS code to...
2
by: markric | last post by:
I recently posted this message to microsoft.public.dotnet.framework.aspnet.webservices, but received no response, so I'm trying here in this group. I could really use some feedback. I'm working...
4
by: SilentThunderer | last post by:
Hey folks, Let me start out by letting you know what I'm working with. I'm building an application in VB 2005 that is basically a userform that employees can use to "Clock in". The form...
6
by: Mike Copeland | last post by:
I am working with a file of data that contains "time values" (e.g. "1225365563"), and I want to convert this data into something I can use in a program I'm developing. From what I've read about...
13
by: dizzydangler | last post by:
Just a quick question...I'm running an MS Access 2007 db that tracks appointments in a single table. Date and time are entered as separate fields in short date (mm/dd/yyyy) and short time (hh:mm)...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.