Date conversions and arithmetic

Hi folks,
New to SQL programming and reading about CONVERT, CAST and the DATE
functions aren't giving me enough info. to solve my problem.
My data looks like this:
20050416000000 Admitted 33
20050416000000 Discharged 26
20050416000000 Registered 70
20050417000000 Admitted 33
20050417000000 Discharged 21
20050417000000 Registered 65
Column I'm interested in Discussing is the first, which is stored in its
table as a VARCHAR(25) and is called DateID. So, clearly it's not a DATETIM
E
or SMALLDATETIME.
The first 8 characters represent the yyyymmdd but it's just a string. The
trailing zeros stored seem to be junk.
I need todo the SELECT to get that column from today and only the previous
seven days. Also, I want it formatted in mm/dd/yyyy in my output if
possible.
This is the closest I've gotten:
Select DateID
from DispositionCount
WHERE ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
(DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
(DateID,7,2), DATEPART(day,GETDATE()))<= 7)
Can anyone tell me how to do this? Getting the DBA to change the column to a
proper datetime is not feasible.
Thanks,
hbcpTry This...
Select ... Convert(VarChar(10), Cast(Left(DateID, 8) As DateTime), 101)
DateName
From ...
Where Left(DateID, 8) Between DateAdd(day, -7, getdate() And getdate()
"hbcp_1" wrote:

> Hi folks,
> New to SQL programming and reading about CONVERT, CAST and the DATE
> functions aren't giving me enough info. to solve my problem.
> My data looks like this:
> 20050416000000 Admitted 33
> 20050416000000 Discharged 26
> 20050416000000 Registered 70
> 20050417000000 Admitted 33
> 20050417000000 Discharged 21
> 20050417000000 Registered 65
> Column I'm interested in Discussing is the first, which is stored in its
> table as a VARCHAR(25) and is called DateID. So, clearly it's not a DATET
IME
> or SMALLDATETIME.
> The first 8 characters represent the yyyymmdd but it's just a string. The
> trailing zeros stored seem to be junk.
> I need todo the SELECT to get that column from today and only the previous
> seven days. Also, I want it formatted in mm/dd/yyyy in my output if
> possible.
> This is the closest I've gotten:
> Select DateID
> from DispositionCount
> WHERE ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> (DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
> (DateID,7,2), DATEPART(day,GETDATE()))<= 7)
> Can anyone tell me how to do this? Getting the DBA to change the column to
a
> proper datetime is not feasible.
> Thanks,
> hbcp|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I believe I'd do it this way - create a derived table that acts as my
own special View (since the DBA is so tight):
SELECT DateID, Status, Tally
FROM
(SELECT LEFT(DateID,8) As DateID, Status, Tally
FROM DispositionCount) As A
WHERE DateID BETWEEN CONVERT(VARCHAR(10),CURRENT_TIMESTAMP-7,112)
AND CONVERT(VARCHAR(10), CURRENT_TIMESTAMP,112)
Substitute the correct column names for the ones I used.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmVLHoechKqOuFEgEQJ1sACgtQThI8u3Vt5W
ILgEnFdsoU9d9V8An04W
o1H4Q/Gf0Z2e3AjCDugl6m/Q
=ykSG
--END PGP SIGNATURE--
hbcp_1 wrote:
> Hi folks,
> New to SQL programming and reading about CONVERT, CAST and the DATE
> functions aren't giving me enough info. to solve my problem.
> My data looks like this:
> 20050416000000 Admitted 33
> 20050416000000 Discharged 26
> 20050416000000 Registered 70
> 20050417000000 Admitted 33
> 20050417000000 Discharged 21
> 20050417000000 Registered 65
> Column I'm interested in Discussing is the first, which is stored in its
> table as a VARCHAR(25) and is called DateID. So, clearly it's not a DATET
IME
> or SMALLDATETIME.
> The first 8 characters represent the yyyymmdd but it's just a string. The
> trailing zeros stored seem to be junk.
> I need todo the SELECT to get that column from today and only the previous
> seven days. Also, I want it formatted in mm/dd/yyyy in my output if
> possible.
> This is the closest I've gotten:
> Select DateID
> from DispositionCount
> WHERE ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> (DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
> (DateID,7,2), DATEPART(day,GETDATE()))<= 7)
> Can anyone tell me how to do this? Getting the DBA to change the column to
a
> proper datetime is not feasible.|||Thanks! Not quite, but I can see it's closer... some syntax error marking at
'And'?
Is DateName a constant or declaration of a variable on the fly?
"CBretana" wrote:
> Try This...
> Select ... Convert(VarChar(10), Cast(Left(DateID, 8) As DateTime), 101)
> DateName
> From ...
> Where Left(DateID, 8) Between DateAdd(day, -7, getdate() And getdate()
> "hbcp_1" wrote:
>|||Untested:
select Select DateID
from DispositionCount
WHERE cast(left(DateID,8) as datetime) >= convert(datetime, dateadd(d, -7,
getdate()), 112)
((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> (DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
> (DateID,7,2), DATEPART(day,GETDATE()))<= 7)
cast(left('20050416000000',8) as datetime)
"hbcp_1" <hbcp_1@.discussions.microsoft.com> wrote in message
news:633B8157-4016-4BDF-A552-FE87BCA7C23F@.microsoft.com...
> Hi folks,
> New to SQL programming and reading about CONVERT, CAST and the DATE
> functions aren't giving me enough info. to solve my problem.
> My data looks like this:
> 20050416000000 Admitted 33
> 20050416000000 Discharged 26
> 20050416000000 Registered 70
> 20050417000000 Admitted 33
> 20050417000000 Discharged 21
> 20050417000000 Registered 65
> Column I'm interested in Discussing is the first, which is stored in its
> table as a VARCHAR(25) and is called DateID. So, clearly it's not a
> DATETIME
> or SMALLDATETIME.
> The first 8 characters represent the yyyymmdd but it's just a string. The
> trailing zeros stored seem to be junk.
> I need todo the SELECT to get that column from today and only the previous
> seven days. Also, I want it formatted in mm/dd/yyyy in my output if
> possible.
> This is the closest I've gotten:
> Select DateID
> from DispositionCount
> WHERE ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> (DateID,5,2) = DATEPART(month,GETDATE()))) and (DATEDIFF(day, SUBSTRING
> (DateID,7,2), DATEPART(day,GETDATE()))<= 7)
> Can anyone tell me how to do this? Getting the DBA to change the column to
> a
> proper datetime is not feasible.
> Thanks,
> hbcp|||CBretana -- THANKS, just was missing a parenthesis...
Much obliged. Now I can backtrack through the help to learn what you taught
me. :-)
"CBretana" wrote:
> Try This...
> Select ... Convert(VarChar(10), Cast(Left(DateID, 8) As DateTime), 101)
> DateName
> From ...
> Where Left(DateID, 8) Between DateAdd(day, -7, getdate() And getdate()
> "hbcp_1" wrote:
>|||Thanks, will try all three suggestions. I'm not only using the query in SQL
Query Analyzer, it's actually being executed by another software product
(Java-based) and alas... the first option didn't work in that product. Need
to include the slashes too...
"Raymond D'Anjou" wrote:

> Untested:
> select Select DateID
> from DispositionCount
> WHERE cast(left(DateID,8) as datetime) >= convert(datetime, dateadd(d, -7
,
> getdate()), 112)
> ((SUBSTRING(DateID,1,4) = DATEPART(year,GETDATE())) and (SUBSTRING
> cast(left('20050416000000',8) as datetime)
> "hbcp_1" <hbcp_1@.discussions.microsoft.com> wrote in message
> news:633B8157-4016-4BDF-A552-FE87BCA7C23F@.microsoft.com...
>
>|||I was making a mistake elsewhere, I've got it perfect now. CBretana's works
best. It's exactly what I wanted and clean, efficient code. THANKS everyon
e.
"hbcp_1" wrote:
> Thanks, will try all three suggestions. I'm not only using the query in S
QL
> Query Analyzer, it's actually being executed by another software product
> (Java-based) and alas... the first option didn't work in that product. Ne
ed
> to include the slashes too...
> "Raymond D'Anjou" wrote:
>|||Since getdate() includes a time portion.
Also, a lot of people in this forum find it less intuitive to use Between
with dates:
(I'm not looking for a fight CBretana)
Select ... Convert(VarChar(10), Cast(Left(DateID, 8) As DateTime), 101)
DateName
From ...
Where Left(DateID, 8) >= CONVERT(DATETIME, DateAdd(day, -7, getdate()), 112)
And Left(DateID, 8) <= CONVERT(DATETIME, getdate(), 112)
"hbcp_1" <hbcp_1@.discussions.microsoft.com> wrote in message
news:1016A71C-28F8-4E9D-A51B-F1DACCEFFF96@.microsoft.com...
>I was making a mistake elsewhere, I've got it perfect now. CBretana's works
> best. It's exactly what I wanted and clean, efficient code. THANKS
> everyone.
>
> "hbcp_1" wrote:
>|||Yes, sorry, missing Parentheses!
"hbcp_1" wrote:
> CBretana -- THANKS, just was missing a parenthesis...
> Much obliged. Now I can backtrack through the help to learn what you taug
ht
> me. :-)
> "CBretana" wrote:
>