I am considering the following snippet to compare a date range...

DECLARE
    @CurDate VARCHAR(14)

SET @CurDate =
SELECT CONVERT(VARCHAR, GETDATE(),112) + CONVERT(VARCHAR,DATEPART(HOUR,
GETDATE())) + CONVERT(VARCHAR,DATEPART(MINUTE, GETDATE())) +
CONVERT(VARCHAR,DATEPART(SECOND, GETDATE()))

then...
SELECT
    someval
FROM
    sometable x
WHERE
    @CurDate BETWEEN x.StartDate AND x.EndDate

is this resonable? or is there a better and or more elegant way to perform
this same task?

thanks in advance.

I don't know that it is any better or more elegant, but here is an
alternative you might consider for the conversion process.  The first
column is what we are starting with, the second is the alternative,
and the third is the example you gave.

SELECT convert(varchar(19),getdate(),121),
       REPLACE(REPLACE(REPLACE(
               convert(varchar(19),getdate(),121),
               '-',''),' ',''),':',''),
       CONVERT(VARCHAR, GETDATE(),112) +
CONVERT(VARCHAR,DATEPART(HOUR,
       GETDATE())) + CONVERT(VARCHAR,DATEPART(MINUTE, GETDATE())) +
       CONVERT(VARCHAR,DATEPART(SECOND, GETDATE()))

Roy Harvey
Beacon Falls, CT

On Fri, 7 Apr 2006 15:43:02 -0700, LvBohemian
<LvBohem***@discussions.microsoft.com> wrote:

Show quoteHide quote

Here's another option...

SELECT
  CONVERT(VARCHAR(8), GETDATE(), 112)
    + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 8), ':', '')

In your expression, the hour, minute and second units are not prefixed with
a leading zero when consisting of a single digit, so you might end up with a
wrong character representation.

Show quoteHide quote

LvBohemian (LvBohem***@discussions.microsoft.com) writes:
Show quoteHide quoteWhat do really want to achieve? What data types are StartDate and EndDate?

what I am trying to do is compare the current datetime passed in getdate() as
a parameter to a proc to determine if the current date down to the minute at
least falls between an existing startdate and enddate and between an existing
time also (hours, minutes for current day), if so, return any applicable
columns that meet said criteria...

startdate and enddate can be whatever I want, I am hoping to use just
datetime but there appears to be problems when I compare just the minutes
part of the current datetime with the minutes of the startdate and enddate or
starttime endtime datetimes that are also in the applicable date range less
time...

so I am trying to do something like this...

SELECT
    someval,
FROM
    sometable
WHERE
     @CurDate BETWEEN StartDate AND EndDate 
     AND @CurDateHoursMinuesOnly BETWEEN StartTime AND EndTime

CurDate, StartDate, EndDate I think would ideally be datetime and
CurDateHoursMinuesOnly, StartTime, EndTime would be time only...

Table could get big so I want to avoid doing any parsing in a where clause
of a stored proc, so I can make use of indexes...

Comments, suggestions etc would be most welcome,
thanks in advance.

LvBohemian (LvBohem***@discussions.microsoft.com) writes:
I am considering the following snippet to compare a date range...

DECLARE
     @CurDate VARCHAR(14)

SET @CurDate =
SELECT CONVERT(VARCHAR, GETDATE(),112) + CONVERT(VARCHAR,DATEPART(HOUR,
GETDATE())) + CONVERT(VARCHAR,DATEPART(MINUTE, GETDATE())) +
CONVERT(VARCHAR,DATEPART(SECOND, GETDATE()))

then...
SELECT
     someval
FROM
     sometable x
WHERE
     @CurDate BETWEEN x.StartDate AND x.EndDate

is this resonable? or is there a better and or more elegant way to perform
this same task?

re:
What do really want to achieve? What data types are StartDate and EndDate?
-
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Why do you have date and time stored in two seperate columns?  If you had
the time stored in StartDate and EndDate all you would need is:

SELECT
    someval,
FROM
    sometable
WHERE
     getdate() BETWEEN StartDate AND EndDate

or, better yet, use CURRENT_TIMESTAMP which is portable


Show quoteHide quotehttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Show quoteHide quote

maybe I am missing something...

this is for another program to determine a re-occuring task...

I need to see if now falls between a current date range less time and if the
the current hours, minutes are also in the time only range...

suggestions?

re:
Why do you have date and time stored in two seperate columns?  If you had
the time stored in StartDate and EndDate all you would need is:

SELECT
     someval,
FROM
     sometable
WHERE
      getdate() BETWEEN StartDate AND EndDate

or, better yet, use CURRENT_TIMESTAMP which is portable

OK, so the task is scheduled by time to run within a given date range.  That
explains the need to have two fields.

In order to compare only time, I believe you need to convert your columns as
well as your parameters.  The same goes for comparing only the date.

As your table is, I think you need to do a lot of conversion and
concatenation.  A better way to do this would be with computed columns, but
first a really convoluted way to do this.

There should be a simpler way to code this without computed columns, but
this is what comes to mind at the moment.

i.e.

DECLARE @CurDate VARCHAR(14)
DECLARE @CurDateHoursMinuesOnly VARCHAR(14)
DECLARE @CurYear VARCHAR(4)
DECLARE @CurMonth VARCHAR(2)
DECLARE @CurDay VARCHAR(2)
DECLARE @CurHour VARCHAR(2)
DECLARE @CurMinute VARCHAR(2)

set @CurYear = datepart(year,current_timestamp)
set @CurMonth = (select case when
len(convert(varchar,datepart(month,current_timestamp)))=1 then '0' +
convert(varchar,datepart(month,current_timestamp)) else
convert(varchar,datepart(month,current_timestamp)) end)
set @CurDay = (select case when
len(convert(varchar,datepart(day,current_timestamp)))=1 then '0' +
convert(varchar,datepart(day,current_timestamp)) else
convert(varchar,datepart(day,current_timestamp)) end)
set @CurHour = (select case when
len(convert(varchar,datepart(hh,current_timestamp)))=1 then '0' +
convert(varchar,datepart(hh,current_timestamp)) else
convert(varchar,datepart(hh,current_timestamp)) end)
set @CurMinute = (select case when
len(convert(varchar,datepart(mi,current_timestamp)))=1 then '0' +
convert(varchar,datepart(mi,current_timestamp)) else
convert(varchar,datepart(mi,current_timestamp)) end)
set @CurDate = @CurYear + @CurMonth + @CurDay
set @CurDateHoursMinuesOnly = @CurHour + @CurMinute

-- select the individual parts of the date comparison criteria so we can see
what we are working with
select
@CurDate
,
convert(varchar,datepart(year,StartDate)) + (case when
len(convert(varchar,datepart(month,StartDate)))=1 then '0' + convert(varchar
,datepart(month,StartDate)) else convert(varchar,datepart(month,StartDate))
end) + (select case when len(convert(varchar,datepart(day,StartDate)))=1
then '0' + convert(varchar,datepart(day,StartDate)) else
convert(varchar,datepart(day,StartDate)) end)
,
convert(varchar,datepart(year,EndDate)) + (case when
len(convert(varchar,datepart(month,EndDate)))=1 then '0' +
convert(varchar,datepart(month,EndDate)) else
convert(varchar,datepart(month,EndDate)) end) + (select case when
len(convert(varchar,datepart(day,EndDate)))=1 then '0' +
convert(varchar,datepart(day,EndDate)) else
convert(varchar,datepart(day,EndDate)) end)
,
@CurDateHoursMinuesOnly
,
(case when len(convert(varchar,datepart(hh,StartTime)))=1 then '0' +
convert(varchar,datepart(hh,StartTime)) else
convert(varchar,datepart(hh,StartTime)) end)  + (select case when
len(convert(varchar,datepart(mi,StartTime)))=1 then '0' +
convert(varchar,datepart(mi,StartTime)) else
convert(varchar,datepart(mi,StartTime)) end)
,
(case when len(convert(varchar,datepart(hh,endtime)))=1 then '0' +
convert(varchar,datepart(hh,endtime)) else
convert(varchar,datepart(hh,endtime)) end)  + (select case when
len(convert(varchar,datepart(mi,EndTime)))=1 then '0' +
convert(varchar,datepart(mi,EndTime)) else
convert(varchar,datepart(mi,EndTime)) end)
from sometable

-- select only what we need based on the criteria
SELECT
    *
FROM
    sometable
WHERE
     @CurDate BETWEEN
convert(varchar,datepart(year,StartDate)) + (case when
len(convert(varchar,datepart(month,StartDate)))=1 then '0' +
convert(varchar,datepart(month,StartDate)) else
convert(varchar,datepart(month,StartDate)) end) + (select case when
len(convert(varchar,datepart(day,StartDate)))=1 then '0' +
convert(varchar,datepart(day,StartDate)) else
convert(varchar,datepart(day,StartDate)) end)
and
convert(varchar,datepart(year,EndDate)) + (case when
len(convert(varchar,datepart(month,EndDate)))=1 then '0' +
convert(varchar,datepart(month,EndDate)) else
convert(varchar,datepart(month,EndDate)) end) + (select case when
len(convert(varchar,datepart(day,EndDate)))=1 then '0' +
convert(varchar,datepart(day,EndDate)) else
convert(varchar,datepart(day,EndDate)) end)
     AND @CurDateHoursMinuesOnly BETWEEN
(case when len(convert(varchar,datepart(hh,StartTime)))=1 then '0' +
convert(varchar,datepart(hh,StartTime)) else
convert(varchar,datepart(hh,StartTime)) end)  + (select case when
len(convert(varchar,datepart(mi,StartTime)))=1 then '0' +
convert(varchar,datepart(mi,StartTime)) else
convert(varchar,datepart(mi,StartTime)) end)
AND (case when len(convert(varchar,datepart(hh,endtime)))=1 then '0' +
convert(varchar,datepart(hh,endtime)) else
convert(varchar,datepart(hh,endtime)) end)  + (select case when
len(convert(varchar,datepart(mi,EndTime)))=1 then '0' +
convert(varchar,datepart(mi,EndTime)) else
convert(varchar,datepart(mi,EndTime)) end)

Show quoteHide quote

doing all the conversion within the select will most likely hose
performance, where computed columns should be able to use indexes
efficently.  Here is a script with computed columns that are indexed:

CREATE TABLE [dbo].[sometable] (
[startdate] [datetime] NULL ,
[enddate] [datetime] NULL ,
[starttime] [datetime] NULL ,
[endtime] [datetime] NULL ,
[compstartdate] AS (convert(varchar,datepart(year,[StartDate])) + case when
(len(convert(varchar,datepart(month,[StartDate]))) = 1) then ('0' +
convert(varchar,datepart(month,[StartDate]))) else
(convert(varchar,datepart(month,[StartDate]))) end + case when
(len(convert(varchar,datepart(day,[StartDate]))) = 1) then ('0' +
convert(varchar,datepart(day,[StartDate]))) else
(convert(varchar,datepart(day,[StartDate]))) end) ,
[compenddate] AS (convert(varchar,datepart(year,[EndDate])) + case when
(len(convert(varchar,datepart(month,[EndDate]))) = 1) then ('0' +
convert(varchar,datepart(month,[EndDate]))) else
(convert(varchar,datepart(month,[EndDate]))) end + case when
(len(convert(varchar,datepart(day,[EndDate]))) = 1) then ('0' +
convert(varchar,datepart(day,[EndDate]))) else
(convert(varchar,datepart(day,[EndDate]))) end) ,
[compstarttime] AS (case when
(len(convert(varchar,datepart(hour,[StartTime]))) = 1) then ('0' +
convert(varchar,datepart(hour,[StartTime]))) else
(convert(varchar,datepart(hour,[StartTime]))) end + case when
(len(convert(varchar,datepart(minute,[StartTime]))) = 1) then ('0' +
convert(varchar,datepart(minute,[StartTime]))) else
(convert(varchar,datepart(minute,[StartTime]))) end) ,
[compendtime] AS (case when (len(convert(varchar,datepart(hour,[endtime])))
= 1) then ('0' + convert(varchar,datepart(hour,[endtime]))) else
(convert(varchar,datepart(hour,[endtime]))) end + case when
(len(convert(varchar,datepart(minute,[EndTime]))) = 1) then ('0' +
convert(varchar,datepart(minute,[EndTime]))) else
(convert(varchar,datepart(minute,[EndTime]))) end)
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_sometable_start_end_date ON dbo.sometable
(
compstartdate,
compenddate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_sometable_startend_time ON dbo.sometable
(
compstarttime,
compendtime
) ON [PRIMARY]
GO

DECLARE @CurDate VARCHAR(14)
DECLARE @CurDateHoursMinuesOnly VARCHAR(14)
DECLARE @CurYear VARCHAR(4)
DECLARE @CurMonth VARCHAR(2)
DECLARE @CurDay VARCHAR(2)
DECLARE @CurHour VARCHAR(2)
DECLARE @CurMinute VARCHAR(2)

set @CurYear = datepart(year,current_timestamp)
set @CurMonth = (select case when
len(convert(varchar,datepart(month,current_timestamp)))=1 then '0' +
convert(varchar,datepart(month,current_timestamp)) else
convert(varchar,datepart(month,current_timestamp)) end)
set @CurDay = (select case when
len(convert(varchar,datepart(day,current_timestamp)))=1 then '0' +
convert(varchar,datepart(day,current_timestamp)) else
convert(varchar,datepart(day,current_timestamp)) end)
set @CurHour = (select case when
len(convert(varchar,datepart(hh,current_timestamp)))=1 then '0' +
convert(varchar,datepart(hh,current_timestamp)) else
convert(varchar,datepart(hh,current_timestamp)) end)
set @CurMinute = (select case when
len(convert(varchar,datepart(mi,current_timestamp)))=1 then '0' +
convert(varchar,datepart(mi,current_timestamp)) else
convert(varchar,datepart(mi,current_timestamp)) end)
set @CurDate = @CurYear + @CurMonth + @CurDay
set @CurDateHoursMinuesOnly = @CurHour + @CurMinute

SELECT
    *
FROM
    sometable
WHERE
     @CurDate BETWEEN compstartdate and compenddate
     AND @CurDateHoursMinuesOnly BETWEEN compstarttime and compendtime


Show quoteHide quoteconvert(varchar,datepart(month,StartDate))
Show quoteHide quote

LvBohemian (LvBohem***@discussions.microsoft.com) writes:
SELECT ...
FROM   tbl
WHERE  convert(char(8), getdate(), 112) BETWEEN StartDate and StopDate
  AND  replace(convert(char(8), getdate(), 108), ':', '')
       BETWEEN StartTime AND EndTime

I assuming here that StartTime and EndTime are stored as char(6),
24-hour clock.

Erland,
Am I mistaken, or will
convert(char(8), getdate(), 112) BETWEEN StartDate and StopDate
only work if StartDate has the default time of midnight?  i.e.

'2006-04-11' between '2006-04-01 14:00:00' and '2006-04-11 14:00:00'
will be false, because '2006-04-11' is 14 hours earlier than the start date?

Show quoteHide quote

Jim Underwood (james.underwoodATfallonclinic.com) writes:
Correct. I assumed a setup with StartDate and StopDate defining the
days in between the job may run. Of course saying

  getdate() BETWEEN StartDate and StopDate

will permit intervals like:

The problem is that if the columns are date-only, and StopDate is
2004-04-12, and getdate() is 2004-04-12 00:10, then people would
expect the job to run.

Full Text Setup
Trigger logic question - using If with AND and OR
Advanced T-SQL Question: Produce Backup Window Gantt Chart
assign a table to a path and filename?
CREATE ASSEMBLY failed because it could not open the physical file