Date Configuration in SQL
SQL represents Organized Inquiry Language. It allows you to get to and control data sets. SQL turned into a norm of the American Public Guidelines Establishment (ANSI) in 1986 and the Global Association for Normalization (ISO) in 1987. It can embed, erase, modify, update records in a data set. This article will examine the date design in SQL.
SQL Date Time Configuration Information Types
The accompanying kinds of information are accessible in SQL Server for putting away Date or date/time values in the data set:
DATE – design: YYYY-MM-DD
DATETIME – design: YYYY-MM-DD HH:MI:SS
TIMESTAMP – design: YYYY-MM-DD HH:MI:SS
YEAR – design YYYY or YY
Expect that we have the accompanying ‘clients’ table:
Note: The configuration of the date you are attempting to embed ought to match the date section design in the data set.
Tip: To keep the questions straightforward and simple to keep up with, don’t permit time parts in your date.
DATE_FORMAT()
The DATE_FORMAT() capabilities organizes a date as indicated.
Grammar: DATE_FORMAT(date,format)
Boundary Values
The table given beneath will make sense of the different date designs in SQL.
Design
Portrayal
%a
Condensed work day name (Sun to Sat)
%b
Truncated month name (Jan to Dec)
%c
Numeric month name (0 to 12)
%d
Day of the month as a numeric worth (01 to 31)
%e
Day of the month as a numeric worth (0 to 31)
%f
Microseconds (000000 to 999999)
%H
Hour (00 to 23)
%h
Hour (00 to 12)
%I
Minutes (00 to 59)
%j
Day of the year (001 to 366)
%k
Hour (0 to 23)
%M
Month name in full (Jan to Dec)
%m
Month name as a numeric worth (00 to 12)
%W
Work day name in full (Sunday to Saturday)
%T
Time in 24 hrs design (hh:mm:ss)
%U
Seven days where Sunday is the primary day of the week (00 to 53)
In SQL, working with date arrangements can include a few perspectives: questioning, designing, and changing over dates. Here is a fast manual for dealing with dates in SQL:
### 1. Normal Date Capabilities in SQL
#### 1.1 Recovering the Ongoing Date and Time
– **MySQL**:
”’sql
SELECT NOW(); – – Returns current date and time
SELECT CURDATE(); – – Returns current date
”’
– **PostgreSQL**:
”’sql
SELECT CURRENT_TIMESTAMP; – – Returns current date and time
SELECT CURRENT_DATE; – – Returns current date
”’
– **SQL Server**:
”’sql
SELECT GETDATE(); – – Returns current date and time
SELECT GETUTCDATE(); – – Returns current UTC date and time
”’
#### 1.2 Designing Dates
– **MySQL**:
”’sql
SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d’); – – Configurations date as ‘YYYY-MM-DD’
”’
– **PostgreSQL**:
”’sql
SELECT TO_CHAR(NOW(), ‘YYYY-MM-DD’); – – Arrangements date as ‘YYYY-MM-DD’
”’
– **SQL Server**:
”’sql
SELECT FORMAT(GETDATE(), ‘yyyy-MM-dd’); – – Arrangements date as ‘YYYY-MM-DD’
”’
#### 1.3 Switching Strings over completely to Dates
– **MySQL**:
”’sql
SELECT STR_TO_DATE(’01-12-2020′, ‘%d-%m-%Y’); – – Converts string to date
”’
– **PostgreSQL**:
”’sql
SELECT TO_DATE(’01-12-2020′, ‘DD-MM-YYYY’); – – Converts string to date
”’
– **SQL Server**:
”’sql
SELECT CONVERT(DATE, ‘2020-12-01′, 103); – – Converts string to date (103 determines English/French arrangement)
”’
### 2. Date Math
#### 2.1 Adding/Taking away Days
– **MySQL**:
”’sql
SELECT NOW() + Stretch multi DAY; – – Adds 7 days
SELECT NOW() – Span multi DAY; – – Deducts 7 days
”’
– **PostgreSQL**:
”’sql
SELECT NOW() + Span ‘7 days’; – – Adds 7 days
SELECT NOW() – Stretch ‘7 days’; – – Deducts 7 days
”’
– **SQL Server**:
”’sql
SELECT DATEADD(DAY, 7, GETDATE()); – – Adds 7 days
SELECT DATEADD(DAY, – 7, GETDATE()); – – Takes away 7 days
”’
### 3. Separating Portions of a Date
– **MySQL**:
”’sql
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()); – – Concentrates year, month, day
”’
– **PostgreSQL**:
”’sql
SELECT EXTRACT(YEAR FROM NOW()), EXTRACT(MONTH FROM NOW()), EXTRACT(DAY FROM NOW()); – – Concentrates year, month, day
”’
– **SQL Server**:
”’sql
SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()); – – Concentrates year, month, day
”’
### 4. Taking care of Various Date Configurations
While working with dates in different arrangements, you might have to deal with changes among strings and dates. The capabilities gave above, for example, ‘STR_TO_DATE’, ‘TO_DATE’, and ‘CONVERT’, can assist with dealing with these transformations. It’s urgent to comprehend the date design specifiers utilized in your SQL lingo.
#### Date Configuration Specifiers:
– ‘%Y’ = Year with century (e.g., 2024)
– ‘%y’ = Year without century (e.g., 24)
– ‘%m’ = Month (01-12)
– ‘%d’ = Day of the month (01-31)
– ‘%H’ = Hour (00-23)
– ‘%i’ = Minutes (00-59)
– ‘%s’ = Seconds (00-59)
Here is an instance of utilizing these specifiers:
**MySQL**:
”’sql
SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%s’); – – Configurations date and time
”’
By dominating these capabilities and arrangement specifiers, you can successfully oversee dates in SQL across different data sets.
Changing the date and time design in SQL can be achieved in different ways relying upon the SQL data set administration framework (DBMS) you’re utilizing (e.g., MySQL, SQL Server, PostgreSQL, Prophet). Here are a few normal techniques for various DBMSs:
### 1. MySQL
In MySQL, you can utilize the ‘DATE_FORMAT’ capability to organize dates.
**Example:**
”’sql
SELECT DATE_FORMAT(date_column, ‘%Y-%m-%d %H:%i:%s’) AS formatted_date
FROM table_name;
”’
– ‘%Y’ – Year (four digits)
– ‘%m’ – Month (two digits)
– ‘%d’ – Day (two digits)
– ‘%H’ – Hour (00-23)
– ‘%i’ – Minutes (00-59)
– ‘%s’ – Seconds (00-59)
### 2. SQL Server
In SQL Server, you can utilize the ‘Configuration’ capability (accessible from SQL Server 2012) or ‘CONVERT’ capability for prior renditions.
**Utilizing ‘FORMAT’:**
”’sql
SELECT FORMAT(date_column, ‘yyyy-MM-dd HH:mm:ss’) AS formatted_date
FROM table_name;
”’
**Utilizing ‘CONVERT’:**
”’sql
SELECT CONVERT(VARCHAR, date_column, 120) AS formatted_date
FROM table_name;
”’
– ‘120’ is the style code for ‘yyyy-mm-dd hh:mi:ss’.
### 3. PostgreSQL
In PostgreSQL, you can utilize the ‘TO_CHAR’ capability.
**Example:**
”’sql
SELECT TO_CHAR(date_column, ‘YYYY-MM-DD HH24:MI:SS’) AS formatted_date
FROM table_name;
”’
– ‘YYYY’ – Year (four digits)
– ‘MM’ – Month (two digits)
– ‘DD’ – Day (two digits)
– ‘HH24’ – Hour (00-23)
– ‘MI’ – Minutes (00-59)
– ‘SS’ – Seconds (00-59)
### 4. Prophet
In Prophet, you can utilize the ‘TO_CHAR’ capability to design dates.
**Example:**
”’sql
SELECT TO_CHAR(date_column, ‘YYYY-MM-DD HH24:MI:SS’) AS formatted_date
FROM table_name;
”’
– ‘YYYY’ – Year (four digits)
– ‘MM’ – Month (two digits)
– ‘DD’ – Day (two digits)
– ‘HH24’ – Hour (00-23)
– ‘MI’ – Minutes (00-59)
– ‘SS’ – Seconds (00-59)
### General Tips
– Continuously check the documentation of your particular DBMS for the specific capabilities and arranging choices accessible.
– Guarantee the segment ‘date_column’ is of a date/time type, any other way, you might have to project it first.
– Different DBMSs have various capabilities and grammar, so the specific strategy can change.
On the off chance that you really want any further help with a particular DBMS or use case, if it’s not too much trouble, let me know!
Certainly, here are a few instances of SQL date designing in different SQL data sets. Different SQL information bases have marginally unique sentence structure and works for date designing.
### SQL Server (T-SQL)
**1. Convert date to string:**
”’sql
SELECT CONVERT(VARCHAR, GETDATE(), 101) AS mmddyyyy
”’
**2. Design date utilizing ‘Configuration’ function:**
”’sql
SELECT FORMAT(GETDATE(), ‘yyyy-MM-dd’) AS FormattedDate
”’
### MySQL
**1. Convert date to string utilizing ‘DATE_FORMAT’:**
”’sql
SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d’) AS FormattedDate
”’
**2. Custom formats:**
”’sql
SELECT DATE_FORMAT(NOW(), ‘%W, %M %e, %Y’) AS CustomFormattedDate
”’
### PostgreSQL
**1. Convert date to string utilizing ‘TO_CHAR’:**
”’sql
SELECT TO_CHAR(NOW(), ‘YYYY-MM-DD’) AS FormattedDate
”’
**2. Custom formats:**
”’sql
SELECT TO_CHAR(NOW(), ‘Day, DD Month YYYY’) AS CustomFormattedDate
”’
### Prophet
**1. Convert date to string utilizing ‘TO_CHAR’:**
”’sql
SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD’) AS FormattedDate FROM double
”’
**2. Custom formats:**
”’sql
SELECT TO_CHAR(SYSDATE, ‘Day, DD Month YYYY’) AS CustomFormattedDate FROM double
”’
### SQLite
**1. Convert date to string utilizing ‘strftime’:**
”’sql
SELECT strftime(‘%Y-%m-%d’, ‘presently’) AS FormattedDate
”’
**2. Custom formats:**
”’sql
SELECT strftime(‘%A, %d %B %Y’, ‘presently’) AS CustomFormattedDate
”’
These models tell the best way to design dates in a few normal SQL data sets. The capabilities utilized (‘CONVERT’, ‘Organization’, ‘DATE_FORMAT’, ‘TO_CHAR’, ‘strftime’) take into consideration an assortment of custom date designs.
Of course, I’d be eager to assist with SQL! Might you at any point if it’s not too much trouble, give more insights concerning what you want help with? The following are a couple of normal subjects:
1. **Basic SQL Queries** (SELECT, Supplement, UPDATE, Erase)
2. **SQL Joins** (Internal JOIN, LEFT JOIN, RIGHT JOIN, FULL External JOIN)
3. **SQL Functions** (COUNT, Aggregate, AVG, MIN, Worst case scenario)
4. **SQL Gathering By and Having Clauses**
5. **SQL Subqueries and Settled Queries**
6. **SQL Lists and Execution Tuning**
7. **SQL Information base Plan and Normalization**
8. **SQL Put away Techniques and Triggers**
Go ahead and determine your inquiry or depict the issue you’re attempting to tackle!
The ‘DATE_FORMAT()’ capability in SQL is utilized to design a date as determined by a configuration string. This capability is especially helpful when you need to show a date in a particular organization as opposed to the default design utilized by your data set administration framework.
Here is the essential punctuation for the ‘DATE_FORMAT()’ capability in SQL (involving MySQL for instance):
”’sql
DATE_FORMAT(date, design)
”’
– ‘date’: The date esteem you need to organize.
– ‘design’: The configuration wherein you need to show the date. This is a string that indicates how the date ought to be organized.
### Normal Configuration Specifiers
Here are some normal organization specifiers utilized in the ‘design’ string:
– ‘%Y’: Year, numeric, four digits (e.g., 2024)
– ‘%y’: Year, numeric, two digits (e.g., 24)
– ‘%m’: Month, numeric (e.g., 06)
– ‘%b’: Month name, shortened (e.g., Jun)
– ‘%M’: Month name, full (e.g., June)
– ‘%d’: Day of the month, numeric (e.g., 03)
– ‘%H’: Hour (00-23)
– ‘%h’: Hour (01-12)
– ‘%i’: Minutes (00-59)
– ‘%s’: Seconds (00-59)
– ‘%p’: AM or PM
### Models
1. **Format Date as ‘YYYY-MM-DD’**
”’sql
SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d’);
”’
Yield: ‘2024-06-03’
2. **Format Date as ‘Month DD, YYYY’**
”’sql
SELECT DATE_FORMAT(NOW(), ‘%M %d, %Y’);
”’
Yield: ‘June 03, 2024’
3. **Format Date as ‘DD-MM-YY’**
”’sql
SELECT DATE_FORMAT(NOW(), ‘%d-%m-%y’);
”’
Yield: ’03-06-24′
4. **Format Time as ‘HH:MM:SS AM/PM’**
”’sql
SELECT DATE_FORMAT(NOW(), ‘%h:%i:%s %p’);
”’
Yield: ’08:45:23 AM’
### Utilization in a Question
Here is an instance of utilizing ‘DATE_FORMAT()’ in a question to design the birthdate of clients:
”’sql
SELECT
first_name,
last_name,
DATE_FORMAT(birthdate, ‘%M %d, %Y’) AS formatted_birthdate
FROM
clients;
”’
This question will choose the main name, last name, and the birthdate arranged as ‘Month Day, Year’ from the ‘clients’ table.
The ‘DATE_FORMAT()’ capability is a useful asset for date control and show, taking into consideration an extensive variety of organizing choices to suit your particular requirements.
Sure! SQL gives different ways of organizing dates relying upon the SQL data set framework you’re utilizing. Here are models for the absolute most generally utilized frameworks: MySQL, PostgreSQL, SQL Server, and Prophet.
### MySQL
1. **Date Format**
”’sql
SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d’) AS formatted_date;
”’
2. **DateTime Format**
”’sql
SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%s’) AS formatted_datetime;
”’
3. **Custom Formats**
”’sql
SELECT DATE_FORMAT(NOW(), ‘%a %D %b %Y’) AS custom_format;
”’
### PostgreSQL
1. **Date Format**
”’sql
SELECT TO_CHAR(NOW(), ‘YYYY-MM-DD’) AS formatted_date;
”’
2. **DateTime Format**
”’sql
SELECT TO_CHAR(NOW(), ‘YYYY-MM-DD HH24:MI:SS’) AS formatted_datetime;
”’
3. **Custom Formats**
”’sql
SELECT TO_CHAR(NOW(), ‘Dy DD Mon YYYY’) AS custom_format;
”’
### SQL Server
1. **Date Format**
”’sql
SELECT FORMAT(GETDATE(), ‘yyyy-MM-dd’) AS formatted_date;
”’
2. **DateTime Format**
”’sql
SELECT FORMAT(GETDATE(), ‘yyyy-MM-dd HH:mm:ss’) AS formatted_datetime;
”’
3. **Custom Formats**
”’sql
SELECT FORMAT(GETDATE(), ‘ddd dd MMM yyyy’) AS custom_format;
”’
### Prophet
1. **Date Format**
”’sql
SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD’) AS formatted_date FROM double;
”’
2. **DateTime Format**
”’sql
SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) AS formatted_datetime FROM double;
”’
3. **Custom Formats**
”’sql
SELECT TO_CHAR(SYSDATE, ‘Dy DD Mon YYYY’) AS custom_format FROM double;
”’
### Breakdown of Organization Specifiers
– **%Y/YYYY**: Year with century (e.g., 2024)
– **%m/MM**: Month (01-12)
– **%d/DD**: Day of the month (01-31)
– **%H/HH24**: Hour (00-23)
– **%I/MI**: Minutes (00-59)
– **%s/SS**: Seconds (00-59)
– **%a/Dy**: Curtailed work day name (e.g., Mon)
– **%b/Mon**: Truncated month name (e.g., Jan)
– **%D**: Day of the month with English postfix (e.g., first, second)
These models cover the nuts and bolts of date arranging in different SQL frameworks. Change the configuration specifiers as per your prerequisites and the SQL lingo you are utilizing.
Leave a Reply