SQLTeam.com | Weblogs | Forums

Select where datetime: 2016-02-10 05:19:43.000

sql2012

#1

Dear team

Hope you can help me on that am new on SQL

I must do a select on a table where column datetime must = to - 1day
my column datetime is on that format 2016-02-10 05:19:43.000

Any idea how to do this

I done the below and i have no data

DECLARE @SEL_DATE_FROM DateTime
DECLARE @SEL_DATE_TO DateTime

select @SEL_DATE_FROM = CONVERT(Datetime, Str(Datepart(year, Getdate() - 1)) + '-' + Str(Datepart(month, Getdate() - 1)) + '-01')
select @SEL_DATE_TO = DATEADD(month, 12, @SEL_DATE_FROM)

Select AccountID,
SUBJECT,URGENCYCODE,ACCOUNTID,
RECEIVEDDATE

from table
where RECEIVEDDATE = -1

Thanks
Regards


#2

to take a date and subtract one day use DATEADD

SELECT DATEADD(day, -1, GETDATE())

If you need the first day of the month that can be done

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

#3

Dear Djj55

Thanks i test it with the below and did not get any return

Many thanks for your help

Attach an sceen shot of my table you will understand

Select AccountID,URGENCYCODE,ACCOUNTID,RECEIVEDDATE

from sysdba.Ticket

where RECEIVEDDATE =DATEADD(day, -1, GETDATE())


#4

What is happening is you are trying to use a date / time to get a date. What I think you want is

WHERE ReceivedDate >= CAST(DATEADD(day,-1, GETDATE()) AS DATE) AND ReceivedDate < GETDATE()

Please note that GETDATE() returns today's date. If you have a date to compare to you must use that. If you are looking for all the data for February 10, 2016:

WHERE ReceivedDate >= '20160210' AND ReceivedDate < '20160211'

#5

Dear Djj,

Am near the result what i want .

Now its give me data for today and yesterday .

I want only data for yesterday Day -1

Hope you can help me on that
Many thanks for your help


#6

Do you understand what the code is doing? If not let me know and I will try and explain.

I believe you need to change GETDATE() to a datatype DATE.

WHERE ReceivedDate >= CAST(DATEADD(day,-1, GETDATE()) AS DATE) AND ReceivedDate < CAST(GETDATE() AS DATE)

Here are two functions to consider:

SELECT
     DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)        AS [Beginning of today]
    , DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)  AS [Beginning of previous day]

#7

Dear Djj,

Many thanks its work fine with the code you send me .

Thanks a lot for your help

Regards