kvnsdr
asked on
DateDiff Minute GetDate Between C# vs SQL
I've been using the following C#/SQL combo to get a solution. It works as-is but I would like to have a pure-SQL solution if possible.
[C#/SQL Combo]
string strCurrentTimeOnly = DateTime.Now.ToShortTimeSt
string strDayofWeek = DateTime.Now.DayOfWeek.ToS
string sql = " Select * from tbl_Users where col3 = 'True' " +
" and DateDiff(n, col_TimeOnly, '" + strCurrentTimeOnly + "') between 1 and 1 " +
" Or col_DayOfWeek = '" + strDayOfWeek + "')";
[SQL using GetDate()]
" Select * from tbl_Users where col3 = 'True' " +
" and DateDiff(minute, col_TimeOnly, GetDate()) between 1 and 1 " +
" Or col_DayOfWeek = '" + strDayOfWeek + "')";
The SQL above returns nothing... I think the problem is GetDate() represents datetime and not just time only ....
Help...
[C#/SQL Combo]
string strCurrentTimeOnly = DateTime.Now.ToShortTimeSt
string strDayofWeek = DateTime.Now.DayOfWeek.ToS
string sql = " Select * from tbl_Users where col3 = 'True' " +
" and DateDiff(n, col_TimeOnly, '" + strCurrentTimeOnly + "') between 1 and 1 " +
" Or col_DayOfWeek = '" + strDayOfWeek + "')";
[SQL using GetDate()]
" Select * from tbl_Users where col3 = 'True' " +
" and DateDiff(minute, col_TimeOnly, GetDate()) between 1 and 1 " +
" Or col_DayOfWeek = '" + strDayOfWeek + "')";
The SQL above returns nothing... I think the problem is GetDate() represents datetime and not just time only ....
Help...
chapmandew
the datediff function returns the number of minutes between two dates...in your last query, you only want those records where the difference is exactly one minute...is there a particular reason for that?
ASKER
Its a polling code that looks for a one minute time diff to trigger another process...
OK...so, your col_TimeOnly field only lists time, and doesn't contain a date, correct?
ASKER
col_TimeOnly = 9:00 PM
col_DayOfWeek = Friday
col_DayOfWeek = Friday
But you don't store the date anywhere?
ASKER
No, Time or DayOfWeek is all that matters.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, made a change... The col_TimeOnly is now type datetime, so the format is:
2008-05-23 18:45:00 or 5/23/2008 4:54:00 PM (same thing)
2008-05-23 18:45:00 or 5/23/2008 4:54:00 PM (same thing)
ASKER
I'm also in the process of adding MySQL database support to our otherwise MSSQL dependant WinApp.
I found the task easier in MySQL:
Select * from tbl_Users where col3 = '1'
and TimeStampDiff(Minute, col_TimeOnly, Now()) Between 1 and 1
I found the task easier in MySQL:
Select * from tbl_Users where col3 = '1'
and TimeStampDiff(Minute, col_TimeOnly, Now()) Between 1 and 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, its working now with a small change: (mm vs. minute)
Select *
from tbl_Users
where col3 = '1'
and dateDiff(minute, col_TimeOnly, getdate()) Between 1 and 1
Select *
from tbl_Users
where col3 = '1'
and dateDiff(minute, col_TimeOnly, getdate()) Between 1 and 1
yeah, sorry about that... small slip and doing months, not minutes....
ASKER
Opps, clicked the incorrect link to award points...
Now, going to community support...
Now, going to community support...