When working with SQL, one of the most significant data types you'll come across is DateTime. DateTime, which represents dates and times, is critical for managing time-sensitive data such as event scheduling, transaction timestamps, and historical records. In this post, we will go deep into DateTime in SQL to help you become a professional SQL developer, looking at its features, functions, real-world examples, and recommended practices.

Understanding DateTime in SQL Server
DateTime is a SQL data type that combines date and time data into a single value. It is required for capturing temporal moments accurately. SQL databases provide a variety of DateTime data types, including DATE, TIME, DATETIME, and TIMESTAMP, each with its own function.

Date and Time Functions
With the help of SQL's DateTime methods, you have numerous options for handling and modifying date and time data. Understanding the syntax and breadth of applications for these functions is critical to realizing their full potential.

1. GETDATE() / CURRENT_TIMESTAMP
Retrieves the current date and time.

Syntax
GETDATE() | CURRENT_TIMESTAMP

Examples
SELECT GETDATE() AS CurrentDateTime;

Output

SELECT CURRENT_TIMESTAMP AS CurrentTimestamp;

Output

2. DATEADD()
Adds or subtracts a specified time interval to/from a DateTime value.

Syntax
DATEADD(interval, number, date)

interval: Specifies the unit of time (e.g., year, month, day) to add or subtract.
number: Represents the quantity of intervals to add (positive) or subtract (negative).
date: The starting date or time to which the operation is applied.

Example
SELECT DATEADD(year, 2, '2023-09-13') AS NewDate;In this example, we add 2 years to the given date, resulting in '2025-09-13'.

Example
SELECT DATEADD(day, -10, '2023-09-13') AS NewDate;

Output

Here, we subtract 10 days from the given date, resulting in '2023-09-03'.

Example
SELECT DATEADD(hour, 3, '2023-09-13 10:00:00') AS NewTime;

Output

This adds 3 hours to the given time, resulting in '2023-09-13 13:00:00'. As per the above example of DateAdd, we can modify the datetime value in different ways.

3. DATEDIFF()
Calculates the difference between two DateTime values in a specified unit (e.g., years, months, days).

Syntax
DATEDIFF(interval, start_date, end_date)

interval: Specifies the unit of time (e.g., year, month, day) to calculate the difference in.
start_date: The beginning date or time.
end_date: The ending date or time.

Example
SELECT DATEDIFF(day, '2023-09-10', '2023-09-13') AS DaysDifference;

Output

In this example, we calculate the difference in days between '2023-09-10' and '2023-09-13', resulting in '3'.

Example
SELECT DATEDIFF(month, '2023-01-15', '2023-09-20') AS MonthsDifference;

Output

Here, we calculate the difference in months between '2023-01-15' and '2023-09-20', resulting in '8'.

Example
SELECT DATEDIFF(year, '1995-08-19', GETDATE()) AS AgeInYears;

Output

In this example, we determine the age of a person born on '1995-08-19' by calculating the difference in years between their birthdate and the current date using GETDATE(). The result is the person's age in years.

4. CONVERT()
Converts DateTime values between different formats.

Syntax
CONVERT(data_type, expression, style)

data_type: Specifies the target data type to which you want to convert the expression.
expression: The value or column to be converted.
style: Defines the format for the conversion (optional).

Example
SELECT CONVERT(DATE, GETDATE()) AS DateOnly;

Output

In this example, we convert the current date and time obtained using GETDATE() into a Date data type. This results in extracting only the date portion, like '2023-09-13'.

Example
SELECT CONVERT(DATETIME, '2023-09-13 15:16:00', 120) AS ConvertedDateTime;

Output

Here, we convert the string '2023-09-13 15:16:00' into a DateTime data type using style '120'. This results in a DateTime value like '2023-09-13 15:16:00.000'.

5. FORMAT()
Formats DateTime values into user-friendly strings.

Syntax
FORMAT(expression, format)
expression: The value or column you want to format, often a DateTime value.
format: Specifies the desired format for the expression.

Example
SELECT FORMAT(GETDATE(), 'd') AS ShortDate;

Output

In this example, we format the current date and time obtained using GETDATE() into a short date format (MM/DD/YY or equivalent based on localization). The result could be something like '09/13/23'.

Example
SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy HH:mm:ss') AS CustomFormattedDateTime;

output

Here, we take the current date and time and format it into a custom string that includes the full month name, day, year, and time in the 'MMMM dd, yyyy HH:mm:ss' format. The result might look like 'September 13, 2023 15:23:52'.

Example
SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS TimeOnly;

Output

In this example, we format the current date and time into a time-only format (HH:mm:ss), showing only the hours, minutes, and seconds. The result could be something like '15:25:43'.

Upcoming Events
Retrieve events that are scheduled for the future.

CREATE TABLE Events (
    EventID INT PRIMARY KEY,
    EventName VARCHAR(100),
    EventDateTime DATETIME
);


INSERT INTO Events (EventID, EventName, EventDateTime)
VALUES
    (1, 'Tech Conference', '2023-09-20 14:30:00'),
    (2, 'Product Launch', '2023-10-05 09:00:00'),
    (3, 'Workshop of SQL', '2023-09-25 10:00:00'),
    (4, 'Seminar', '2023-11-15 15:45:00'),
    (5, 'Annual Conference', '2022-08-01 18:00:00'),
    (6, 'Annual MVP Seminar', '2022-10-14 11:30:00');


Select * From Events

SELECT EventName, EventDateTime
FROM Events WHERE EventDateTime > GETDATE() Order By EventDateTime;

Output

Best Practices

When working with DateTime functions in SQL, consider these best practices:

• Data Validation: Ensure that your DateTime values are valid to avoid unexpected results.
• Avoid Mixing Data Types: Be cautious when mixing different DateTime data types in calculations.
• Optimize Queries: Index DateTime columns for improved query performance, especially in large datasets.
• Handle Time Zones: Address time zone issues when dealing with international data.

Summary
DateTime type in SQL, enabling you to handle date and time-related data effectively. By understanding its properties, functions, and best practices, you can ensure data accuracy and precision in your SQL databases.

If you find this article valuable, please consider liking it and sharing your thoughts in the comments.

Thank you, and happy coding.

HostForLIFEASP.NET SQL Server 2022 Hosting

In SQL Server, you can use the MERGE statement to perform conditional INSERT, UPDATE, or DELETE operations in a single query. The MERGE statement is often used for synchronizing data between two tables or performing upserts (INSERT or UPDATE, depending on whether a matching row exists). Here's an example of how you can use the MERGE statement with multiple examples for each operation (INSERT, UPDATE, DELETE):

Assume you have two tables: TargetTable and SourceTable. We'll use these tables for our examples.
INSERT

Suppose you want to insert rows from SourceTable into TargetTable if they don't already exist in the target table.

MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name)
    VALUES (Source.ID, Source.Name);

This SQL code will insert rows from SourceTable into TargetTable where the ID doesn't match.
UPDATE

Suppose you want to update existing rows in TargetTable with data from SourceTable. When there's a match.
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name;

This code updates the Name column in TargetTable If there's a matching ID in SourceTable.
DELETE

Suppose you want to delete rows from TargetTable If they don't exist in SourceTable.
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

This code will delete rows from TargetTable where the ID doesn't match any in SourceTable.

Now, let's see how you can execute these MERGE statements with multiple examples.
-- Example 1: Insert
INSERT INTO SourceTable (ID, Name)
VALUES (1, 'John'), (3, 'Alice');

-- Example 2: Update
UPDATE SourceTable
SET Name = 'Bob'
WHERE ID = 2;

-- Example 3: Delete
DELETE FROM SourceTable
WHERE ID = 4;

-- Execute MERGE for all operations
MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.ID = Source.ID
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name)
    VALUES (Source.ID, Source.Name)
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

In this example, we first perform individual INSERT, UPDATE, and DELETE operations on the SourceTable. Then, we execute the MERGE statement to synchronize the TargetTable with the SourceTable using a single query. The MERGE statement handles all three operations based on the specified conditions.

HostForLIFEASP.NET SQL Server 2022 Hosting

In this post, we’ll explore the IIF SQL functionality. IIF (If and only if) function, and we’ll use the AdventureWorks database for our testing purposes. Let’s get started.
What’s IIF Function?

Introduced in SQL Server 2012
This function returns one of two values depending on the boolean expression [also known as condition] that evaluates true or false.
IIF is composed of the logical statement, the boolean expression known as the condition, followed by the “true” and the “false” expressions.
This function can be compared to CASE expressions and a shorthand way of writing a CASE expression.

IIF Syntax
-- Syntax
    IIF(boolean_expressions, true, false)
-- OR
    IIF(condition, true, false)

    Boolean expression or condition – this is required, and it is because of the value that needs to be tested.
    True - this is optional, but the value is returned if the condition is true.
    False - this is optional, but the value is returned if the condition is false.

Examples
1. Compare Numbers
-- Let's declare and initialize two numbers
DECLARE @NUM1 INT = 20;
DECLARE @NUM2 INT = 25

--OUTPUT: 25 is greater than 20
SELECT IIF( (@NUM2 > @NUM1), FORMATMESSAGE('%i is greater than %i', @NUM2, @NUM1),
                             FORMATMESSAGE('%i is greater than %i', @NUM1, @NUM2))
                             AS [ComparingNumbers];
--OUTPUT: TRUE
SELECT IIF(@NUM2 > @NUM1, 'TRUE', 'FALSE')  AS [ComparingNumbers];

SET @NUM1  = 120;
SET @NUM2  = 25;

--OUTPUT: 120 is greater than 25
SELECT IIF((@NUM1 > @NUM2), FORMATMESSAGE('%i is greater than %i', @NUM1, @NUM2),
                            FORMATMESSAGE('%i is greater than %i', @NUM2, @NUM1))
                             AS [ComparingNumbers];
--OUTPUT: TRUE
SELECT IIF(@NUM2 > @NUM1, 'FALSE', 'TRUE')  AS [ComparingNumbers];

As you can see from the example we have shown how we can easily compare numbers using the IIF function.

Output

2. Compare numbers within a table column
This section will try to explore some examples using the AdventureWorks database. Checking the Person Person table of the AdventureWorks database, you’ll see a NameStyle column. This column uses bit as its datatype. When it is 0, FirstName and LastName are stored in Western-style (first name, last name). Otherwise, when it is 1, it is stored in an Eastern-style (Lastname, first name) order.

Let’s try to use the IIF function here.
USE [AdventureWorks2019]
/** Let’s try to format the person’s names using either western-style or eastern-style by using IIF.*/
SELECT IIF(NameStyle = 0,
    CONCAT(FirstName, ', ' , LastName),
    CONCAT(LastName, ', ', FirstName))
FROM
[Person].[Person]

Output

3. Compare strings within a table column
In this example, we’ll try to get the Employee’s marital status by using the IIF function and joining the two tables [HumanResources].[Employee] and [Person].[Person] inside the AdventureWorks database. The column MaritalStatus uses the nchar(1) datatype, and M stands for married, while S stands for single.

Let’s see an example below.
USE [AdventureWorks2019]

SELECT TOP 10 IIF(MaritalStatus = 'M', 'Married', 'Single') as [Marital Status],
    FirstName,
    LastName
FROM [HumanResources].[Employee] E
INNER JOIN [Person].[Person] P
ON E.[BusinessEntityID] = P.[BusinessEntityID]

Output

4. Nested SQL IIF Statement
In this example, we’ll try to get the Person’s type by using a nested IIF function and checking the PersonType column, which uses nchar(2) as its datatype. Has many meanings.

Let’s see the list below.
    SC = Store Contact
    IN = Individual
    SP = Sales Person
    EM = Employee
    VC = Vendor Contact
    GC = General Contact

USE [AdventureWorks2019]
-- Randomly select rows in this table (10 percent)
SELECT TOP 10 PERCENT
       FirstName,
       LastName,
       IIF(PersonType = 'EM', 'Employee',
       IIF(PersonType = 'SC', 'Store Contact',
       IIF(PersonType = 'IN', 'Individual',
       IIF(PersonType = 'SP', 'Sales Person',
       IIF(PersonType=  'VC', 'Vendor Contact',
       IIF(PersonType = 'GC', 'General Contact', 'n/a')))))) AS [Type of Person]
FROM [Person].[Person]
ORDER BY NEWID()

Just a reminder, as you can see, the query sample is randomly selecting records on the [Person].[Person] table to see different results every time we execute the query. So your results will be further from the output shown below.

Output

5. SQL IIF and NULL Value
When dealing with the IIF function and passing a NULL value within both the true and false expressions/parameters, it will throw an exception. But before we see an example, we’ll be using the [Production].[Product] table as our example and let’s try to see the number of days to manufacture a certain product.
USE [AdventureWorks2019]
SELECT MIN(DaysToManufacture), Max (DaysToManufacture) FROM [Production].[Product]

If you will execute the query above, this will show us that the minimum number of days to manufacture a product is 0, and the maximum number of days to manufacture a product is 4 days.

Now, let’s try to use the IIF function and check if the [DaysOfManufacture] is zero and pass the value NULL to both parameters.
USE [AdventureWorks2019]
SELECT IIF(DaysToManufacture = 0, NULL, NULL) FROM [Production].[Product]

Output

It’s clearly saying that we need to have at least one true or false argument and be mindful next time not to encounter this exception.

Let’s see an example below.
USE [AdventureWorks2019]
SELECT DaysToManufacture,
         IIF(DaysToManufacture = 0, 'Fast to manufacture.', NULL) FROM [Production].[Product]

Output

Summary
In this article, we have discussed the following:
    What’s IIF Function
    IIF Syntax
    Examples
        Compare numbers
        Compare numbers within a table column
        Compare strings within a table column
        Nested SQL IIF Statement
        SQL IIF and NULL Value

Once again, I hope you have enjoyed reading this article/tutorial as much as I have enjoyed writing it.

Stay tuned for more. Until next time, happy programming!

Please don't forget to bookmark, like, and comment. Cheers! And Thank you!

HostForLIFEASP.NET SQL Server 2021 Hosting

In this article, I will guide you in how to take SQL Server Database backup to a local folder. There are two ways to take a database backup.

Method 1
Open SQL Server Management Studio (SSMS) and follow the below steps
Select the Database that you want to take backup.

For example, here I am using the EmployeeDB database.

Select Database. Right click on database -> select Task -> Back Up.

Once we click on Back up, a pop-up window will open. This window will show the Database name from which we are taking a backup. Select the backup type as Full, and back up to Disk.

Now click on the remove button and then click on Add. This will open one more popup window, which will allow us to choose our specific path. Now once we navigate to our path, we need to provide the database file name. In my case, I have given EmployeeDB.bak. and click on OK.

Note: ".bak" is an extension for backup.

Once we click Ok, our backup path will be set. Now click Ok.

Once we click on OK our database backup will be created to our provided path.

Now we can verify whether our database backup is created or not.

Method 2
Using SQL Query.
declare @backuppath as nvarchar(max)
set @backuppath  = N'C:\WorkingProjects\Practice\DataBase\Employee\EmployeeDB_'
+ CONVERT(nvarchar,YEAR(getdate()))
+ CONVERT(nvarchar,Month(getdate())) +
+ CONVERT(nvarchar,DAY(getdate())) + '.bak'
Backup Database [EmployeeDB] to DISK = @backuppath WITH NOFORMAT, NOINIT, NAME=N'EmployeeDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Now execute the query

Once the query is executed, it will create a database backup to the specified path.

Eg. In my case path is "C:\WorkingProjects\Practice\DataBase\Employee".

Now, we can verify whether our database backup is created or not.

HostForLIFEASP.NET SQL Server 2021 Hosting

This article demonstrates how we can easily rename various database objects like Tables, Columns, Constraints, Indexes, SP in SQL Server. You may have faced a scenario where sometimes we need to rename database objects to specify correct business significance and meaning to the system on production code. The database objects which were originally designed may not match the current business objects. To solve this problem, you may need to rename existing database objects like table name, column name, store procedure name, etc. The best and easiest way is to use SP_RENAME, a build-in stored procedure to rename these objects.

This approach is recommended because we can run pre-deployment scripts in the environment before deploying these changes.

SP_RENAME takes below arguments.

To demonstrates this, I am creating a table with Primary key, check constraint, non-clustered index and putting some data into this table. We will compare before and after snapshots.

CREATE TABLE OrderInfo
(
  Id INT IDENTITY(1, 1) NOT NULL,
  OrderMode VARCHAR(20) CONSTRAINT [CK_OrderInfo_OrderMode] CHECK (OrderMode IN ('ONLINE','OFFLINE')) NOT NULL,
  OrderName VARCHAR(100) NOT NULL,
  OrderDate DATETIME CONSTRAINT [DF_OrderInfo_OrderDate] DEFAULT (GETDATE()) NOT NULL,
  CONSTRAINT PK_OrderInfo_Id PRIMARY KEY NONCLUSTERED (Id ASC)
)

CREATE NONCLUSTERED INDEX IX_OrderInfo_OrderMode ON dbo.OrderInfo (OrderMode)

INSERT INTO  OrderInfo
VALUES
    ( 'ONLINE', 'Notebook', GETDATE()),
    ( 'ONLINE', 'PC', GETDATE()),
    ( 'OFFLINE', 'Printer', GETDATE())
GO

Before snapshot of table, constraints, and index.

Rename a Table
--Rename table OrderInfo to OrderSummary
EXEC SP_RENAME 'dbo.OrderInfo', 'OrderSummary'

Rename a Column
--Rename column Id to OrderSummaryId
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= 'OrderSummary' AND COLUMN_NAME='Id')
BEGIN
EXEC SP_RENAME 'dbo.OrderSummary.Id', 'OrderSummaryId'
END

Rename an Index
--REANME INDEX
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name='IX_OrderInfo_OrderMode' AND OBJECT_ID = OBJECT_ID('dbo.OrderSummary'))
BEGIN
EXEC SP_RENAME 'dbo.OrderSummary.IX_OrderInfo_OrderMode','IX_OrderSummary_OrderMode','INDEX';
END

Rename a Primary Key Constraint
--REANME PRIMARY KEY CONSTRAINT
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME ='PK_OrderInfo_Id')
BEGIN
EXEC SP_RENAME 'dbo.PK_OrderInfo_Id','PK_OrderSummary_OrderSummaryId','OBJECT';
END

Rename a Check Constraint
--REANME CHECK CONSTRAINT
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME ='CK_OrderInfo_OrderMode')
BEGIN
EXEC SP_RENAME 'dbo.CK_OrderInfo_OrderMode','CK_OrderSummary_OrderMode','OBJECT';
END

Rename a Stored Procedure
--RENAME SP
EXEC SP_RENAME 'dbo.spGetOrderInfoByOrderMode' , 'spGetOrderSummaryByOrderMode';

Let’s verify the changes have been made by issuing a SELECT against the table using new table name.

One thing to keep in mind is that when we rename these objects, we need to make changes in dependencies of these objects. For example – if you are renaming a table and that table is being used in multiple SPs then we also to modify those SPs as well. But that is a manual activity to find and fix. This warning represents the same thing.

Hope you find this information useful. Happy Learning!

In this article, we will learn how to remove all reversed number pairs from given table, keep only one (random) in output table if something exists. This SQL problem is a often asked in interview questions, so this have been asked by multiple companies for the data analyst, data science and data engineering interviews. And in this input table for this simplicity we can consider there will be one entry for such kind thing like (1,2) and (2,1) can only be exist once there won't be any such entries like one entry for (1,2) and (2,1) repeated twice or thrice. This is our SQL problem in this article we are going to solve the statement.

Using join operation and where clause
At first we need create a database (here in my case database name is example) then create a table (here in my case i have taken table name is details) now insert some dummy data by using below SQL Query

create database example;
use example
create table details(A int, B int)
insert into details values(1,2);
insert into details values(3,2);
insert into details values(2,4);
insert into details values(2,1);
insert into details values(5,6);
insert into details values(4,2);

now check the table data using below query
select*from details

Output

So first try to solve it using the join operation. so here first let me explain how this is going to work so we can do this self join operation with a condition to change those pairs together (1,2) , (2,1) , (2,4) and (4,2).  We can apply where condition where we can eliminate anyone of it either (1, 2) or (2, 1) for the self a join operation. The on condition which is writing. That is little bit tricky, not very complex to understand and how we can actually change these two pairs together (1, 2) to (2,1) So let's say this is my table T1 and again we are using these self join here. So that table we can name it as a T2. So what we actually need to check in the on condition for the table T1 the column of value B should be equals to the column of value A in T2 table and the second condition part will be the column value of A from table T1 should be equal to the column value of B from the T2 table.

So this would be our join condition. So first let me try to write that join condition for you. Then it will be more clear to you. So here what we're trying to do, we will be doing a left join operation while left join again with this table number pairs and we will name it as a T2 and what would be my join condition. The join condition will be value of B column from T1 table should be equals to value of A from the T2 table. And the second part of the condition will be T1 of A Should be equals to T2 of B or directly you can use the below code:

SELECT  *  FROM details t1
LEFT JOIN details t2
  ON t1.B = t2.A
  AND t1.A = t2.B;

Output

See the below snapshot. Blue highlighted data is coming from the table T1 and Red highlighted data is actually coming from the table T2.

So you can see simply how this pair (1,2) got chained with (2,1)  based on just above mentioned code condition since (3,2) there was nothing as such available. That's why I use left join. Because we will be using these null related values in order to filter these pairs as well because we need it in our output. so this one is clear that how it got joined and when we will come to this pair (2,4) this one reversed pair was (4,2) and now (2,1) Obviously this condition will get satisfied because we're doing a self join, so this pair will find this one as a reversed pair and that's why it is being populated here for (5,6) there was number such reverse pair exist, so that's why these values are null and (4,2) we found (2,4) and now we need to print our output. An in our output, if you remember the values were like this among these two pairs (1,2) and (2,1) . We were picking this single value only and how we were actually picking single value, the value from the column A from table T1 should be less than the value of column A from the table T2. This kind of comparison we can put and this way we will be only able to pick this pair and we will be discarding the repeated values and same thing applies for (2,4) and (4,2)  and we need these records as well. So simply we can put a check for this column right where the T2.A is null. In that case we're simply picking single value. No need to check for any quality related thing. So this is our join part. We need to select the columns which we want in our final output so that we are going to select from T1 table itself T1.B once we are getting the join output then we are applying where clause, first where clause will be that if this value of A  column from T2 is null then straight forward we are going to pick these records. There is no further checking required or let's say this is populated. In that case we need to apply the logic for the value of column A from T1 table should be less than value of. A column from the T2 table. That's how we are going to pick any of one among these two like (1,2) to (2,1). Now let me print this one and here, we can see the output which we were looking for or directly you can use the below code:

SELECT
  t1.A,
  t1.B
FROM details t1
LEFT JOIN details t2
  ON t1.B = t2.A
  AND t1.A = t2.B
WHERE t2.A IS NULL
OR t1.A < t2.A;

Output

HostForLIFEASP.NET SQL Server 2021 Hosting