Compare Date only in Datetime field in T-SQL

This is simple still I want to share as this is asked by so many people around. So, here is the scenario why it's needed. [Mainly in reporting.]

From the UI, users selects the date and by using that date we need to compare the datetime field in database and produce some result. But, in database it actually the datetime field, we can't apply equal operator on it as it fails to do it because of time part.

I know, now there is a need of time for thinking on how to do it.

Solution:

SELECT * FROM [Employee] WHERE Convert(date,DateSent) = Convert(date,@Date);

OR

SELECT * FROM [Employee] WHERE DATEADD(dd,(DATEDIFF(dd,0,DateSent)),0) = DATEADD(dd,(DATEDIFF(dd,0,@Date)),0);

So, in above query we have taken out the Date part from both DateSent field in database and @Date parameter from the UI  and comparing them. Which produces exact result as we are looking for.

Hope this helps and want to know are there any better solutions for it. Read More...

Group By Date Time without milliseconds in T-SQL

There are lot of requirements which needs some complex logic and put maximum effort to implement them. The below scenario is the one I want to explain in writing T-SQL queries.

The requirement is very simple, I want to apply group by on DateTime field in database which does not includes the milliseconds part from the time. So, on the UI I need to group by and show records with seconds and but not with milliseconds.

In my first try, I just wrote the T-SQL query by directly applying the GROUP BY clause on the DateTime field and found that milliseconds are also including. Which is not matching with what exactly I was looking for. So, tested lot of scenarios and nothing works. I am out of ideas on how to trim the milliseconds and apply group by.

After spent some good time on it, got an idea and not sure this is the best and efficient way of implementing.

SELECT CAST(CAST(CONVERT(Date,Datesent) AS Varchar(20)) + ' ' + CAST(CONVERT(TIME(0),Datesent) AS Varchar(20)) AS Datetime) AS DateSent,

Name, Department,
    FROM [EmployeHistory]
    WHERE EmployeID = @EmployeID
    GROUP BY CAST(CAST(CONVERT(Date,Datesent) AS Varchar(20)) + ' ' + CAST(CONVERT(TIME(0),Datesent) AS Varchar(20)) AS Datetime),
Name, Department
ORDER BY DateSent Desc

So, in the above query, if you observe take the GROUP BY clause out and analyze it.

GROUP BY CAST(CAST(CONVERT(Date,Datesent) AS Varchar(20)) + ' ' + CAST(CONVERT(TIME(0),Datesent) AS Varchar(20)) AS Datetime)

I am doing some way to get  the things work out. :) So, don't be surprise. I am doing nothing here, other than taking the Date part from the date time and taking the Time part from the date time without milliseconds and finally casting it back to date by clubbing those two back...

Do you think, this is the best and efficient way? I am sure this will work without any issues. But, the main thing is even though it is looking like simple it took me time to get the solution for it. Hope this helps and please let me know, if you know any better solution than this. Read More...

Date formatting in Sql server reporting services

When we work with reporting services, we come across with different requirements to display the date in different formats. So, we need to know the formats we can give to get required value. Sql server reporting services supports different formats as T-sql and C# does. So, I just want to give the valid formats I use to display the date in required format.

=Format(Fields!DateCreated.Value, "M/d/yy") - 3/14/1986
=Format(Fields!DateCreated.Value, "d-MMMM-yy") - 14-March-86
=Format(Fields!DateCreated.Value, "MM/dd/yyyy") - 03/14/1986
=Format(Fields!DateCreated.Value, "M/d/yyyy H:mm") - 3/14/1986 22:10
=Format(Fields!DateCreated.Value, "dd-MMM-yyyy") - 14-March-1986
=Format(Fields!DateCreated.Value, "MMM-dd-yyyy") - Mar-14-1986
=Format(Fields!DateCreated.Value,"dd-MMM-yy") - 14-Mar-86

You can try different combinations of above to get desired output. Hope this helps and love to hear the comments.

Read More...

C# display date time with milliseconds

By default, in c# DateTime object will display the values in the 12 hours format with AM and PM. Which don't include the milliseconds. But how to display the date and time with milliseconds? Use the below format to display it.

DateTimeObject.ToString("yyyy-MM-dd HH:mm:ss.fff")

f is for the format for printing the milliseconds. Remember when you try to convert the date time with milliseconds back to C# Date time, then just use Convert.ToDateTime().

Hope this helps.

Read More...