DATETRUNC New T-SQL Enhancements in SQL Server

 In this article, we'll explore how the new DATETRUNC function works, look at some practical use cases, and examine how it differs from traditional data manipulation methods. This article is written for SQL developers, DBAs, and data engineers who are looking to understand how to leverage this new functionality in SQL Server 2022.

Understanding DATETRUNC

The DATETRUNC function allows you to truncate a datetime or datetime2 value to a specified date part. This means you can strip the time portion or reduce the precision of a date down to a specific unit such as a year, quarter, month, day, hour, minute, or second. The DATETRUNC function is a more streamlined and expressive way to handle date truncation compared to the traditional methods involving DATEADD, DATEDIFF, and CONVERT.

Syntax

DATETRUNC ( datepart, expression )

--datepart: The part of the date to truncate. This can be year, quarter, month, day, hour, minute, second, etc.
--expression: The datetime, smalldatetime, date, or datetime2 expression to truncate.

SQL

Examples

Here’s an example of truncating a DateTime value to different date parts using DATETRUNC.

USE [AdventureWorks2022]
GO

DECLARE @date datetime2 = '2024-10-08 13:45:30.123'

-- Truncate to year
SELECT DATETRUNC(year, @date) AS TruncatedToYear

-- Truncate to month
SELECT DATETRUNC(month, @date) AS TruncatedToMonth

-- Truncate to day
SELECT DATETRUNC(day, @date) AS TruncatedToDay

-- Truncate to hour
SELECT DATETRUNC(hour, @date) AS TruncatedToHour

-- Truncate to minute
SELECT DATETRUNC(minute, @date) AS TruncatedToMinute

-- Truncate to second
SELECT DATETRUNC(second, @date) AS TruncatedToSecond

SQL

Output

Use Cases

DATETRUNC offers several advantages for common date truncation use cases in SQL Server, making it easier to,

• Group by a Specific Date Part: You can use DATETRUNC to group data by a specific date part (e.g., by month, day, or hour).
• Remove Time from Datetime Values: Often, developers need to work with only the date part, ignoring the time part.
• Aggregate Data at Different Levels: DATETRUNC makes it easy to aggregate data at different levels of granularity such as hourly, daily, weekly, or monthly.
• Date Range Filtering: DATETRUNC helps simplify date-based filtering where you need to match specific parts of a date.

Comparing DATETRUNC with Traditional Methods

Before the introduction of DATETRUNC, SQL Server developers typically used a combination of DATEADD, DATEDIFF, and CONVERT to achieve similar results. Let’s compare DATETRUNC with traditional approaches.

Truncating to Day

The DATETRUNC approach is much simpler and more intuitive compared to the CAST and CONVERT methods. Here’s how you might truncate a datetime value to the day using traditional methods:

USE [AdventureWorks2022]
GO

DECLARE @date datetime2 = '2024-10-08 13:45:30.123';

-- Traditional method
SELECT CAST(CONVERT(date, @date) AS datetime) AS TruncatedToDay;

-- Using DATETRUNC
SELECT DATETRUNC(day, @date) AS TruncatedToDay;

SQL

Output

Truncating to Month and Year

USE [AdventureWorks2022]
GO

DECLARE @date datetime2 = '2024-10-08 13:45:30.123';

-- Truncating to Month
-- Traditional method
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0) AS TruncatedToMonth;

-- Using DATETRUNC
SELECT DATETRUNC(month, @date) AS TruncatedToMonth;

-- Truncating to Year
-- Traditional method
SELECT DATEADD(year, DATEDIFF(year, 0, @date), 0) AS TruncatedToYear;

-- Using DATETRUNC
SELECT DATETRUNC(year, @date) AS TruncatedToYear;

SQL

Conclusion

By replacing more complex combinations of DATEADD and DATEDIFF with a single, readable function, DATETRUNC helps reduce query complexity, improve maintainability, and streamline date-based calculations.

As a SQL professional, mastering DATETRUNC will enhance your ability to write clean and efficient T-SQL code, making it easier to work with DateTime data in SQL Server 2022. Whether you're aggregating sales by month or grouping website traffic by hour, DATETRUNC can be a valuable tool in your SQL library.

 

Union Budget 2024-25 – All You Need to Know

The Union Budget is a yearly financial plan presented by the Finance Minister of India for the development of the country. For the year 2024-25, Finance Minister Nirmala Sitharaman announced important changes to simplify the tax system and boost economic growth. With a focus on job creation and youth skilling, ‘EMPLOYMENT’ was the theme of Union Budget 2024-25.

Here, each letter represents a key focus area: Employment, MSMEs, Productivity, Land, Opportunity, Youth, Middle Class, Energy Security, New Generation Reforms, and Technology. These ten priorities outline the government’s strategy for economic growth and development.

Budget 2024 Key Highlights

Below are the major announcements from the Union Budget 2024:

• Income Tax Changes: Introduction of new income tax slabs and an increase in the standard deduction.
• Social Security: Enhancements to the National Pension Scheme (NPS) deductions.
• Sectoral Focus: Emphasis on agriculture, MSMEs, and employment generation.

Taxation Changes

The budget introduces several changes to the income tax regime. So, here are the revised tax slabs under the New Tax Regime:

New Tax Slabs

• Up to ₹3 lakh: 0%
• ₹3 lakh to ₹7 lakh: 5%
• ₹7 lakh to ₹10 lakh: 10%
• ₹10 lakh to ₹12 lakh: 15%
• ₹12 lakh to ₹15 lakh: 20%
• Above ₹15 lakh: 30%

With this new regime, salaried employees can save up to ₹17,500/- in income tax.

• Income Tax Standard Deduction: Increased from ₹50,000 to ₹75,000 to provide more relief to salaried employees.
• Long Term Capital Gains Tax: LTCG gains tax hiked from 10% to 12.5%.

Social Security and Employment Benefits

The budget also introduces several measures to enhance social security and employment:

• Increased NPS Deductions: Employers’ contributions to NPS have been increased from 10% to 14% of the employee’s salary.
• One-Month Wage for New Entrants: The government will provide a one-month wage to all new employees in the formal sector in 3 instalments up to ₹15,000. This would directly be transferred as part of their provident fund contribution.
• Family Pension Deduction: The deduction on family pensions has been increased from ₹15,000 to ₹25,000, thereby, providing better financial stability to pensioners.

Impact On Major Sectors and Funds Allocation

Different sectors are set to benefit from various budget allocations:

• Agriculture and Allied Sectors: ₹1.52 lakh crores have been allocated to enhance strength and productivity in the agricultural sector.
• Education: For education and employment, a total of ₹1.48 lakh crores has been allocated.
• Healthcare: Funds of approximately ₹90,958 crores are allocated for building healthcare facilities to improve public services.
• Infrastructure: To maintain and increase infrastructural development, ₹11.11 lakh crores have been allocated in the Union Budget 2024.
• Urban Development: For upgrading urban infrastructure, ₹82,575 crores have been allocated by the government.
• Technology and Innovation: A total of ₹21,936 crores has been allocated to the Ministry of Electronics and Information Technology to support startups and technology companies.

Revenue and Expenditure

The budget outlines the government’s revenue and expenditure plans:

• Revenue Forgone: Approximately ₹37,000 crore in revenue will be forgone due to the new tax changes, with an additional ₹30,000 crore to be mobilised.
• Expenditure Plans: The government is set to spend ₹48,20,512 crore in 2024-25, which is 8.5% more than the actual expenditure in 2023-24. The major expenditure areas include agriculture, infrastructure, social welfare programs, and employment schemes.

Economic Projections and Targets

• GDP Growth: The budget highlights the expectation for continued strong economic performance.
• Fiscal Deficit: A target set at 4.9% for the fiscal deficit with plans to reduce it to 4.5%.

MSMEs and Employment Generation

The budget also looks forward to supporting Micro, Small, and Medium Enterprises (MSMEs) and generating employment. As a result, it doubles the Mudra loan limits to ₹20 lakh and implements a digital credit assessment model to nurture growth in the MSME sector.

Impact On Gold Price

As per the Budget 2024, the import duties on gold have been reduced from 15% to 6%. This custom duties cut will reduce the taxes on gold from 18.5% to 9%, eventually, reducing the price of gold.

Conclusion

So, this sums up the basics of the Budget 2024 key updates.

Now, if you are looking for instant personal loan to manage your expenses, the  app is a great choice. It offers quick and easy personal loans with minimal documentation and is perfect for emergencies, home renovations, or other personal expenses.

Moreover, with the reduction in import duties on gold, you can consider investing in digital gold with and have a hedge against inflation. Download the app now!