SQL - SMALLDATETIMEFROMPARTS() Function

---

---

The SQL SMALLDATETIMEFROMPARTS() function is used to construct a new datetime value from individual segments provided as parameters to this function.

This function accepts five parameters − year, month, day, hour, and minute and returns a datetime value; it returns an error if any of the parameters are missing.

• It returns an error if any of the parameters are missing.

• If the values passed as parameters are invalid, this function will result in an error.

• If any of the parameters are null, it will return the result as null.

Syntax

Following is the syntax of the SQL SMALLDATETIMEFROMPARTS() function −

SMALLDATETIMEFROMPARTS(year, month, day, hour, minute)

Parameters

This function accepts five parameters. The same is described below −

• year − This specifies the year segment of the datetime2 value, expressed as an integer.

• month − This specifies the month segment of the datetime2 value, expressed as an integer from 1 to 12.

• day − This specifies the day segment of the datetime2 value, expressed as an integer from 1 to 31.

• hour − This specifies the hour segment of the datetime2 value, expressed as an integer from 0 to 23.

• minute − This specifies the minute segment of the datetime2 value, expressed as an integer from 0 to 59.

Example

The following example demonstrates the usage of the SQL SMALLDATETIMEFROMPARTS() function −

SQL> SELECT SMALLDATETIMEFROMPARTS(2023, 02, 18, 16, 30) AS RESULT;	

Output

On executing the above query, the output is displayed as follows −

+---------------------+
| RESULT              |
+---------------------+
| 2023-02-18 16:30:00 |
+---------------------+

Example

If we provide invalid values to any of the parameters of the function, it results an error.

SQL> SELECT SMALLDATETIMEFROMPARTS(2023, 02, 34, 87, 72) AS RESULT;

Error

If we execute the program, the result is produced as follows −

Cannot construct data type datetime, some of the arguments have values which are not valid.

Example

If we provide any of the parameters of the function as null, the function will result as null.

SQL> SELECT SMALLDATETIMEFROMPARTS (2023, 02, null, 23, null) AS RESULT;   

Output

When we execute the above query, the output is obtained as follows −

+-----------+
| DATE_TIME |
+-----------+
| NULL      |
+-----------+

Example

We need to provide all FIVE parameters of the function. If we do not provide it, the function will result in an error.

SQL> SELECT SMALLDATETIMEFROMPARTS(2023, 02, 18) AS DATE_TIME;  

Error

If we execute the above query, the result is produced as follows −

The smalldatetimefromparts function requires 5 argument(s)..

Example

Here, we are providing the leap year date as parameters to the function using the following query −

SQL> SELECT SMALLDATETIMEFROMPARTS (2024, 02, 29, 19, 11) AS RESULT

Output

When we execute the above query, the output is obtained as follows −

+---------------------+
| RESULT              |
+---------------------+
| 2024-02-29 19:11:00 |
+---------------------+

Example

In the following example, we are providing 29 as the date and the year 2023 is not a leap year. So, the function will return an error.

SQL> SELECT SMALLDATETIMEFROMPARTS (2023, 02, 29, 19, 11) AS RESULT

Error

If we execute the program, the result is produced as follows −

Cannot construct data type datetime, some of the arguments have values which are not valid.

Example

Assume we have created a table with the name STUDENTS in the SQL database using the CREATE statement as shown in the query below −

SQL> CREATE TABLE STUDENTS(ID INT NOT NULL, NAME VARCHAR (200) NOT NULL, YEAR VARCHAR (200) NOT NULL, MONTH VARCHAR (200) NOT NULL, DAY VARCHAR (200) NOT NULL, HOURS VARCHAR (200) NOT NULL, MINUTES VARCHAR (200) NOT NULL)

Now, let us insert some records in the STUDENTS table using INSERT statements as shown in the query below −

SQL> INSERT INTO STUDENTS(ID, NAME, YEAR, MONTH, DAY, HOURS, MINUTES) VALUES(1, 'Dhruv', '2023', '01', '01', '14', '23');
INSERT INTO STUDENTS(ID, NAME, YEAR, MONTH, DAY, HOURS, MINUTES) VALUES(2, 'Arjun', '2023', '02', '01', '05', '43');
INSERT INTO STUDENTS(ID, NAME, YEAR, MONTH, DAY, HOURS, MINUTES) VALUES(3, 'Dev', '2023', '03', '01', '06', '11');
INSERT INTO STUDENTS(ID, NAME, YEAR, MONTH, DAY, HOURS, MINUTES) VALUES(4, 'Riya', '2023', '04', '01', '07', '21');
INSERT INTO STUDENTS(ID, NAME, YEAR, MONTH, DAY, HOURS, MINUTES) VALUES(5, 'Aarohi', '2023', '08', '01', '08', '54');
INSERT INTO STUDENTS(ID, NAME, YEAR, MONTH, DAY, HOURS, MINUTES) VALUES(6, 'Lisa', '2023', '09', '01', '09', '32');
INSERT INTO STUDENTS(ID, NAME, YEAR, MONTH, DAY, HOURS, MINUTES) VALUES(7, 'Roy', '2023', '10', '01', '10', '01');

We can verify whether the table is created or not using the following query −

SQL> SELECT * FROM STUDENTS

The table STUDENTS is successfully created in the SQL database.

+----+--------+------+-------+-----+-------+---------+
| ID |  NAME  | YEAR | MONTH | DAY | HOURS | MINUTES |
+----+--------+------+-------+-----+-------+---------+
| 1  | Dhruv  | 2023 | 01    | 01  | 14    | 23      |
| 2  | Arjun  | 2023 | 02    | 01  | 05    | 43      |
| 3  | Dev    | 2023 | 03    | 01  | 06    | 11      |
| 4  | Riya   | 2023 | 04    | 01  | 07    | 21      |
| 5  | Aarohi | 2023 | 08    | 01  | 08    | 54      |
| 6  | Lisa   | 2023 | 09    | 01  | 09    | 32      |
| 7  | Roy    | 2023 | 10    | 01  | 10    | 01      |
+----+--------+------+-------+-----+-------+---------+

Here, we are trying to join all the date and time values of the students using the following query −

SQL> SELECT NAME, SMALLDATETIMEFROMPARTS(YEAR, MONTH, DAY, HOURS, MINUTES) AS SMALLDATETIMEFROMPARTS_VALUE FROM STUDENTS;

Output

When we execute the above query, the output is obtained as follows −

+--------+-------------------------+
| NAME   | DATETIMEFROMPARTS_VALUE |
+--------+-------------------------+
| Dhruv  | 2023-01-01 14:23:00     |
| Arjun  | 2023-02-01 05:43:00     |
| Dev    | 2023-03-01 06:11:00     |
| Riya   | 2023-04-01 07:21:00     |
| Aarohi | 2023-08-01 08:54:00     |
| Lisa   | 2023-09-01 09:32:00     |
| Roy    | 2023-10-01 10:01:00     |
+--------+-------------------------+