prisen
August 10, 2016, 12:52pm
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
djj55
August 10, 2016, 2:25pm
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)
prisen
August 11, 2016, 5:15am
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())
djj55
August 11, 2016, 10:35am
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'
prisen
August 11, 2016, 10:51am
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
djj55
August 11, 2016, 12:24pm
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]
prisen
August 11, 2016, 12:37pm
7
Dear Djj,
Many thanks its work fine with the code you send me .
Thanks a lot for your help
Regards