Solved

get date in sql

Posted on 2014-04-01
5
405 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 3

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 3

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 3

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

20 Experts available now in Live!

Get 1:1 Help Now