August 14, 2011 at 10:08 am
Hi can someone tell me this doesn;t work?? It just returns a blank every time. The same happens when i use parameters @startDate/@endDate.
select * from Sales.SalesOrderHeader
where OrderDate between 01/01/2004 and 31/12/2004
It's a (really) simple query from the Adventure Works db.
Thanks.
August 14, 2011 at 10:23 am
Dates don't quite work that way. Try this:
select * from Sales.SalesOrderHeader
where OrderDate between '2004-01-01' and '2004-12-31'
In fact, because dates in SQL Server generally have a time component, this may be what you want:
select * from Sales.SalesOrderHeader
where OrderDate between '2004-01-01' and '2004-12-31 23:59:59.997'
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 14, 2011 at 10:43 am
I never use between for dates (inclusive of both ends). I preffer this =>
where dt >= @StartDate and dt < (no equal) @EndDate
That way you don't have to worry about the time part.
August 14, 2011 at 11:17 am
thanks both
this now works fine....
declare @startDate as smalldatetime
declare @endDate as smalldatetime
set @startDate = '2004-01-01'
set @endDate = '2004-12-31'
select * from Sales.SalesOrderHeader
where OrderDate >= @startDate and OrderDate < @endDate
August 15, 2011 at 5:44 am
I tihnk the issue is that you are mixing date formats for dates
I have noticed in my past encounters with between operator
is that if you give date format that is not proper for your "locale"
it does not work some times.
By default, the date format for SQL server is in U.S. date format MM/DD/YYYY, unless a localized version of SQL Server has been installed.
as in your example the end date is in format used outside of US "dd/mm/yyyy" and thus it might have been triggering a silent error during compare operation.
I have found that if I want to use "select [something] from table where a between x and y" sintax with dates it is better to either always provide date in proper format if they are hard-coded
or use CONVERT(datetime,myValue,format) on both side
sheck the format value here "http://msdn.microsoft.com/en-us/library/ms187928.aspx"
:w00t::cool:
August 15, 2011 at 5:58 am
SELECT
[DAY number 1] = 01/01/2004, -- result is integer 0
[DAY number 2] = 31/12/2004, -- result is integer 0
[Date 1] = CAST(01/01/2004 AS DATETIME), -- day 0 is '1900-01-01'
[Date 2] = CAST(0 AS DATETIME)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 15, 2011 at 7:46 am
I didn't noticed before, but in your reply with "that now works"
you are not only changed select from using between to new notation
but yut you also changed the end date format from "31/12/2004" to "2004-12-31"
this format would work in your original query as well as it is a valid date in SQL... try it 🙂
August 15, 2011 at 8:24 am
hi
the reason i wanted this...
select * from Sales.SalesOrderHeader
where OrderDate between 01/01/2004 and 31/12/2004
...is because i'm trying out SSRS using BIDS. i wanted the date to be a parameter value from a textbox/date picker within the report. when i try to run the report the using @startDate/@endDate the parameter pop up window appears and i type in 01/01/2004 to whatever and it returns nothing. it now works with the xxxx-xx-xx format but no one will want to type the date in that format.
August 15, 2011 at 10:25 am
well the issue is not the start date but the end date.
"31/12/2004" is not a valid date in SQL server unless the server default is set to a locale(location or country code ) that use this format for the date values.
otherwise the transformation will return either 0 or an error "invalid date format" or something thus resulting the empty qurey return.
you realy have 2 options here.
1. validate user input before constracting the query
-- this however might have some issues in it self as the report might be smarter then SQL and see this date as valid.
2. process each date as if they were direct strings and if the date can not be safly converted to a valid SQL date try to massage it untill it can be.
IE. option 2
-- set incomming values
declare @StartDate varchar(10), @EndDate varchar(10)
SET @StartDate = '01/01/2004'
SET @EndDate = '31/12/2004'
use [TestManufacturing]
Go
if(object_id(N'[dbo].[udf_GetValidDateFromUserInputString]') > 0 )
drop function [dbo].udf_GetValidDateFromUserInputString
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.udf_GetValidDateFromUserInputString (
@UserInput varchar(10)
,@outputFormat int = 120)
RETURNS DateTime
AS
BEGIN
DECLARE @Result datetime
DECLARE @COUNT int
declare @ErrorNum int
SET @COUNT = 0
SELECT @Result = CONVERT(dateTime, @UserInput,@outputFormat)
select @ErrorNum = @@ERROR
if @ErrorNum <> 0
Begin
WHILE @ErrorNum <> 0 OR (@ErrorNum <> 0 AND @COUNT > 2)
Begin
-- Process the input string as needed to get the valid date or to fail
declare @Month varchar(2),@Date varchar(2) ,@Year varchar(4), @TempDate varchar(10)
select @TempDate = @UserInput -- get the value of user input into Temp variable
-- split temp value into Month/Date/Year
-- assumption here is the Input value is ALWAYS in "MM/DD/YYYY" format with "/" as delimiter
select @Month = SUBSTRING(@TempDate,0, CHARINDEX('/',@TempDate,0))
,@Date = SUBSTRING(@TempDate, CHARINDEX('/',@TempDate,DATALENGTH(@Month))+1,2)
,@Year =SUBSTRING(@TempDate, CHARINDEX('/',@TempDate,DATALENGTH(@Month)+ DATALENGTH(@Date))+1,4)
-- check if Month value is wrong hence we are in this block of code and build new temp date swapping the month and date values
if(@Month > 12)
SET @TempDate = @Date + '/' + @Month + '/' + @Year
-- try to convert new date value
SELECT @Result = CONVERT(dateTime, @TempDate,@outputFormat)
-- if we still error out stop processing and return null date
select @ErrorNum = @@ERROR
if @ErrorNum <> 0
begin
SET @COUNT += 1
SET @Result = NULL
end
End
End
return @Result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
August 16, 2011 at 6:59 am
wow. a little more complicated than i'd expected. trying to get my head around this now. i'll let you know if it works out. thanks.
August 16, 2011 at 7:15 am
Vlad-207446 (8/15/2011)
2. process each date as if they were direct strings and if the date can not be safly converted to a valid SQL date try to massage it untill it can be.
Assuming the date will always come in as DD/MM/YYYY, why not just convert it to datetime?
DECLARE @test-2 VARCHAR(20)
SET @test-2 = '31/12/2007'
SELECT CONVERT(DATETIME, @test-2, 103)
So the OPs original query becomes : -
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate >= CONVERT(DATETIME, '01/01/2004', 103) AND OrderDate < CONVERT(DATETIME, '31/12/2004', 103)
Obviously this doesn't validate the date, but since it's not being stored I don't really see that as a database task anyway. I'd be validating it in the business logic.
August 16, 2011 at 7:18 am
spin (8/16/2011)
wow. a little more complicated than i'd expected. trying to get my head around this now. i'll let you know if it works out. thanks.
It doesn't need to be this complicated. When converting from a string to a DATETIME, CONVERT uses the style to interpret the input string (when converting from a DATETIME to a string data type, it shapes the output string);
DECLARE @StartDate VARCHAR(10), @EndDate VARCHAR(10)
SET @StartDate = '01/01/2004'
SET @EndDate = '31/12/2004'
SET DATEFORMAT MDY
SELECT @StartDate, CONVERT(DATETIME, @StartDate, 103)
SELECT @EndDate, CONVERT(DATETIME, @EndDate, 103)
SET DATEFORMAT DMY
SELECT @StartDate, CONVERT(DATETIME, @StartDate, 103)
SELECT @EndDate, CONVERT(DATETIME, @EndDate, 103)
Eugene Elutin (8/16/2011)
I see I've just duplicated the previous advice. Yeah, that's what happens when not refreshing before posting .....
Join the queue mate!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2011 at 7:20 am
Vlad-207446 (8/15/2011)
well the issue is not the start date but the end date."31/12/2004" is not a valid date in SQL server unless the server default is set to a locale(location or country code ) that use this format for the date values.
...
You don't need to change SQL Server locale to convert you strings into dates. Just use:
set dateformat dmy
select cast ('05/08/2011' as datetime)
select cast ('31/12/2011' as datetime) -- you will see it will work the same in any locale
Just to see the difference try:
set dateformat mdy
select cast ('05/08/2011' as datetime)
select cast ('31/12/2011' as datetime)
Feel the difference 😀
I see I've just duplicated the previous advice. Yeah, that's what happens when not refreshing before posting .....
August 16, 2011 at 9:58 am
SSC -- please disregard my post with all that crazy function of mine 🙂 it was designed for something else and I just tried to adapt it for this case...
for some reason when I run the convert(datetime, var,120) function yesterday it error out on the end date but today it works as expected
so it is not complicated at all. just add convert()
to the column and both between values like so (that is if you want to keep the "Between" syntax that is...)
select * from Sales.SalesOrderHeader
where CONVERT(DATETIME, OrderDate , 103)
between CONVERT(DATETIME, @StartDate, 103) and CONVERT(DATETIME, @EndDate, 103)
August 16, 2011 at 10:04 am
Vlad-207446 (8/16/2011)
SSC -- please disregard my post with all that crazy function of mine 🙂 it was designed for something else and I just tried to adapt it for this case...for some reason when I run the convert(datetime, var,120) function yesterday it error out on the end date but today it works as expected
so it is not complicated at all. just add convert()
to the column and both between values like so (that is if you want to keep the "Between" syntax that is...)
select * from Sales.SalesOrderHeader
where CONVERT(DATETIME, OrderDate , 103)between CONVERT(DATETIME, @StartDate, 103) and CONVERT(DATETIME, @EndDate, 103)
Vlad - it shouldn't be necessary to convert the OrderDate column to DATETIME. More importantly, it would probably prevent the optimiser from choosing a plan based on the existence of an index on OrderDate.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply