Skip to main content

Things to remember with Datetime Queries

I wrote some stored procedures to get data for my reports in last week. Most of the queries involve day's summary and cumulative summary till the given date. After running the queries I observed that I am getting wrong results in all the datasets.
After some analysis, I figured out my mistake. I have compared the date given directly with the date field in the table. The problem here is If I give '11/30/2009' as my input date, Database automatically adds the time part to that. So the input becomes '11/30/2009 00:00:00:000'. Whenever I compare the date the query always checks for the exact match. So I need to modify my queries to return correct data.
Here is the modification:
instead of comparing date directly... like 'date = @ReportDate' or 'date < @ReportDate', we should compare with the 'floor' and 'ceil' values for the given date. So the query has modified as 'date between cast(floor(cast(@ReportDate as float))as datetime) and cast(ceiling(cast(@ReportDate as float))as datetime)'. This query works and returns the required datarows. But here one point should be remembered, if we give the parameter with any date picker, It selects only the date and by default it will become 'givenDate 00:00:00:000'. In this case the floor and ceil values for this date are same. So We need to correct our query once again. This modification is very simple and floor value is considered as a benchmark always. After this modification, the query will look like, 'date between cast(floor(cast(@ReportDate as float))as datetime) and dateadd(hh,24,cast(floor(cast(@ReportDate as float))as datetime))'. This last query will always return the correct set of results for a given date.

I think this is known to many of the techies.. But useful to starter bees :)
Labels:

Comments

Post a Comment

Popular posts from this blog

Solution Array for List in WCF

Here is another common problem encounters with WCF Service in initial days. Suppose there is a method which returns a List of Objects the proxy will be generated as method which returns an array of objects. Reason: This problem is due to the default interpretation of collections. While generating proxy for the service all the collections are by default interpreted as arrays. That is why all the lists will be converted to arrays. Solution: If you are generating proxy by adding service reference from visual studio directly, there will be a 'advanced' button. Click that button and select the System.Generic.List as your Collection. This will resolve the problem. If you are generating proxy with svcutil here is the command to do the job. svcutil.exe http://ServerName/ServiceName/Servic.svc?wsdl /collectionType:System.Collections.Generic.List`1 or in short svcutil http://ServerName/ServiceName/Servic.svc?wsdl /ct:System.Collections.Generic.List`1 for more information go through this ...
1 comment
Read more

Linq: Query for multiple join conditions in Linq to SQL

This is common to join a table with another on multiple conditions, Like Select * from Customer c Join Invoice i on c.CustomerID = i.CustomerID and c.CustomerTypeID = i.CustomerTypeID I need to write a Linq to SQL query for the same scenario. But the syntax for this query is a bit different to expect. The Linq query is as follows: From c in DataContext .Customers Join i in DataContext .Invoices on new { c.CustomerID, c.CustomerTypeID } equals new { i.CustomerID, i.CustomerTypeID } *DataContext in the above query should be replaced with actual datacontext name.
3 comments
Read more

Troubleshoot WCF service returning object with null fields

When you are working with WCF service for the first time, generally you will get this error for some reason. Here is the description of the error: WebServiceReference . ServiceClient client = new WebServiceReference . ServiceClient (); WebServiceReference . ImplementationRequest req = new WebServiceReference . ImplementationRequest (); req = client.GetRequest(); Here the Method GetRequest will return an object of ImplementationRequest. Everything will run without any error but the req object will contain all null fields/ majority null fields. Solution: Those nulls are due to error in generating the proxy for the Data Contract defined by the service. Verify that [DataMember] attribute tag is given for all the data fields and [DataContract] for the Class. If those attributes are missing, the object will be generated with null values. If all fields have the tags but the issue is not resolved, then the service reference should be re-added and the proxy should be re-generated. Then t...
1 comment
Read more