[SQL Server 2008] Problem with Between syntax

Transact-SQL
1

[SQL Server 2008] Problem with Between syntax

Hi there, hope in your help.

In SQL server I have the column doTableDate set a Datetime.

I need extract all rows in on date range and I think use to syntax Between And

If try this version of query I have in output 889 rows all with date 2014-01-03... but I have other records with date 2014-01-04 in column doTableData...

SELECT
	*
FROM
	doTable
WHERE
	doTableDate BETWEEN CONVERT (
		datetime,
		'03/01/2014 00:00:00',
		103
	)
AND CONVERT (
	datetime,
	'04/01/2014 00:00:00',
	103
);

If try this version I don't have output no record, why?
The syntax Between And not working in SQL Server?

Can you help me?
Thank you in advance.

SELECT
	*
FROM
	doTable
WHERE
	doTableDate BETWEEN CONVERT (
		datetime,
		'03/01/2014 00:00:00',
		103
	)
AND CONVERT (
	datetime,
	'03/01/2014 00:00:00',
	103
);
2

I don't often use the word "NEVER" but I'll say NEVER use BETWEEN for temporal criteria. And I'd also use date literals that a whole lot less ambiguous in the international community. Like this...

 SELECT *
   FROM dbo.doTable
  WHERE doTableDate >= '2014-03-01'
    AND doTableDate <  '2014-04-01'
;
1 Like
3

Beware that "YYYY-MM-DD" is ambiguous for DATETIME, although it is unambiguous for DATE datatype.

"YYYYMMDD" is unambiguous for both datatypes

(Unless MS have chagned that in SQL2012? ... still on SQL2008 here for another week ... then SQL2014 Yeah!)