November 19, 2015 at 2:18 pm
Hi,
I need help please,
This statement work but the system only see the hour not the minute:
(create_dtim between #1/1/2015# and #3/1/2017#) and (hour(create_dtim) >= 22)+ (minute(create_dtim) >= 15)
Thks a lot for the sugestions to fix that.
Regards,
Cachado
November 19, 2015 at 2:27 pm
jcachado (11/19/2015)
Hi,I need help please,
This statement work but the system only see the hour not the minute:
(create_dtim between #1/1/2015# and #3/1/2017#) and (hour(create_dtim) >= 22)+ (minute(create_dtim) >= 15)
Thks a lot for the sugestions to fix that.
Regards,
Cachado
Lost, what exactly are you doing? There is absolutely no context for this question to even try to answer it.
November 19, 2015 at 2:32 pm
Sorry my poor english.
I want records after the time 22:15 but with this statement appear after 22 hour! 🙁
Thks,
Cachado
November 19, 2015 at 2:33 pm
Now, assuming that you are looking for all data where the create_dtim is greater than or equal to 2015-01-01 and less than 2017-03-01 where the TIME portion for each day is greater than or equal to 22:15 and assuming you are using SQL Server 2012, try the following:
select * from dbo.MyTable
where
create_dtim >= '20150101' and create_dtim < '20170301' and
cast(create_dtim as TIME) >= '22:15';
November 19, 2015 at 2:39 pm
🙁
Dont work
I use Visual studio 2012 and the table is from database access.
Thks for your help,
Cachado
November 19, 2015 at 2:39 pm
The pound signs around the dates make me think Access is being used. Just a hunch...
November 19, 2015 at 2:53 pm
jcachado (11/19/2015)
🙁Dont work
I use Visual studio 2012 and the table is from database access.
Thks for your help,
Cachado
Well, I suspected but hoped I was wrong. You did post your question in a SQL Server 2012 forum, so you should expect a SQL Server 2012 answer in return.
I haven't touched an Access data in well over 10 years so I am unable to help here.
November 19, 2015 at 2:57 pm
This work but i need the minutes to 🙁
(create_dtim between #1/1/2015# and #1/1/2017#) and (hour(create_dtim) >= 19)
Any ideas please,
Regards,
Cachado
November 19, 2015 at 3:10 pm
jcachado (11/19/2015)
This work but i need the minutes to 🙁(create_dtim between #1/1/2015# and #1/1/2017#) and (hour(create_dtim) >= 19)
Any ideas please,
Regards,
Cachado
Only thing I would suggest is this: and minute(create_dtim) >= 15
November 19, 2015 at 3:13 pm
There are a couple ways to do this.
Let's say you're wanting only rows where the time portion is greater than or equal to 22:15 in the date range in your first post.
You could either then query that you want rows where (hour=22 and minute>=15) OR (hour>22), or you could rely on the fact that you can do math on the datetimes, and that the time is the fractional portion.
Below are those two WHERE clauses:
WHERE create_dtim BETWEEN #1/1/2015# and #3/1/2017# AND ((HOUR(create_dtim)=22 AND MINUTE(create_dtim)>=15) OR HOUR(create_dtim)>22)
OR
WHERE create_dtim BETWEEN #1/1/2015# and #3/1/2017# AND create_dtim-INT(create_dtim)>=#11/13/2015 10:15 PM#-#11/13/2015#
Note you can't just AND the MINUTE criterion with the HOUR criterion, or you'll omit a time like 23:05.
Cheers!
November 19, 2015 at 3:26 pm
Jacob Wilkins (11/19/2015)
There are a couple ways to do this.Let's say you're wanting only rows where the time portion is greater than or equal to 22:15 in the date range in your first post.
You could either then query that you want rows where (hour=22 and minute>=15) OR (hour>22), or you could rely on the fact that you can do math on the datetimes, and that the time is the fractional portion.
Below are those two WHERE clauses:
WHERE create_dtim BETWEEN #1/1/2015# and #3/1/2017# AND ((HOUR(create_dtim)=22 AND MINUTE(create_dtim)>=15) OR HOUR(create_dtim)>22)
OR
WHERE create_dtim BETWEEN #1/1/2015# and #3/1/2017# AND create_dtim-INT(create_dtim)>=#11/13/2015 10:15 PM#-#11/13/2015#
Note you can't just AND the MINUTE criterion with the HOUR criterion, or you'll omit a time like 23:05.
Cheers!
This, yes. Don't know why I did think of it.
WHERE create_dtim BETWEEN #1/1/2015# and #3/1/2017# AND ((HOUR(create_dtim)=22 AND MINUTE(create_dtim>=15) OR HOUR(create_dtim>22)
Not sure of the other as I really don't know Access.
November 19, 2015 at 3:27 pm
Jacob Wilkins (11/19/2015)
There are a couple ways to do this.Let's say you're wanting only rows where the time portion is greater than or equal to 22:15 in the date range in your first post.
You could either then query that you want rows where (hour=22 and minute>=15) OR (hour>22), or you could rely on the fact that you can do math on the datetimes, and that the time is the fractional portion.
Below are those two WHERE clauses:
WHERE create_dtim BETWEEN #1/1/2015# and #3/1/2017# AND ((HOUR(create_dtim)=22 AND MINUTE(create_dtim)>=15) OR HOUR(create_dtim)>22)
OR
WHERE create_dtim BETWEEN #1/1/2015# and #3/1/2017# AND create_dtim-INT(create_dtim)>=#11/13/2015 10:15 PM#-#11/13/2015#
Note you can't just AND the MINUTE criterion with the HOUR criterion, or you'll omit a time like 23:05.
Cheers!
Ohhh its work you are the best my friend:
create_dtim BETWEEN #1/1/2015# and #3/1/2017# AND ((HOUR(create_dtim)=19 AND MINUTE(create_dtim)>=46) OR HOUR(create_dtim)>19)
just one question, is necessary the OR HOUR(create_dtim)>19) ?
Thks, thks thks a lot,
Cachado
November 19, 2015 at 3:34 pm
Yes, it's necessary.
If you just do (HOUR(create_dtim)>=19 AND MINUTE(create_dtim)>=46), then that requires every returned row to have a minute greater than or equal to 46. Because of that, you wouldn't include 20:10, because while the hour is greater than or equal to 19, the minute is less than 46.
So, you have to say that either the hour is 19 and the minute is 46 or greater, or it's a time in any hour past 19.
Hopefully that helps!
November 19, 2015 at 3:48 pm
Thank you for help, you saved my day, better to correct the night 🙂
Hug!
Cachado
November 23, 2015 at 10:18 am
Access ACTUALLY stores dates and times as numbers. Leverage that to get your answer. Go here for some help: https://support.microsoft.com/en-us/kb/210276
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply