C SQL Date Time Functions PDF

Preview

Summary

Date, time funcitons...

Description

URL: https://www.w3schools.com/sql/func_msaccess_dateadd.asp

--------------------- SOME USEFUL SQL FUNCTIONS AND FORMATTING (JET-SQL) -------------------------

MS Access DateAdd() Function Example:

Add two years to a specified date: SELECT DateAdd("yyyy", 2, #22/11/2017#);

Definition and Usage The DateAdd() function returns a date after a certain time/date interval has been added.

Syntax

DateAdd(interval, number, date)

Parameter Values Parameter

Description Required. The time/date interval to add. Can be one of the following values: • • • • • • • • • •

interval

yyyy = Year q = Quarter m = month y = Day of the year d = Day w = Weekday ww = Week h = hour n = Minute s = Second

number

Required. The number of intervals to add

date

Required. The date to be modified

Technical Details Works in:

Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000

More Examples Example:

Add one year to the current system date: SELECT DateAdd("yyyy", 1, Date());

Example:

Add 6 months to the employees' birth date: SELECT LastName, DateAdd("m", 6, BirthDate) FROM Employees;

MS Access DateDiff() Function Example:

Return the difference between two dates (in years): SELECT DateDiff("yyyy", #13/01/1998#, #09/05/2017#);

Definition and Usage The DateDiff() function returns the difference between two date values, based on the interval specified.

Syntax

DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear)

Parameter Values Parameter

Description Required. The interval to use to calculate the difference between date1 and date2. Can be one of the following values:

interval

date1 and date2

• • • • • • • • • •

yyyy = Year q = Quarter m = month y = Day of the year d = Day w = Weekday ww = Week h = hour n = Minute s = Second

Required. The two dates to calculate the difference between Optional. Specifies the first day of the week. If omitted, Sunday is the first day of the week. Can be one of the following values:

firstdayofweek

• • • • • • • •

0 = Use the NLS API setting 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = Saturday

Optional. Specifies the first week of the year. If omitted, the week containing January 1st is the first week of year. Can be one of the following values: firstdayofyear

• • • •

0 = Use the NLS API setting 1 = Use the first week that includes Jan 1st (default) 2 = Use the first week in the year that has at least 4 days 3 = Use the first full week of the year

Technical Details Works in:

Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000

More Examples Example:

Return the difference between two dates (in months): SELECT DateDiff("m", #13/01/1998#, #09/05/2017#);

Example:

Return the difference between a specified date and today's date (in days): SELECT DateDiff("d", #13/01/1998#, Date());

MS Access Format() Function Example:

Format the "Price" column to currency: SELECT Format(Price, "Currency") AS FormattedPrice FROM Products;

Definition and Usage The Format() function formats a string expression.

Syntax

Format(expression, format)

Parameter Values Parameter expression

Description Required. The string expression to format Optional. The format to apply to the expression. Format

Description

General Number

Displays a number without thousand separators

Currency

Displays thousand separators as well as two decimal places

Fixed

Displays at least one digit to the left of the decimal place and two digits to the right of the decimal place

Standard

Displays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place

Percent

Displays a percent value - with a percent sign. Displays two digits to the right of the decimal place

Scientific

Scientific notation

Yes/No

Displays No if the number is 0 and Yes if the number is not 0

True/False

Displays False if the number is 0 and True if the number is not 0

On/Off

Displays Off if the number is 0 and On if the number is not 0

format

General Date Displays date based on your system settings Long Date

Displays date based on your system's long date settings

Medium Date

Displays date based on your system's medium date settings

Short Date

Displays date based on your system's short date settings

Long Time

Displays time based on your system's long time settings

Medium Time

Displays time based on your system's medium time settings

Short Time

Displays time based on your system's short time settings

Technical Details Works in:

Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000...