getdate in between condition - MS SQL Server

Thean S
Thean S used Ask the Experts™
on
Hi

Can you please tell me how to use getdate:00:00:00 and getdate:11:59:00 in SQL between condition

select count(*) from tablename where createdon between 'getdate:00:00:00' and 'getdate:11:59:00'

thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Slight change in WHERE clause like this will do..
select count(*) from tablename where createdon >= cast(getdate() as date) and createdon < cast(dateadd(dd,1,getdate()) as date)

Open in new window

or else
select count(*) from tablename where createdon between cast(getdate() as date) and dateadd(ms, -2, cast(cast(dateadd(dd,1,getdate()) as date) as datetime))

Open in new window

Author

Commented:
Thanks Raja, I will check query...

I have another issue with count that I have raised in below link. Can you please help me to fix my problem.

Link: https://www.experts-exchange.com/questions/29128960/help-required-on-MS-SQL-query.html?headerLink=workspace_open_questions

Subject: help required on MS SQL query
HuaMin ChenProblem resolver

Commented:
Try
select * from tab0 where CONVERT(varchar,datecolumn,112)=CONVERT(varchar,getdate(),112)

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
--
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> CONVERT(varchar,datecolumn,112)

This one will make the indexes to go for scan instead of seek if any available on that particular column..
hence this will have some performance issues.

>> I have another issue with count that I have raised in below link

Sure, let me check now..
HuaMin ChenProblem resolver

Commented:
Try the way below to trucate the date by removing the time:
select * from tab0 where CONVERT(DATETIME, CONVERT(DATE, datecolumn))=CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

Open in new window

Author

Commented:
Hi Raja and HuaMin Chen,

Thanks for your help on this.

I have raised another question for my issue. Can you please help on this.

https://www.experts-exchange.com/questions/29129054/Problem-with-SQL-command-MS-SQL-Server.html?headerLink=workspace_open_questions

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial