Solved

datetime filtering

Posted on 2008-06-24
6
260 Views
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

0
Comment
Question by:amillyard
  • 3
  • 3
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
is the data type DATETIME in that table?
a=yes:
 DateTimeCallBack < getdate() ORDER BY DateTimeCallBack ASC



0
 

Author Comment

by:amillyard
Comment Utility
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


0
 

Author Comment

by:amillyard
Comment Utility
do I not need the :

serverTime.AddDays(1).ToString("ddMMyyyy HH:mm:ss")
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
getdate() is the time on the server.
0
 

Author Comment

by:amillyard
Comment Utility
getdate()   -- this will filter the current date & time, does it filter 'seconds' as well ?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
yes.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now