SQL Server 2005, storing only date and checking the condition with date only

Question

I am using SQL Server, and wants to store only the date part, if it stores the time also no issue, but while checking the condition, I want to consider only date. for ex in .NET.

SELECT * 
  FROM checkins 
 WHERE checkindate='" + DateTime.Now.toShortDateString() + "'";

score 1 · Accepted Answer · answered Apr 30 '11 at 06:15

1

Use the CONVERT function to only get the date portion of the DateTime column:

SELECT * 
FROM checkins 
WHERE CONVERT(VARCHAR(10),checkindate, 106) ='" + 
                                     DateTime.Now.ToShortDateString() + "'";

Though, to make the date unambiguous I would not use ToShortDateString, but a custom format string:

DateTime.Now.ToString("d MMM yyyy")

So all together:

SELECT * 
FROM checkins 
WHERE CONVERT(VARCHAR(10),checkindate, 106) ='" + 
                                     DateTime.Now.ToString("d MMM yyyy") + "'";

answered Apr 30 '11 at 06:15

Oded

489,969
99
883
1,009

@Agay - Yes, but to ensure that a date such as `2/1/2011` can't be interpreted as either January 2nd **or** February 1st, it is better to use a named month format (2 Jan 2011). – Oded Apr 30 '11 at 06:27
@Oded, but then the application will break if the NLS settings change. – Apr 30 '11 at 06:45
@a_horse_with_no_name - I am simply suggesting a format that will not be ambiguous. What would be a better format? – Oded Apr 30 '11 at 06:50
@Oded: I'm a big fan of the ISO standard: YYYY-MM-DD – Apr 30 '11 at 07:05
@a_horse_with_no_name: That ISO standard doesn't work for SQL Server until the new '2008 types. Read the comments here http://stackoverflow.com/questions/1138142/best-way-to-convert-and-validate-a-date-string – gbn Apr 30 '11 at 08:58
@gbn: I didn't know that, thanks for the info. I assumed that all DBMS (at least all "big names") support the ANSI format nowadays. – Apr 30 '11 at 14:20

score 0 · Answer 2 · answered Apr 30 '11 at 06:30

0

SELECT * 
  FROM checkins 
 WHERE DATEADD(DAY, DATEDIFF(DAY, 0, checkindate), 0)
    = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

answered Apr 30 '11 at 06:30

Paul Keister

12,851
5
46
75

score 0 · Answer 3 · answered Apr 30 '11 at 06:31

SELECT dateadd(dd, datediff(dd,0,getdate()), 0)

will get you the date only while retaining it as a datetime type. Comparing datetime types tends to be more efficient than comparing strings.

That approach has the flexibility to ignore the day, month, get to the previous month, year, etc. See this article for more info.. http://www.sqlservercentral.com/articles/Date+Manipulation/69694/

SQL Server 2005, storing only date and checking the condition with date only

3 Answers3