Date Update

Ramco Ramco

3y871SQL Server

I have 2 fields . Date & Datetime field. For e.g

10/03/2023 10/03/2023 06:15:00

11/04/2023 11/04/2023 07:15:00

I want to get those records which don't have same date.

12/04/2023 14/04/2023 08:15:00

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul3y ago
Accepted answer
Database approach :
To get the records which don't have the same date for the date and datetime field, you can use the `DATE` function to extract the date part of the datetime field, and then compare it with the date field.

See the Query :
```
SELECT *
FROM YourTable
WHERE DATE(DateTimeField) != DateField
```
In this query, `YourTable` is the name of your table, `DateTimeField` is the name of your datetime field, and `DateField` is the name of your date field. The `DATE(DateTimeField)` function extracts the date part from the datetime field, and then we compare it with the date field using the `!=` operator. This will return all the records where the date part of the datetime field is not the same as the date field. The query would return the record with the datetime value of "14/04/2023 08:15:00", since it doesn't have the same date as the date value of "12/04/2023".

Tuhin Paul3y ago

CSharp approach :

 
using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    public static void Main()
    {
        List dates = new List()
        {
            new DateTime(2023, 3, 10),
            new DateTime(2023, 3, 10, 6, 15, 0),
            new DateTime(2023, 4, 11),
            new DateTime(2023, 4, 11, 7, 15, 0),
            new DateTime(2023, 4, 12),
            new DateTime(2023, 4, 14, 8, 15, 0)
        };

        var result = dates.GroupBy(x => x.Date)
                          .Where(g => g.Count() > 1)
                          .SelectMany(g => g);

        foreach (var date in result)
        {
            Console.WriteLine(date.ToString());
        }
    }
}

This code creates a list of `DateTime` objects and uses LINQ to group the dates by their `Date` component (i.e. without the time) and select only those groups with more than one date. The `SelectMany` method is then used to flatten the resulting list of groups back into a single list of `DateTime` objects. The code loops over the resulting list and prints out each date that doesn't have a match in the list with a different time.