Formula help

Report Field (LastApptDate)
I want to take the CurrentDate - LastApptDate and if its Less than 1500 days, to produce an "X" or else nothing
The second part is CurrentDate - LastApptDate - if it greater than 1500 days to produce an "X" else nothingI just started a new job and don't have access to anything yet, but if I had access to some data to test it, I think it would go something like this:

if DateDiff("d",LastApptDate,CurrentDate) >= 1500 then

"x"

else

""

or maybe like this:

if LastApptDate >= DateAdd("d", -1500, CurrentDate) then

"X"

else

""

format datetime without characters

I need to convert the date format with an output of yyyymmdd'
I have tried the following code but it doesn't produce the desired output
CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
Any suggestions?
Thanks in advanceThat should work if supp_creation_date is a DATETIME or SMALLDATETIME. What
result do you get? Can you post some code to reproduce it? What version of
SQL Server? Here's an example:
SELECT CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112
)
Result:
20050502
(1 row(s) affected)
If you get something else then I'd guess that in the context in which you
are using it the result is being implicitly cast to some other datatype.
David Portas
SQL Server MVP
--|||That seems to work for me:
Declare @.Date DateTime
Set @.Date = Current_TimeStamp
Select Convert(VarChar(8), @.Date, 112)
Produces:
20050502
What output are you getting?
Thomas
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry,
What is the type of column [supp_creation_date]?. If it is not a datetime
then you have to convert it to, before using the statement you posted.
Example:
declare @.s varchar(10)
set @.s = '05/02/2005'
select convert(char(8), convert(datetime, @.s, 101), 112)
go
-- this works
select convert(char(8), getdate(), 112)
go
AMB
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry, Your column in your table must not be a datetime... you'll have to
cast it to a datetime first.
Try this
Select CONVERT(VARCHAR(8), Cast(supp_creation_date As DateTime) , 112 )
From YourTable
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Does it produce the proper format in Query Analyzer? This is always the
first place to try stuff out, as QA is very good not to insert it's own
formatting to the output:
create table test
(
supp_creation_date datetime
)
insert into test
select getdate()
go
select CONVERT(VARCHAR(8),supp_creation_date , 112 )
from test
returns:
20050502
However, if this value gets put back into a datetime variable before the
client recieves it, or the client puts it inot a date container, the
formatting goes away. Formatting only works on textual values, not date
values.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance

Form that creates a temporal table

Hey,
I have a kind-of-newbie question.
I need to create a form that will eventually produce a report in html /
asp.
while in access that would not be a problem, I'm learning my ways
through this project in the asp world.
So, I created an access database that should hold the information
entered by the form and then the report will be just retieval of
information from it.
The thing is that the form is built in the way that you select a few
options and then click "submit" and that's one line in the database,
after you click "produce" it should take all that information you
loaded into the database and make it a report.
How will the report's query look like? how will it know what
information was enterd by that specific user at that time? is there a
way to make a temporal table that starts with the first "submit" and
ends when you click "produce" ?
Thanks,
Tomer.The answer is: primary key.
The procedure that inserts the data should return the primary key of the
newly inserted row after the insert, then you can use the primary key to
retrieve the row(s) that you want to display in the report.
In SQL 2005 this is even simpler - use the OUTPUT statement.
ML