datetime filtering

Posted on 2008-06-24
Medium Priority
Last Modified: 2013-12-16
Date and Time is stored in the DB field as follows:

25/06/2008 11:28:49

...the relevant part of the sql statement attached below :

(DateTimeCallBack < '" + serverTime.AddDays(1).ToString("ddMMyyyy HH:mm:ss") + "') ORDER BY DateTimeCallBack ASC

what am I doing wrong please?

... I wanting to filter historic and upto today (date and time) ... so for example it is 11:53 GMT when I wrote this note -- so if I were to search the db -- I would want only to have historic (date) and upto 11:53 GMT todays records to be returned etc.

InitialiseLeadCall("SELECT TOP 1 T1.[MasterAccount_ID], LTRIM(RTRIM(T1.[DisplayNameApp1])), LTRIM(RTRIM(T1.[DisplayNameApp2])), T2.[LeadSourceProviderProductName], LTRIM(RTRIM(T1.[TelWorkApp1])), LTRIM(RTRIM(T1.[TelHomeApp1])), LTRIM(RTRIM(T1.[TelMobileApp1])), LTRIM(RTRIM(T1.[TelWorkApp2])), LTRIM(RTRIM(T1.[TelHomeApp2])), LTRIM(RTRIM(T1.[TelMobileApp2])), T1.[LeadSourceProviderProducts_ID], T1.[ApplicationTypeJointSingle], T1.[AssociatedSpooler_ID] FROM [MasterAccounts] T1 LEFT JOIN [LeadSourceProviderProducts] T2 ON T1.[LeadSourceProviderProducts_ID] = T2.[LeadSourceProviderProducts_ID] WHERE [AssignedStaffMember]=" + (String)Session["fortuneAgent_ID"].ToString() + " AND [DIPStatus]=0 AND [ActiveMemberStatus]=1 AND (DateTimeCallBack < '" + serverTime.AddDays(1).ToString("ddMMyyyy HH:mm:ss") + "') ORDER BY DateTimeCallBack ASC");

Open in new window

Question by:amillyard
  • 3
  • 3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21854290
is the data type DATETIME in that table?
 DateTimeCallBack < getdate() ORDER BY DateTimeCallBack ASC


Author Comment

ID: 21854683
yes.  but I use the following for the current datetime as the sql server is in a different timezone to user and web-site as follows:

DateTime serverTime = DateTime.Now.AddHours((int)Session["fortuneAgentGMT_offset"]);

so, with that in mind do I update your supplied scripting as follows:

DateTimeCallBack < serverTime ORDER BY DateTimeCallBack ASC


Author Comment

ID: 21854804
do I not need the :

serverTime.AddDays(1).ToString("ddMMyyyy HH:mm:ss")
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 21854955
getdate() is the time on the server.

Author Comment

ID: 21912063
getdate()   -- this will filter the current date & time, does it filter 'seconds' as well ?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21913515

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question