Scribd
Upload
5 views3 pages

SQL Date and Conversion Functions Guide

The document provides SQL queries for date functions and conversion functions. It includes examples for displaying the current date, inserting employee records, calculating days worked, and manipulating dates. Additionally, it covers creating a students table, converting data types, and performing calculations with marks and strings.

Uploaded by

jamshed101jutt
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views3 pages

SQL Date and Conversion Functions Guide

The document provides SQL queries for date functions and conversion functions. It includes examples for displaying the current date, inserting employee records, calculating days worked, and manipulating dates. Additionally, it covers creating a students table, converting data types, and performing calculations with marks and strings.

Uploaded by

jamshed101jutt
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Assignment: Date Functions &

Conversion Functions in SQL


🔶 Part A: Date Functions

Q1. Display the current date and time:


```sql
SELECT NOW() AS current_datetime;
```

Q2. Insert employee records and perform date-based operations:


```sql
CREATE TABLE employees (
id INT,
name VARCHAR(50),
joining_date DATE,
birth_date DATE
);

INSERT INTO employees VALUES


(1, 'Ali', '2021-05-10', '1998-06-15'),
(2, 'Sara', '2020-08-20', '1997-08-25'),
(3, 'Ahmed', '2023-01-12', '2000-01-05'),
(4, 'Ayesha', '2019-11-30', '1995-06-20'),
(5, 'Bilal', '2022-03-01', '1999-03-10');
```

a) Retrieve the year of joining:

```sql
SELECT name, YEAR(joining_date) AS joining_year FROM employees;
```

b) Calculate how many days each employee has been working till today:

```sql
SELECT name, DATEDIFF(CURDATE(), joining_date) AS days_worked FROM employees;
```
Q3. Employees whose birthday is in the current month:
```sql
SELECT name, birth_date
FROM employees
WHERE MONTH(birth_date) = MONTH(CURDATE());
```

Q4. Difference in days between two dates using DATEDIFF():


```sql
SELECT DATEDIFF('2025-12-31', '2025-01-01') AS days_difference;
```

Q5. Add 30 days to a given date using DATE_ADD():


```sql
SELECT DATE_ADD('2025-06-19', INTERVAL 30 DAY) AS new_date;
```

🔶 Part B: Conversion Functions

Q6. Create table students and convert marks to INTEGER:


```sql
CREATE TABLE students (
id INT,
name VARCHAR(50),
marks VARCHAR(10)
);

INSERT INTO students VALUES


(1, 'Junaid', '85'),
(2, 'Fatima', '90'),
(3, 'Kashif', '78'),
(4, 'Hira', '88'),
(5, 'Zain', '92');

-- a) Convert marks to INTEGER and calculate average


SELECT AVG(CAST(marks AS UNSIGNED)) AS average_marks FROM students;
```

Q7. Convert a decimal number into string using CAST():


```sql
SELECT CAST(123.456 AS CHAR) AS string_value;
```
Q8. Use CONVERT() to get only date or time from a DATETIME:
```sql
SELECT
CONVERT(NOW(), DATE) AS only_date,
CONVERT(NOW(), TIME) AS only_time;
```

Q9. Use CAST() to round a float to integer:


```sql
SELECT CAST(ROUND(45.8973) AS SIGNED) AS rounded_integer;
```

Q10. Convert string to uppercase and then to another data type:


```sql
SELECT
CAST(UPPER('hello world') AS CHAR) AS uppercase_string,
CAST(UPPER('12345') AS UNSIGNED) AS numeric_conversion;
```

Common questions

Powered by AI

To calculate how many days each employee has worked till today, the DATEDIFF() function can be used. It calculates the difference in days between two dates — the current date (CURDATE()) and the joining_date of each employee. The query is: SELECT name, DATEDIFF(CURDATE(), joining_date) AS days_worked FROM employees;

To round a float to the nearest integer and then convert it to an integer type in SQL, you can use the ROUND() function together with CAST(). For example, to round 45.8973 and convert it, the query is: SELECT CAST(ROUND(45.8973) AS SIGNED) AS rounded_integer;

To retrieve the year each employee joined the company, you can use the YEAR() function in SQL to extract the year from the joining_date column in the employees table. The query would be: SELECT name, YEAR(joining_date) AS joining_year FROM employees;

First, you can convert a string to uppercase using the UPPER() function and then convert it to an unsigned integer using CAST(). For example, to convert '12345', the query is: SELECT CAST(UPPER('12345') AS UNSIGNED) AS numeric_conversion;

To convert a decimal number to a string in SQL, you can use the CAST() function. For example, to convert the decimal 123.456 to a string, the query would be: SELECT CAST(123.456 AS CHAR) AS string_value;

Identifying employees whose birthday falls in the current month can be achieved using the MONTH() function to compare the month part of the birth_date column with the current month from CURDATE(). The query is: SELECT name, birth_date FROM employees WHERE MONTH(birth_date) = MONTH(CURDATE())

To calculate the average of student marks when they are stored as VARCHAR, you need to convert these VARCHAR values to UNSIGNED (or INTEGER) first using CAST(). Then, use the AVG() function to calculate the average. The query would be: SELECT AVG(CAST(marks AS UNSIGNED)) AS average_marks FROM students;

To calculate the difference in days between two dates, January 1, 2025, and December 31, 2025, you can use the DATEDIFF() function in SQL. The query is: SELECT DATEDIFF('2025-12-31', '2025-01-01') AS days_difference;

Using the DATE_ADD() function in SQL allows you to add a specified interval to a date. To add 30 days to the date '2025-06-19', you can use the query: SELECT DATE_ADD('2025-06-19', INTERVAL 30 DAY) AS new_date;

You can use the CONVERT() function in SQL to extract either the date or the time from a DATETIME value. To get only the date, use CONVERT(NOW(), DATE), and to get only the time, use CONVERT(NOW(), TIME). The query is: SELECT CONVERT(NOW(), DATE) AS only_date, CONVERT(NOW(), TIME) AS only_time;

You might also like