How to get Yesterday Date with mid night time to Yesterday date with 11:59:00

Transact-SQL
1

Hi all,

I am new to SQL programing , I need to pull total yesterday records by giving time stamp from mid night as shown below

I need to give condition as like this

Date BETWEEN '8/05/2015 00:00:00' AND '8/05/2015 11:59:59'

so how can I automate this by getdate()

Can you please help me on this

Thanks

2 
[Date] >= DATEADD(dd,-1,CAST(GETDATE() AS DATE))
AND [Date] < CAST(GETDATE() AS DATE)
3

As you are new to SQL programming I recommend that you don't bother to learn the mnemonic codes for functions like DATEADD (e.g. that "dd" means "Day") and use the full parameter names instead - Day, Month, Year, etc.

Otherwise you will have to learn, or guess??, whether things like "m" are Month, Minute, Millisecond or Microsecond (all of which are valid options and I have no idea what the mnemonics for all of them are, nor why the mnemonics for Weekday is "DW" - much easier to read if you stick to "Weekday")

1 Like
4

i need time stamp like I shown above.

Can I like that please?

5

I am not quite following what you are asking. The query I posted earlier will do exactly what you asked for, namely, include all the values in the [Date] column that start at midnight yesterday, and end before the beginning of today.

Here I am making the assumption that the data type of your [Date] column is DATETIME or one of the other datetime data types. Use the code that I posted and see if you get the results you want. If you are not getting the results you are looking for, we can figure out why.

If you are getting the results you want, and you want more clarifications on why what I posted is the correct way to do it (rather than generating a string such as '8/05/2015 00:00:00'), we can discuss that as well.

6

I think we should have a sweepstake:

  1. [Date] is a Datetime :smile:
  2. [Date] is a Varchar :grimacing:
  3. [Date] is a DATE - not offering ANY odds on this one!
1 Like
7

It worked Thank you for your help.