Help with dates - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with dates

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-15 : 00:30:09

A number of months ago, I got some help to write the following query. I would like to see if it's possible to make adjustments as my results are a bit "off".

Query:
SELECT *
FROM
(
SELECT
DATEADD(DD,V.NUMBER,
CAST(
SUBSTRING(CONVERT(VARCHAR,T.FROMDATE),5,2)+'/'+SUBSTRING(CONVERT(VARCHAR,T.FROMDATE),7,2)+'/'+SUBSTRING(CONVERT(VARCHAR,T.FROMDATE),1,4) AS DATETIME) ) AS DATE

FROM TKABSENCES T
CROSS JOIN MASTER..SPT_VALUES V

WHERE V.TYPE='P'
AND T.FROMDATE>={?FROM}
AND T.TODATE<={?TO}
AND DATEADD(DD,V.NUMBER,
CAST(
SUBSTRING(CONVERT(VARCHAR,T.FROMDATE),5,2)+'/'+SUBSTRING(CONVERT(VARCHAR,T.FROMDATE),7,2)+'/'+SUBSTRING(CONVERT(VARCHAR,T.FROMDATE),1,4) AS DATETIME)
)<=CAST(
SUBSTRING(CONVERT(VARCHAR,T.TODATE),5,2)+'/'+SUBSTRING(CONVERT(VARCHAR,T.TODATE),7,2)+'/'+SUBSTRING(CONVERT(VARCHAR,T.TODATE),1,4) AS DATETIME)
)T

ORDER BY DATE ASC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The purpose of the query was to be able to assign individual dates between a date range.

Sample data:

FromDate ToDate Date
20091220 20091223 12/20/2009
20091220 20091223 12/21/2009
20091220 20091223 12/22/2009
20091220 20091223 12/23/2009
20091221 20091221 12/21/2009

If I use the above query and use the following parameters:

{?From}=20091221
{?To} =20091221

It will only display row 5. Is there a way to adjust
the query to display rows 2 and 5 instead of just 5 while using
the same parameters?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-15 : 01:12:59

try like this
simple example
declare @t table(d varchar(32), d2 varchar(32))

insert into @t select '2009221','2009225'
insert into @t select '2009211','2009317'
insert into @t select '200941','200955'

select * from @t where d between '200911' and '2009325'

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-15 : 01:32:15

Hi Bklr,

Thanks for the reply but I'm not following the numbering you're using.

Also, I need to be able to select any date range at a time. I will never know if there are records beyond the original parameter input that I choose.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-15 : 02:06:40

[code]
declare @TKABSENCES table
(
FROMDATE varchar(10),
TODATE varchar(10)
)

insert into @TKABSENCES
SELECT '20091220', '20091223' union all
SELECT '20091221', '20091221'

declare @fromdate varchar(10),
@todate varchar(10)

select @fromdate = '20091221',
@todate = '20091221'

SELECT FROMDATE, TODATE, DATE
FROM
(
SELECT t.FROMDATE, t.TODATE, DATE = DATEADD(DAY, v.number, t.FROMDATE)
FROM @TKABSENCES t
CROSS JOIN master..spt_values v
WHERE v.type = 'P'
AND v.number <= datediff(day, t.FROMDATE, t.TODATE)
) d
WHERE d.DATE >= @fromdate
AND d.DATE <= @todate
[/code]

Your FROMDATE and TODATE is a varchar ? should use dateime

KH
[spoiler]Time is always against us[/spoiler]

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-15 : 13:09:46

KH,
Thanks for the idea. 2 things...it didn't work that way but it got me to thinking about a different direction to go which worked. The problem is really that the Fromdate and Todate fields vary too much to really be anchors. So I put a free text parameter in, converted it to datetime and that works for my start/end dates.

As for the dates being varchar...actually, they're int, but in order to convert them to datetime, I need to use the substring function. I know it's clunky but it ultimate works.

The performance on this query is pretty slow, especially since I'm plugging the query into Crystal (Command Object):

SELECT *
FROM
(
SELECT t.EmployeeID AS Employee, T.AbsenceTypeId, T.FromDate, T.ToDate,
Convert(Varchar,
DATEADD(dd,v.number,
CAST(
Substring(convert(varchar,T.FromDate),5,2)+'-'+Substring(convert(varchar,T.FromDate),7,2)+'-'+Substring(Convert(Varchar,T.FromDate),3,2) as Datetime)
),10) AS Date

FROM TKAbsences t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,
CAST(
Substring(convert(varchar,T.FromDate),5,2)+'-'+Substring(convert(varchar,T.FromDate),7,2)+'-'+Substring(Convert(Varchar,T.FromDate),3,2) as Datetime)
)<=CAST(
Substring(convert(varchar,T.ToDate),5,2)+'-'+Substring(convert(varchar,T.ToDate),7,2)+'-'+Substring(Convert(Varchar,T.ToDate),3,2) as Datetime)
)t

WHERE T.Date BETWEEN

CAST(
Substring(convert(varchar,{?From}),5,2)+'/'+
Substring(convert(varchar,{?From}),7,2)+'/'+
Substring(convert(varchar,{?From}),1,4) as Datetime)

AND

CAST(
Substring(convert(varchar,{?To}),5,2)+'/'+
Substring(convert(varchar,{?To}),7,2)+'/'+
Substring(convert(varchar,{?To}),1,4) as Datetime)

ORDER BY Employee, Date asc

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-15 : 15:36:08

Ok...I tried to apply the conversion directly from INT to DATETIME as in this query and seems to be performing faster. Thanks again for your help!

SELECT *
FROM
(
SELECT t.EmployeeID AS Employee, T.AbsenceTypeId, T.FromDate, T.ToDate,
DATEADD(dd,v.number,
CAST(convert(char(8),CAST(T.FromDate AS int)) As Datetime)
) AS Date
FROM TKAbsences t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,v.number,
CAST(convert(char(8),CAST(T.FromDate AS int)) As Datetime))<=
CAST(convert(char(8),CAST(T.ToDate AS int)) As Datetime)
)t

WHERE T.Date BETWEEN
CAST(convert(char(8),CAST({?From} AS int)) As Datetime)
AND
CAST(convert(char(8),CAST({?To} AS int)) As Datetime)

ORDER BY Employee, Date asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources