getdate in between condition - MS SQL Server


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
Thean SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thean SAuthor 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.


Subject: help required on MS SQL query
HuaMin ChenProblem resolverCommented:
select * from tab0 where CONVERT(varchar,datecolumn,112)=CONVERT(varchar,getdate(),112)

Open in new window

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Thean SAuthor Commented:
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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 resolverCommented:
Try the way below to trucate the date by removing the time:

Open in new window

Thean SAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.