Link to home
Create AccountLog in
Avatar of amillyard
amillyardFlag for United Kingdom of Great Britain and Northern Ireland

asked on

datetime filtering

I am trying to sql select all current records (today and historic --i.e. not tomorrow onwards).

DateTimeCallBack -- DateTime format

Not understanding why this is not working.
SELECT [MasterAccount_ID], [DisplayNameApp1], [DateTimeCallBack], [DIPStatus], [ActiveMemberStatus] 
FROM [MasterAccounts] 
WHERE ([ActiveMemberStatus] = @ActiveMemberStatus) AND ([DIPStatus] = @DIPStatus) AND ([AssignedStaffMember] = @AssignedStaffMember) AND (DateTimeCallBack <= CONVERT(datetime,(CONVERT (VARCHAR(20), GETDATE()+1, 112))))
 
ORDER BY [DateTimeCallBack]

Open in new window

Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi, try this
SELECT [MasterAccount_ID], [DisplayNameApp1], [DateTimeCallBack], [DIPStatus], [ActiveMemberStatus] 
FROM [MasterAccounts] 
WHERE ([ActiveMemberStatus] = @ActiveMemberStatus) AND ([DIPStatus] = @DIPStatus) AND ([AssignedStaffMember] = @AssignedStaffMember) AND (DateTimeCallBack < CONVERT(datetime,(CONVERT (VARCHAR(10), GETDATE()+1, 120))))
ORDER BY [DateTimeCallBack]

Open in new window

style 112 will convert it to yymmdd format, which will not be yy-mm-dd when being converted back to datetime.
Avatar of amillyard

ASKER

ee_rlee:  nothing changes on the list, cannot see any record in list with todays date on it
I am formatting the datetime column as follows:

{0:dd-MMM-yy  HH:mm}
how about this one?
SELECT [MasterAccount_ID], [DisplayNameApp1], [DateTimeCallBack], [DIPStatus], [ActiveMemberStatus] 
FROM [MasterAccounts] 
WHERE ([ActiveMemberStatus] = @ActiveMemberStatus) AND ([DIPStatus] = @DIPStatus) AND ([AssignedStaffMember] = @AssignedStaffMember) 
AND (DateTimeCallBack < CONVERT(datetime,(CONVERT(VARCHAR(10), dateadd(day,1,GETDATE()), 120))))
ORDER BY [DateTimeCallBack]

Open in new window

ee_rlee:  no change in result list
ee_rlee:  if I remove the datetime, filtering -- I can confirm I see the other records, they are definately there in db
What if you try this one?

DATEDIFF (day, DateTimeCallBack , GETDATE())<=0
ee_rlee:  ok -- some progress...

DATEDIFF (day, DateTimeCallBack , GETDATE())<=0

is going the other way -- this is showing todays records and tomorrow onwards
DATEDIFF (day, DateTimeCallBack , GETDATE())>=0
ee_rlee: todays date is missing
I added +1 the getdate -- seems to have brought up todays date now (and historic records)

DATEDIFF (day, DateTimeCallBack , GETDATE()+1)>=0

this datediff is not limited to a few days difference?   works even if a couple of years difference?
yes it would work. can you try to run SELECT GETDATE() to check if you have the correct date?
ee_rlee: ok, interesting question about the correct date.

I am running a datetime webservice as well -- to take into consideration endusers timezone.

the getdate in the select statemtn  you are referring too is what is on the.... sql server?  web server?  or local browser (user machine) ?
where is it getting the datetime from that is?
am I right to assume that the getdate is getting is value from the SQL server itself?   if so, this could be the issue as the sql server is a dffierent machine to www and our datetime webservices hosted service....we might be comparing correctly ...but my datetime is out...

if we are picking from the sql server date/time ...is it possible for the sql statement to access the webservice -- which automatically compensates the date to timezone etc.
getdate gets the system date and time on where sql server is running. I'm not sure if that is possible but one option is to pass the current datetime as a parameter instead of using the getdate function of sql server.
SELECT GETDATE()  -- yes -- if giving me yesterday date (as I am in a different time zone) -- the sql server is a few hours behind !!  -- so I need to take the +1 off then ...
ok -- I have the current date being passed to a text label as follows:

fyi -- when saving to the db and all those transations -- the datetime is correct, i.e. non sql based as comes from the datetime webservice as mention previously....its just the sql select statements that are are incorrect still.

is the label in a suitable format -- or do I need to create another label (make it invisible).
DateTime serverTime = service.GetServerTime();
            Label_Date.Text = serverTime.ToString("ddd, MMM dd, yyyy");

Open in new window

If the difference in time will be constant, then you could use dateadd

i.e.
if 3 hrs behind

dateadd(hour,3,getdate())
would prefer to capture the user adjust date/time -- so its consistent with alll other db records that are saved.  also users can be in differnet timezones, so when they log into the system, their personal date/time is prepared as local.

because of this, it would be easier to support access to this variable in order to continue this type consistancy.

was the label varaible ok to use?  or a better way to use:

DateTime serverTime = service.GetServerTime();
yes, i think that should be fine
how do I add the :    Label_Date.Text    into the sql statement -- to replace the GetDate()
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
am getting the following error now as follows:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Conversion failed when converting the nvarchar value 'Mon, Mar 10, 2008' to data type int.
mmm.... very interesting...
I went back into the control -- used the advanced settings and changed its type to datetime (although being received as an nvarchar from the label string (text conversion).

seems to be working fine now !!!
yes, still working .... checked the server time --- this is still @ 9th mar, whereas I am now seeing 10th mar on my listing (which means it is now sync up with the user ok)...

wow - finally got there... I saw a couple of strange results, i.e. date being out of sync on a couple of other lists -- now I know why (just depends how late I'm working to notice -- i.e. change of day event)

REALLY appreiated your time and efforts :-))  Thank You.
You're welcome

Glad I could help ;)