The FORMAT function in various programming and database environments is used to convert date and number values into a specified string format. Here are some examples in common contexts:
1. In SQL Server
In SQL Server, the FORMAT function allows you to format dates and numbers. The syntax is:
FORMAT(value, format_string, [culture])
Example for Date:
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate; -- Outputs the current date in 'YYYY-MM-DD' format
Example for Numbers:
SELECT FORMAT(12345.6789, 'C', 'en-US') AS FormattedNumber; -- Outputs as '$12,345.68'
2. In Excel
In Excel, the TEXT function is often used to format values, but FORMAT can also be used in VBA.
Example for Date in Excel:
=TEXT(A1, "dd/mm/yyyy") -- Formats the date in cell A1 to 'DD/MM/YYYY'
Example for Numbers:
=TEXT(B1, "$ #,##0.00") -- Formats the number in cell B1 to a currency format
3. In Power BI / DAX
In Power BI, you can use the FORMAT function to format date and numeric values.
Example for Date:
FormattedDate = FORMAT(TODAY(), "MMMM DD, YYYY") -- Outputs a date like 'October 10, 2023'
Example for Numbers:
FormattedNumber = FORMAT(12345.678, "Currency") -- Formats the number as currency
4. In Python (Pandas)
In Python, you often use the .strftime() method for dates and string formatting for numbers.
Example for Date:
import pandas as pd
date = pd.to_datetime('2023-10-10')
formatted_date = date.strftime('%Y-%m-%d') # '2023-10-10'
Example for Numbers:
number = 12345.6789
formatted_number = "${:,.2f}".format(number) # '$12,345.68'
Summary
- The FORMAT function varies by the platform/language but generally follows the pattern of taking a value and a format string (and possibly a culture).
- For dates, typical formats include yyyy-MM-dd, MM/dd/yyyy, etc.
- For numbers, formats can include currency symbols (e.g., $ or €), comma separators for thousands, and fixed decimal places.
Always refer to the specific documentation for the platform you are using to see all available format options.