Searching SMALLDATETIME fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Searching SMALLDATETIME fields

Author

Topic

kdefilip
Starting Member

2 Posts

Posted - 2015-04-11 : 14:39:08

Hi
Trying to search a table which has a datatype of "SMALLDATETIME". Data in the field, of course, is in following format:
2005-06-07 00:00:00 or YYYY-MM-DD HH:MM:SS

I only need to search the date part with no consideration for the time part.

I am getting very inconsistent and inaccurate results. Mostly, the results returned are outside the date span. I have also tried altering the BETWEEN format, i.e., replacing "/" with "-", full year instead of 2-digit year, etc

EDIT: I have also tried the CONVERT statement in the SELECT clause OR in the WHERE clause AND have tried the CONVERT in both the SELECT and WHERE clause with no difference in result set.
I have tried the following sql syntax without consistent or accurate results:


SELECT CONVERT(VARCHAR(11),DateField,101),c_number, cc_summary.prdkey.
?FROM cc_summary INNER JOIN P_HEADER ON CC_Summary.PrdKey =P_HEADER.PrdKey
WHERE CC_Summary.DateField BETWEEN '01/01/15' AND '03/30/15'
AND P_HEADER.Status <> '0'
AND cc_summary.ID = '033'?

Any help would be greatly appreciated.

SQL Server version 2008 SP1
Windows server 2012

Kristen
Test

22859 Posts

Posted - 2015-04-12 : 07:38:22

DOn't use string dates in the format '01/01/15' - they are ambiguous and depends on all sorts of facotrs (bother Server Settings and also the Language setting for the currently connected user). Even if they work today something may change and stop them working tomorrow.

Use only:

'yyyymmdd'

or the ISO format:

'yyyy-mm-ddThh:mm:ss.sss'

Note that BETWEEN will select a finish value that exactly matches '03/30/15' (ie the TIME part is MIDNIGHT)

What you might be expecting is this instead:


WHERE CC_Summary.DateField >= '20150101' AND CC_Summary.DateField < '20150331'

i.e. your query would say "AND Date is LESS than day-after-end-date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources