Solved

get date in sql

Posted on 2014-04-01
5
407 Views
Last Modified: 2014-04-01
Hi all,
I am looking to get the date part only of a field in sql without the time, for example I have a field named DateSent and it actually stores date and time, now I want to see all records who DateSent was today or in the last two days, how do I accomplish that?
0
Comment
Question by:bfuchs
  • 3
  • 2
5 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39971142
select *
from MyTable T
where T.DateSent >= convert(datetime, convert(varchar(11), dateadd(day, -2, getdate()), 113), 113)
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39971157
Hi,
this is for the last two days, how do I get for today only?
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 350 total points
ID: 39971163
Just change the number of days you subtract...

declare @NumDays int

set @NumDays = 2

-- Shows 2 full days + today
select *
from MyTable T
where T.DateSent >= convert(datetime, convert(varchar(11), dateadd(day, -1*@NumDays, getdate()), 113), 113)

set @NumDays = 0

-- Shows today
select *
from MyTable T
where T.DateSent >= convert(datetime, convert(varchar(11), dateadd(day, -1*@NumDays, getdate()), 113), 113)

Open in new window

0
 
LVL 4

Author Comment

by:bfuchs
ID: 39971183
well, I am posting here the sql I have in access and the equivalent sql you're suggesting in sql, and I get completely different results.

Access
SELECT EmployeesEmailMessages.EmployeeID, Count(EmployeesEmailMessages.ID) AS CountOfID
FROM EmployeesEmailMessages INNER JOIN EmailMessagesTbl ON EmployeesEmailMessages.EmailMessagesID = EmailMessagesTbl.ID
WHERE (((EmailMessagesTbl.DateCreated)>=Date()))
GROUP BY EmployeesEmailMessages.EmployeeID
HAVING (((Count(EmployeesEmailMessages.ID))>1));

Open in new window


sql
SELECT     dbo.EmployeesEmailMessages.EmployeeID
                            FROM          dbo.EmployeesEmailMessages INNER JOIN
                                                   dbo.EmailMessagesTbl ON dbo.EmployeesEmailMessages.EmailMessagesID = dbo.EmailMessagesTbl.ID
                            WHERE      (dbo.EmailMessagesTbl.DateCreated >= convert(datetime, convert(varchar(11), dateadd(day, -1, getdate()), 113), 113) 
)
                            GROUP BY dbo.EmployeesEmailMessages.EmployeeID
                            HAVING      (COUNT(dbo.EmployeesEmailMessages.ID) > 1)

Open in new window

0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 39971191
actually, I was comparing to the wrong option, indeed it works with 0, quick job, thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
My Query is not giving correct result. Please help 5 39
Access 2010 Query Syntax 5 30
convert in derived column 7 27
Find results from sql within a time span 11 29
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

13 Experts available now in Live!

Get 1:1 Help Now