Date range

What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @.Begin Date AND @.EndDate. I want to include all of the @.EndDate.well, BETWEEN is inclusive but your probably not receiving all of the enddates due to the time part of datetime datatype.

Another method is to add 1 day to the enddate (with no time indicator) and use:

WHERE date1 >= @.beginDate AND date1 < @.enddate

|||I used WHERE date1 BETWEEN @.Begin Date AND @.EndDate + '24:59:59' to include the data from the last day of the search.

So I assume there is no benefit from using between to < and >.|||Your query is not correct. It will exclude 1 second before midnight (SQL Server datetimes do include ms)

You should be using:

where Date1 >= @.Begin
 and Date1 < @.End + 1

OR

where convert(datetime, convert(varchar(10), Date1, 111), 111) = @.Begin

if it's only one day|||Using the CONVERT function would make the query non-sargable -- don't do it that way!! Definitely stick with Pierre's first option.

Terri|||Adding one to the date doesn't pose any issues? @.date + 1?|||As long as @.date does not contain any time data (ie it is the default midnight 00:00:00.000) that method should be fine.

Terri

Date formats in SQL

I need to compare a date field in a WHERE clause and I'm finding it very
difficult to do so.
I have a date stored in tables as integers like so:
20060317
etc
I need to compare todays date (less a day) to get the data out of the
table...so my query looks like this:
WHERE
IDPERI > 200601 and ididat =
case when UPPER(datename(dw,getdate())) = 'MONDAY'
then
cast(dateadd(dd,-3,getdate()) as int)
--and ididat < 20060320
else
cast(dateadd(dd,-1,getdate()) as int)
--and ididat < 20060320
end
However, the GetDate() statements between the then and else clauses return
the following:
2006-03-17 15:08:15.663
Now I just want the date to be yyyymmdd so I can compare. I have tried just
about everything I know and I cannot get the format to be yyyymmdd.
Thx
BillyBil, CAST your integer AS CHAR(8) il will workd fine.
A +
Billy a écrit :
> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SET @.DT_CURDATE = CAST(FLOOR(CAST(getdate() AS DECIMAL(15, 8))) AS DATETIME)
And then use @.DT_CURDATE instead of getdate()
Hope that helps
"Billy" wrote:

> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy|||On Mon, 20 Mar 2006 07:57:32 -0800, Billy wrote:

>I need to compare a date field in a WHERE clause and I'm finding it very
>difficult to do so.
>I have a date stored in tables as integers like so:
>20060317
Hi Billy,
Don't do this.
Change your DB. Store the date as a datetime or a smalldatetime. It'll
save you lots of headaches.
(snip)
>However, the GetDate() statements between the then and else clauses return
>the following:
>2006-03-17 15:08:15.663
>Now I just want the date to be yyyymmdd so I can compare. I have tried just
>about everything I know and I cannot get the format to be yyyymmdd.
Ugly kludge:
SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS int)
Hugo Kornelis, SQL Server MVP

date format, select convert(varchar(8), getdate(), 1)

With this clause "select convert(varchar(16), getdate(), 101)" I can get mmddyyyy but

How can I get time such as mmddyyyyhhmm

Thanks
Daniel

I do not believe there is a standard format that will give you exactly what you are wanting. Here is how to do it using DATEPART and REPLICATE to pad the items out.

Code Snippet

SELECT REPLICATE('0', 2-LEN(DATEPART(mm, getdate()))) + CAST(DATEPART(mm, getdate()) AS VARCHAR(2)) + REPLICATE('0', 2-LEN(DATEPART(dd, getdate()))) + CAST(DATEPART(dd, getdate()) AS VARCHAR(2)) + CAST(DATEPART(YY, getdate()) AS VARCHAR(4)) + REPLICATE('0', 2-LEN(DATEPART(hh, getdate()))) + CAST(DATEPART(hh, getdate()) AS VARCHAR(2)) + REPLICATE('0', 2-LEN(DATEPART(mi, getdate()))) + CAST(DATEPART(mi, getdate()) AS VARCHAR(2))

|||

select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

This will get the format of hh:mms (108) and you just replace the : with nothing and take the last 2 characters off.

|||

Very good

I have mmddyyyyhhmm but a little error

I get 04/19/20071223

should be 04/19/2007 12:23

Thanks
Daniel

|||

Code Snippet

create function dbo.fn_FormatADate(

@.dtInput datetime

)

returns char(13)

as

begin

return replace(Replace( convert(varchar,@.dtInput,101) + left(convert(varchar,@.dtInput,108),5) ,'/',''),':','')

end

GO

select dbo.fn_FormatADate(getdate())

GO

|||

select convert(varchar(16), getdate(), 101)+' '+LEFT(convert(varchar, getdate(), 108),5)

That should give you the format that you illustrated in your "should be".

|||

Hi Ben,

Glad to see you here.

Regards,

Alejandro Mesa

|||

Just a thought...since we always know the len of the output, replicate is a bit more combersome than using "RIGHT"...

Code Block

declare @.TheDate datetime

set @.TheDate = GetDate()

select

right('0' + convert(varchar,datepart(Month,@.TheDate)), 2) --The Month

+ right('0' + convert(varchar,datepart(Day,@.TheDate)), 2) --The Day

+ convert(varchar,datepart(Year,@.TheDate)) --For the Year

+ right('0' + convert(varchar,datepart(hour,@.TheDate)), 2) --The hour

+ right('0' + convert(varchar,datepart(minute,@.TheDate)), 2) --The minute

Date Comparison excluding time

I have a stored procedure that is suppose to get all records that have an expiration date of today. My where clause is as follows:

Round2ExpDate <= getdate()

This is not working because of the time factore. What is the best way to truncate the time for each date? All recommendations are greatly appreciated.You can remove time by casting a datetime datatype as a varchar.