Angular 12 CRUD Example
In this article, we will learn angular 12 CRUD example with web API as well as way to implement cascading dropdown, searching, sorting, and pagination...
May 30, 2021 read moreIn this article, we will learn angular 12 CRUD example with web API as well as way to implement cascading dropdown, searching, sorting, and pagination...
May 30, 2021 read moreIn this article i am going to explain how you can use angular js table with bootstrap 4 in asp.net web form, and also show you how you can display records...
December 08, 2018 read moreCodingvila also allowing a guest post for digital marketing, where you can explore your business, product, or services in terms of articles. write quality.......
August, 21, 2021 read moreIn this article, we will learn how to create a bar chart in angular 12 using ng2-charts. Here, I'll explain how to create an angular 12 project in visual........
May 29, 2021 read moreThis article gives an explanation about convert Datatable to CSV in c# and explains the efficient way to write CSV files from Datatable as well as show...
March 01, 2020 read moreThis article provides an explanation about how to merge multiple pdf files into single pdf in using Itextsharp in c# here I also explained the use of Itextsharp.
January 22, 2019 read moreDECLARE @Employee AS TABLE ( EmpId INT, FirstName VARCHAR(50), LastName VARCHAR(50) , Department VARCHAR(50), Designation VARCHAR(50), Country VARCHAR(50), DateOfBirth DATETIME )Now, we will insert some dummy records on the Employee table, for demonstration.
INSERT INTO @Employee VALUES (1, 'Nikunj', 'Satasiya', 'IT', 'Software Engineer', 'India', '1996-04-08 00:00:00:00 '), (2, 'Hiren', 'Dobariya', 'IT', 'Web Devloper', 'India', '1996-12-31 00:00:00:00'), (3, 'Krishna', 'Patel', 'IT', 'Web Devloper', 'UK', '1995-10-05 00:00:00:00'), (4, 'Vivek', 'Ghadiya', 'IT', 'Software Engineer', 'India', '1993-12-18 00:00:00:00'), (5, 'Pratik', 'Pansuriya','IT', 'Software Engineer', 'India', '1997-01-16 00:00:00:00'), (6, 'Sneha', 'Patel', 'IT', 'Web Devloper', 'India', '1996-08-07 00:00:00:00')If you read the given requirement then we need to fetch all the records of the employee whose birthday coming in the given date range passed as criteria in where clause and date criteria should be optional, it means if the user didn't select any date range then all the appropriate records should be displayed as a result set.
DECLARE @From_Birth_Date VARCHAR(10)='1996-01-01', @To_Birth_Date VARCHAR(10)='1997-01-01'Now, we will write a select statement to archive our requirement.
--Paramiter Declaration-------------------- DECLARE @From_Birth_Date VARCHAR(10)='1996-01-01', @To_Birth_Date VARCHAR(10)='1997-01-01' SELECT * FROM @Employee Emp WHERE -------------1st Date Criteria For FromDate----------------- ( CAST(Emp.DateOfBirth AS DATE) >= CAST(@From_Birth_Date AS DATE) OR 1 = ( CASE WHEN LTRIM(RTRIM(@From_Birth_Date)) = '' THEN 1 ELSE 0 END ) ) -------------2nd Date Criteria For ToDate----------------- AND ( CAST(Emp.DateOfBirth AS DATE) <= CAST(@To_Birth_Date AS DATE) OR 1 = ( CASE WHEN LTRIM(RTRIM(@To_Birth_Date)) = '' THEN 1 ELSE 0 END ) )If you analyzed above select statement then we have passed the date range in where clause as date criteria. we have cast/convert our column DateOfBirth in "yyyy-MM-dd" formate.
SELECT CONVERT(VARCHAR(10),GETDATE(),103) As [Date]
SELECT CAST(GETDATE() AS DATE) As [Date]We have used date condition something like shown below, where CAST(Emp.DateOfBirth AS DATE) >= CAST(@From_Birth_Date AS DATE) return all the records from employee table whose date of birth is equal to or greater than equal to From_Birth_Date. Now suppose user didn't pass the From_Birth_Date as a date criteria then we also used another condition with "OR" operator with this condition where we checked if From_Birth_Date is blank then our condition should be 1=1 else 1=0.
( CAST(Emp.DateOfBirth AS DATE) >= CAST(@From_Birth_Date AS DATE) OR 1 = ( CASE WHEN LTRIM(RTRIM(@From_Birth_Date)) = '' THEN 1 ELSE 0 END ) )Now, we have passed our second date criteria for To_Birth_Date same as above just our conditions will change, here we will use something like CAST(Emp.DateOfBirth AS DATE) <= CAST(@To_Birth_Date AS DATE) and this condition will return all the records from employee table whose date of birth is less than or equal to To_Birth_Date. and also used "OR" operator with this condition where we checked if To_Birth_Date is blank then our condition should be 1=1 else 1=0.
AND ( CAST(Emp.DateOfBirth AS DATE) <= CAST(@To_Birth_Date AS DATE) OR 1 = ( CASE WHEN LTRIM(RTRIM(@To_Birth_Date)) = '' THEN 1 ELSE 0 END ) )
![]() |
SQL Server Date Range Condition in WHERE Clause with Date Criteria |
![]() |
SQL Server Date Range Condition in WHERE Clause Without Date Criteria |
Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.
Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.
Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.
Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.
If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com
Tutup Konverter!sentiment_satisfied Emoticon