CONVERT(varchar(10), GETDATE(), 101)  question

Posted on 2009-02-08
Last Modified: 2012-05-06
WHERE     (DateTime >= CONVERT(varchar(10), GETDATE() - 1, 101)) AND  (DateTime < CONVERT(varchar(10), GETDATE(), 101)


This gives me all records of the day before .. now i want all the records of the day before .. but only between a certain time. Notation: 2/7/2009 8:00:00 PM

Thanks in advanced
Question by:Amien90
    LVL 25

    Expert Comment

    Something like this perhaps -

    You could adapt it to suit your filter requirements..
    DECLARE @starttime varchar(12), @endtime varchar(12)
    SELECT   @starttime = '08:00:000 PM'
    	,@endtime   = '09:00:000 PM'
    SELECT   COMPARISONFROM = DATEADD(d, 0, datediff(d, 0,getdate() - 1)) + cast(@starttime as datetime)
    	,COMPARISONTO   = DATEADD(d, 0, datediff(d, 0,getdate() - 1)) + cast(@endtime as datetime)

    Open in new window


    Author Comment

    not something like:

    DateTime >= CONVERT(varchar(10), GETDATE() - 1, 101) + ' 9:00:0000 PM'
    LVL 25

    Accepted Solution

    Then you need to cast the entire result into a datetime like -

    DateTime >= CONVERT(datetime, CONVERT(varchar(10), GETDATE() - 1, 101) + ' 9:00:000 PM')


    Author Comment

    CONVERT(varchar(10), GETDATE() - 1, 101) + ' 7:00:000 AM')  .. Works

    why 2x Convert?

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now