Adding two dates

I have two fields, Date Purchased (smalldatetime) and warranty in months (integer).

can anyone help me to formulate a query to show the date when the warranty ends, and/or the remaining days/months in the warranty please?

I'm not sure if its doable, but i would really appreciate it if anyone can help me!

Give a look to the DATEADD function in books online; it looks like this:

declare @.example table
( rid integer,
datePurchased smalldatetime,
warranty integer
)

insert into @.example
select 1, '4/8/7', 3 union all
select 2, '5/14/7', 12

select rid,
convert(varchar(10), datePurchased, 101) as datePurchased,
warranty,
dateadd (mm, warranty, datePurchased) as warrantyEndDate,
datediff (day, getdate(), dateadd (mm, warranty, datePurchased)) as daysRemaining,
datediff (mm, getdate(), dateadd (mm, warranty, datePurchased)) as monthsRemaining
from @.example

/*
rid datePurchased warranty warrantyEndDate daysRemaining monthsRemaining
-- - -- - -
1 04/08/2007 3 2007-07-08 00:00:00 45 2
2 05/14/2007 12 2008-05-14 00:00:00 356 12
*/

Adding times together

Hi guys,

I have a field in my DB called EventDate as a DateTime field,
therefore it holds both the date and time together like this:
'2004-10-14 08:42:57.000'.

I need to add together all the times in this column for a particular
date range (BETWEEN).

Any suggestions will be great.

Thanks
Sunny:)Sunny K (sunstarwu@.yahoo.com) writes:
> I have a field in my DB called EventDate as a DateTime field,
> therefore it holds both the date and time together like this:
> '2004-10-14 08:42:57.000'.
> I need to add together all the times in this column for a particular
> date range (BETWEEN).

If I take you by the word, it sounds like the answer is:

SELECT SUM(datefiff(ss, convert(char(8), EventDate, 112), EventDate)
FROM tbl
WHERE EventDate BETWEEN ... AND ...

But it looks a little funny.

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You certainly can use BETWEEN with the DATETIME datatype but if you are
querying values with times other than midnight it's often more convenient to
use use >= and < instead of BETWEEN. For example

This:

SELECT *
FROM YourTable
WHERE eventdate >= '20041014'
AND eventdate < '20041015'

Is equivalent to this:

SELECT *
FROM YourTable
WHERE eventdate
BETWEEN '2004-10-14T00:00:00.000'
AND '2004-10-14T23:59:59.997'

Hope that answers your question.

--
David Portas
SQL Server MVP
--|||> I need to add together all the times in this column

I missed that bit from my first post - maybe because I've no idea what it
means! Just what would you expect to be the result of, for example
'2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
you want to add up a DATETIME?

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||Sunny K (sunstarwu@.yahoo.com) writes:
> Thats for the reply, and admitly i was very vague in what i meant to
> say. From your above example the time result of the two times would
> give me 08:42:57.000, as the time added was 00:00:00.000.
> Maybe this will help explain what i mean a bit better. Here is a few
> typical lines from my table:
> Name EventDate EventID
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>
> My 'exact' requirements are to SUM all the 6005 EventID times together
> and SUM all the 6006 EventID times together then find the difference
> between the two times. The dates in the column are of no use.
> Ive been banging my head over how to do this for a few days now. Any
> suggestions?

I repeat from my previous post:

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

In this case, the part with the desired result is very important,
because I am not sure what result you are looking for, and I don't
feel like guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
> Sunny K (sunstarwu@.yahoo.com) writes:
> > Thats for the reply, and admitly i was very vague in what i meant to
> > say. From your above example the time result of the two times would
> > give me 08:42:57.000, as the time added was 00:00:00.000.
> > Maybe this will help explain what i mean a bit better. Here is a few
> > typical lines from my table:
> > Name EventDate EventID
> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
> > My 'exact' requirements are to SUM all the 6005 EventID times together
> > and SUM all the 6006 EventID times together then find the difference
> > between the two times. The dates in the column are of no use.
> > Ive been banging my head over how to do this for a few days now. Any
> > suggestions?
> I repeat from my previous post:
> A common advice for this type of query is that you post
> o CREATE TABLE statement for your table.
> o INSERT statements with sample data.
> o The desired result, given the sample data.
> This make it easy to cut and paste and compose a tested solution.
> In this case, the part with the desired result is very important,
> because I am not sure what result you are looking for, and I don't
> feel like guessing.

Hi,

Here is the script to create the table with some sample data:

CREATE TABLE [dbo].[tbltemp23] (
[Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[EventDate] [datetime] NOT NULL ,
[EventID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)

Now I need to work out the the total time of all the 6006 EventIDs
(the date is here is not needed) which should equal: 93:32:16 then the
the total time of all the 6005 EventIDs which equals: 93:42:44. Then
finally find the difference between the two times, which should equal:
00:10:28 in this case.

I hope this is enough information.

Thanks
Sunny|||Thanks for the DDL and data.

SQL Server doesn't have a timespan data type. The query below uses
1900-01-01 as the base date from which durations are calculated, ignoring
the date component of the table data. You can format the returned values
according to your reporting requirements.

SELECT
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006) AS EventId6006Duration,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005) AS EventId6005Duration,
DATEADD(s,
DATEDIFF(s,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006),
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005)),
'19000101'
) AS EventDurationDifference

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sunny K" <sunstarwu@.yahoo.com> wrote in message
news:1ecdad8f.0411080132.4d6627fe@.posting.google.c om...
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
> news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
>> Sunny K (sunstarwu@.yahoo.com) writes:
>> > Thats for the reply, and admitly i was very vague in what i meant to
>> > say. From your above example the time result of the two times would
>> > give me 08:42:57.000, as the time added was 00:00:00.000.
>>> > Maybe this will help explain what i mean a bit better. Here is a few
>> > typical lines from my table:
>>> > Name EventDate EventID
>> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
>> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
>> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
>> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>>>> > My 'exact' requirements are to SUM all the 6005 EventID times together
>> > and SUM all the 6006 EventID times together then find the difference
>> > between the two times. The dates in the column are of no use.
>>> > Ive been banging my head over how to do this for a few days now. Any
>> > suggestions?
>>
>> I repeat from my previous post:
>>
>> A common advice for this type of query is that you post
>>
>> o CREATE TABLE statement for your table.
>> o INSERT statements with sample data.
>> o The desired result, given the sample data.
>>
>> This make it easy to cut and paste and compose a tested solution.
>>
>> In this case, the part with the desired result is very important,
>> because I am not sure what result you are looking for, and I don't
>> feel like guessing.
>
> Hi,
> Here is the script to create the table with some sample data:
>
> CREATE TABLE [dbo].[tbltemp23] (
> [Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [EventDate] [datetime] NOT NULL ,
> [EventID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.
> I hope this is enough information.
> Thanks
> Sunny|||Sunny K (sunstarwu@.yahoo.com) writes:
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.

To be honest, this still seem very strange to me. Sure, there is enough
information to write a solution, but somehow I wonder what is the real
problem.

Looking at your data, it seems that event 6006 means start and 6005
means end, and what you really are computing is the total duration of
all start-stop sequences. Given that, I wrote this query:

SELECT convert(char(8), dateadd(ss, SUM(diff), '19000101'), 108)
FROM (select diff = datediff(ss, a.EventDate,
(SELECT MIN(EventDate)
FROM tbltemp23 b
WHERE b.EventDate > a.EventDate
AND b.EventID = 6005))
FROM tbltemp23 a
WHERE a.EventID = 6006) AS c

Of course, this query breaks down if the 6006 and 6005 can come in
any order, but in that case I have no clue of what might be going on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What can I say you guys, you really know your stuff. Thanks for all
the help, its given me the exact results I've needed.

I will consider you guys when I face another problem:-P

Sunny

Adding the Date to Filename of DTS Export

I'd like to add the date to the file name of a DTS Export.
For Example:
Export092503.xls
I've tried various methods but nothing has worked as of yet.
Any ideas?
Thanks in Advance.
TechRickI export a standard file name, then rename using a BAT file. Here's the BAT file I use...

-------------------
@.echo off

c:
cd \SQL_Support_Applications\FTP Scripts\SONIC_Data

IF EXIST CUST_%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%.dat DEL CUST_%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%.dat

IF EXIST CUST_raw.dat REN CUST_raw.dat CUST_%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%.dat

EXIT
-------------------

It checks to see if the dated file exists and deletes before renaming.|||Hi TechRick!

If I get you correct, you run an data export task in a dts package.

In this case you can use an ActiveXTask in this package to get the system date and modify the properties of the export task. A similar example is shown here: http://www.sqldts.com/default.aspx?231

Hope this helps you!

Greetings,
Carstensql

Adding the date to a subscription report name

Does anyone have information on how I can add a file date to my named report when a subscription writes that file to a network folder? I am exporting as .pdf files. For example, today I have filename abc.pdf but I'd like it to be abc03312006.pdf. Likewise, tomorrow's file would be abc04012006.pdf.

Any help is greatly appreciated.

You have to go for reporting services programming and create a subscription programatically and pass the date as a value to subscription's description parameter like this.

service.CreateSubscription(reportpath, extSettings, description, eventType, matchData, parameters );

|||

Can you please give more details on it? Where to go and how to do this?

Thanks,

-Rohit

Adding the date to a subscription report name

Does anyone have information on how I can add a file date to my named report when a subscription writes that file to a network folder? I am exporting as .pdf files. For example, today I have filename abc.pdf but I'd like it to be abc03312006.pdf. Likewise, tomorrow's file would be abc04012006.pdf.

Any help is greatly appreciated.

You have to go for reporting services programming and create a subscription programatically and pass the date as a value to subscription's description parameter like this.

service.CreateSubscription(reportpath, extSettings, description, eventType, matchData, parameters );

|||

Can you please give more details on it? Where to go and how to do this?

Thanks,

-Rohit

Adding subreport to report header?

Is it possible? Crystal allowed you to do it but RS doesn't seem to. I have
a number of reports that need to display same data (Org name, date etc.)
on the report header and I thought doing it as a subreport was a good idea.
Any workarounds?
Thanks in advance.Found report templates meet my needs better. Thanks anyway!
> Is it possible? Crystal allowed you to do it but RS doesn't seem to. I
> have a number of reports that need to display same data (Org name,
> date etc.) on the report header and I thought doing it as a subreport
> was a good idea.
> Any workarounds?
> Thanks in advance.
>sql

Adding Records in SQL Server Express 2005

When adding records containing a date field in SQL Server 2005 Express I get an errorInput string was not in a correct format. Do datefields have to be converted when adding or editing into strings?

I usually insert dates into SQL in String format. SQL can convert them by himself. BUT at least in our environment you have to create the date string in "MM.DD.YYYY" format so if you are trying "DD.MM.YYYY" It propably wont work though I know this could be solved with localization somewhere, somehow :)
|||

I do have it in string format. Any Ideas? My code excerpt

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:Web2005ConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:Web2005ConnectionString1.ProviderName %>"

SelectCommand="SELECT * FROM [People] WHERE ([id] = @.id)"

InsertCommand="INSERT INTO [People] ([LastName], [FirstName], [Price], [LogDate]) VALUES (@.LastName, @.FirstName, @.Price, @.LogDate)"UpdateCommand="UPDATE [People] SET [LastName] = @.LastName, [FirstName] = @.FirstName, [Price] = @.Price, [LogDate] = @.LogDate WHERE [id] = @.id"DeleteCommand="DELETE FROM [People] WHERE [id] = @.id">

<SelectParameters>

<asp:ControlParameterControlID="GridView1"Name="id"PropertyName="SelectedValue"

Type="Int64"/>

</SelectParameters>

<UpdateParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

<asp:ParameterName="id"Type="Int64"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

</InsertParameters>

Adding Hours, Minutes, Seconds (SQL 2000)

Hi There,
I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.
For instance, "01:50:10" + "01:20:5" = "3:10:15"
Any ideas?
Using SQL 2000try this tricky thing...

declare @.Dt as datetime
set @.Dt = '2007-02-20'
declare @.Dt1 as datetime
set @.Dt1 = '2007-02-20 01:50:10'
declare @.Dt2 as datetime
set @.Dt2 = '2007-02-20 01:20:05'

select convert(varchar,cast((cast(@.Dt1 as float) - cast(@.Dt as float)) + (cast(@.Dt2 as float) - cast(@.Dt as float)) as datetime),114)

now dont ask me what will happen if the sum is more than 24 hrs etc. etc... ;)|||select sum(datediff(s, '2000-01-01', '2000-01-01 ' + [TimeString]))
from [YourTable]
You'll need to verify that the above function syntax is correct, but you should get the general idea.|||declare @.tm1 datetime, @.tm2 datetime
select @.tm1='23:50:10', @.tm2='23:20:05'
select 'sum1'=
str((datediff(s,0,@.tm1)+datediff(s,0,@.tm2))/60/60,4,0)
+right(convert(char(8),dateadd(s,datediff(s,0,@.tm2 ),@.tm1),108),6)

sum1
----
47:10:15

Thanks upalsen, I didn't know it was that ease to convert between gregorian date and julian day number.
select 'JulianDayNo'=convert(float,getdate())+2415020.5

JulianDayNo
-------
2454154.9927028548|||I really don't think the formula needs to be that complicated...
set nocount on
declare @.TimeStrings table (TimeString varchar(8))

insert into @.TimeStrings (TimeString) values ('01:50:10')
insert into @.TimeStrings (TimeString) values ('01:20:5')

select sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)) as TotalSeconds,
convert(varchar(8), dateadd(s, sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)), 0), 8) as DateString
from @.TimeStrings|||UPalsen's way works - thanx

Adding Default Dates within Reporting Services Admin

Is there any way, within the admin to specify that
yesterday and today for start date and end date. Someone
suggested =Today.AddDays(-1) but I don't think they meant
in the admin. Could you tell me where i am supposed to
apply this default.
Regards,
BryanIf by admin you mean through the report manager, then there is no way. The
report manager does not support entering expressions.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:9f5001c4791a$bf4e8040$a301280a@.phx.gbl...
> Is there any way, within the admin to specify that
> yesterday and today for start date and end date. Someone
> suggested =Today.AddDays(-1) but I don't think they meant
> in the admin. Could you tell me where i am supposed to
> apply this default.
> Regards,
> Bryan

Adding days and setting time

Hi,
Can someone please help me with a SQL Server 2005 issue with date and
time.
I want to take the current date and time and add 2 days to it, but the
time must be set to 5pm.
If the current date/time is past 5pm it will go to the next day at 5pm.
So if the date was:
2006-06-23 15:55:46.337 then the new date should say 2006-06-25
17:00:46.337
If the current time was past 5pm then it should be as follows:
2006-06-23 19:55:46.337 then the new date should say 2006-06-26
17:00:46.337
Notice the day is an extra day because 5pm has already gone by hence it
has to go to the next 5pm, which is the next day.
Thanks.
SimonTry this:
SELECT DATEADD(DAY, (CASE
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
ELSE 3
END), DATEADD(HOUR, 17, CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS
DATETIME)));
HTH
Vern Rabe
"simon_s_li@.hotmail.com" wrote:

> Hi,
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
> If the current date/time is past 5pm it will go to the next day at 5pm.
> So if the date was:
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
> If the current time was past 5pm then it should be as follows:
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
> Thanks.
> Simon
>|||Something like:
DECLARE @.Date1 DATETIME;
SELECT @.Date1 = GETDATE();
PRINT @.Date1;
SET @.Date1 = CASE WHEN DATEPART(HH, @.date1) > 17 THEN DATEADD(DAY, 1,
@.Date1) ELSE @.Date1 END;
SET @.Date1 = DATEADD(HH, (17-DATEPART(HH, @.Date1)), @.Date1);
PRINT @.Date1;|||I just realized that you apparently want to retain the current seconds and
milliseconds, to be added to 5:00 PM. Seems strange, but to do that, this
should work:
SELECT DATEADD(DAY, (CASE
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
ELSE 3
END), DATEADD(HOUR, 17, CAST(CONVERT(varchar(10), CURRENT_TIMESTAMP, 110) +
' 00:00' + RIGHT(CONVERT(varchar(24), CURRENT_TIMESTAMP, 13), 7) AS
DATETIME)));
HTH
Vern Rabe
"simon_s_li@.hotmail.com" wrote:

> Hi,
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
> If the current date/time is past 5pm it will go to the next day at 5pm.
> So if the date was:
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
> If the current time was past 5pm then it should be as follows:
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
> Thanks.
> Simon
>

Adding dates in SQL

I am trying to pull only records that are greater than 1 month prior to today's date. This is what I have so far...

select *
from MyTable
where eventDateStart > '$Now'
order by eventDateStart

$Now is a variable that pulls in today's date. This sql statement delivers only records that have a eventDateStart greater than today. My problem is I do not know how to make it so it only shows records that are 1 month prior.

Any idea how to do this?Have a look at the DATEDIFF function in SQL Server|||This should do it for you:

SELECT * FROM MyTable
WHERE eventDateStart < DATEADD(m, -1, GETDATE())

Cheers

Gary|||Ok, this is frustrating, I can not get the DATEADD function to work for some reason. This is exactly what I want..

select *
from MyTable
where eventDateStart > DATEADD (m,-1,'2004/12/26')

This "SHOULD" return all the events that have a start date greater than November 26,2004 right? Am I crazy or something? If I get rid of the dateadd function and just have this where clause...

where eventDateStart > '2004/12/26'

it returns exactly what it should...all events that have a start date later than the 12/26/2004. But as soon as I try to DATEADD it returns zero rows. What the heck am I doing wrong|||Hi again,

Sorry I screwed up the first time as I should have said:
eventDateStart > DATEADD(m, -1, GETDATE())
instead of
eventDaytStart < DATEADD(m, -1, GETDATE())

In any case I checked your hardcoded version (SELECT DATEADD (m,-1,'2004/12/26') )and my version (SELECT DATEADD(m, -1, GETDATE())) in query analyzer and both give me the expected results.

I don't know if this is typo on your part but your DATEADD version will produce a comparison date of 2004/11/26 not 2004/12/26, so you are likely to get different results. Try it in query analyzer making sure you are using a dateadd that will be the same date as your hardcoded value and see what happens.

If that failds post the entire query/procedure and I'll look at it again because you're right, there is no reason why this should be difficult.

Cheers

Gary

Adding dates giving blank or incorrect date

My report has a finishdate column which is grouped into week intervals, Im trying to run the following formula to get the Saturday(end of the week) based on the finishdate e.g if the finish date is 4/7/2006 which is a Tuesday, I want to find out what the end off week date is - which would be 8/7/2006 (Saturday) and place this into another column.

NumberVar whatday;

whatday = dayofweek({finishdate});
If whatday = 2 then //monday
{finishdate} + 5;
Else whatday = 3 then
{finishdate} + 4;
Else whatday = 4 then
{finishdate} + 3;
Else whatday = 5 then
{finishdate} + 2;
Else whatday = 6 then
{finishdate} + 1;

This is not returning any errors but also it is giving me just a blank output. Also when i run the lines
{finishdate} + 4;
on its own, the dates are adding up correctly

OR

dayofweek({finishdate}) onit owns it returns the correct day off week for the finishdate

when i put variables
NumberVar whatday;

whatday = dayofweek({finishdate});
If whatday = 2 then //monday
{finishdate} + 5;

i get a day returned off 1/1/-4713 or an incorrect dayofweek
I have no idea why this is returned.

I am using Crystal Report 7 and the dateadd() is not available, I dont know why when run with conditions etc such as if and variable declarations, it doesnt return the right date, but when run on its own it works.

Can someone help,

thanksThe common mistake people do with Crystal, is when they forget there are two different syntaxes: Crystal and VB. In your case, I think the problem is in assignment operator. Try whatday := dayofweek({finishdate}); instead of whatday = dayofweek({finishdate}); The latter would return the boolean value as a result of comparision, not assignment.

Adding date to filename in report subscription

My company sends reports on a daily basis to our customers. Now I want to save all the sent reports on disc with the date in the filename. I have set up a subscription which daily saves the files where I want them. However, I haven't found a way to add the date easily. I already have a parameter when creating the report, it is called Date. Does anybody know if I can use a parameter or something else?

Thank's

Hello,

Sorry, I don't believe there is a way to modify the filename from a subscription, but you can specify a filename from a Data-Driven Subscription. Do a data-driven subscription to a file share, and just include an extra column in your subscription query to have something like this:

select 'Report or file name here ' + convert(varchar, getdate(), 101) as FileName, ...

Then, when you are setting the delivery extension settings, use this field as your File name.

Hope this helps.

Jarret

adding date timestamp to xp_sendmail procedure

I am trying to figure out how to add a time datestamp to my xp_sendmail procedure:

use master;
go

CREATE PROC pr_sendmail
AS

DECLARE @.DT DATETIME
SET @.DT=GETDATE()

BEGIN

EXEC xp_sendmail @.recipients = 'me@.work.com',
@.message = 'send email from SQL Server Stored Procedure.',
@.copy_recipients = 'me@.work.com',
@.subject = 'Job Started at ', @.DT

END

How do I get this to work? Thanks!@.subject = 'Job Started at ' + cast(getdate() as varchar)

you can also use CONVERT instead of CAST to format the date in various different formats.

Adding Date and zero values to non existent dates

Hi,
I have info about my customers and when they place their orders. I am trying
to get a report that will tell me the sum of their orders for each month fo
r
the last 24 months. The problem I'm having is that certain customers don't
have order in every month so I'm only able to query on what's there.
How can I create a table or a view that would return every months in the
last 24 months with the sum of their orders for each month and 0 for months
that had no orders?
Thanks in advance.Read this for some ideas:
http://www.aspfaq.com/show.asp?id=2519
"Frenchie418" <Frenchie418@.discussions.microsoft.com> wrote in message
news:F57924D5-DF9C-46EE-A4B6-B8CFBBBB4026@.microsoft.com...
> Hi,
> I have info about my customers and when they place their orders. I am
> trying
> to get a report that will tell me the sum of their orders for each month
> for
> the last 24 months. The problem I'm having is that certain customers don't
> have order in every month so I'm only able to query on what's there.
> How can I create a table or a view that would return every months in the
> last 24 months with the sum of their orders for each month and 0 for
> months
> that had no orders?
> Thanks in advance.|||Thanks, I think this will help... Merci Beaucoup!
"Raymond D'Anjou" wrote:

> Read this for some ideas:
> http://www.aspfaq.com/show.asp?id=2519
> "Frenchie418" <Frenchie418@.discussions.microsoft.com> wrote in message
> news:F57924D5-DF9C-46EE-A4B6-B8CFBBBB4026@.microsoft.com...
>
>

Adding current date into attachment filename in reporting service

Hi there,

I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.

My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.

My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.

Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005

Regards,

Hello...

I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?

Thanks.

|||

You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1

Hope this helps.

Jarret

Adding current date into attachment filename in reporting service

Hi there,

I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.

My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.

My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.

Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005

Regards,

Hello...

I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?

Thanks.

|||

You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1

Hope this helps.

Jarret

Adding columns at runtime

I have a procedure that will return a dataset with an unknown number of columns (the user chooses a date range, and there will be one column per day). Since the columns are not always the same, the report designer doesn't want to help me with this. How can I make this work?

Thanks

Hello my friend,

For performance and ease-of-use reasons, I strongly recommend you take a different approach than using columns in this way, especially for reporting services. Please give details on what you are trying to do (the table structure and the query, etc) and I will try to suggest an alternative to achieving the same result.

Kind regards

Scotty

|||

Currently, I have this table:

CriticalUnitHistory
(
CritcalUnitHistory int (PK),
MarketID int,
UnitLCN int,
CriticalDate datetime,
CriticalReason varchar(50)
)

Every day, I look through a list of computers (each with a UnitLCN that is unique to its city) in different cities (MarketID corresponds to each city), and if its current status satisfies certain criteria, I add a record to this table with the MarketID, UnitLCN, current date and a short description of the criteria that it met to be included on the critical list.

I have been asked to create a report that will take a list of UnitLCNs and MarketIDs, and a date range, and show a table with the UnitLCNs down the left side, the dates across the top, and, if the computer was critical on a certain day, show the CriticalReason in the corresponding cell.

It would look something like this:

MarketID UnitLCN 1/20/2007 1/21/2007 1/22/2007 1/23/2007
1 519 No Contact No Contact
1 234 DL Error DL Error
1 219 GPS Fail GPS Fail

Hope that helps. Thanks for your assistance

|||

Hello my friend,

I take it you are having problems generating the data in this way from the original query. Refer to the following url: -

http://www.sqlteam.com/item.asp?ItemID=2955

It is really good. It shows you how to do a cross tab pivot to make data come out in this way. I tested the code myself with my own database tables and it works.

Kind regards

Scotty

Adding a time column to a date column

I have two columns in a table:
StartDate DateTime and StartTime DateTime.
The StartDate column holds a value such as 07/16/2004
The StartTime column holds a value such as 3:00:00 PM

I want to be able to add them in a stored procedure.
When I use StartDate + StartTime I get a date two days earlier than expected.
For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
7/14/2004 3:00:00 PM.

Can anyone point out wht I'm doing wrong with this one?

Thanks,
lqLauren,

It sounds like you are using Enterprise Manager. All datetime columns in SQL Server hold both a date and a time, and if you view the
data in Query Analyzer, you should find that your StartDate column holds something like 2004-07-16 12:00:00AM and your StartTime column
holds a value like 1899-12-30 03:00:00PM.

Tools that allows data input will attach a date when a time only is entered into a SQL Server database column, and unfortunately some
tools will attach 1900-01-01 and others will attach 1899-12-30. SQL Server stores the first of these as its zero date, but it stores the
second as -2 (plus whatever fraction of a date the time portion represents, in each case). Enterprise Manager thinks that 1899-12-30 is the
base date, and both attaches it when a bare time is entered and suppresses it when it appears in a datetime to be displayed.

You are doing nothing wrong, but to be safe, you can calculate the time portion explicitly before you add. One way to do this is

StartDate + (StartTime - datediff(day,0,StartTime))

Storing time-only values in SQL Server is tricky, since there is no appropriate type. You need to be careful, and you might want to
consider alternatives, such as storing only the StartDateTime in the database, in which case you can make StartDate and StartTime computed
columns, or calculate them on the fly when you need them.

Steve Kass
Drew University

Lauren Quantrell wrote:

> I have two columns in a table:
> StartDate DateTime and StartTime DateTime.
> The StartDate column holds a value such as 07/16/2004
> The StartTime column holds a value such as 3:00:00 PM
> I want to be able to add them in a stored procedure.
> When I use StartDate + StartTime I get a date two days earlier than expected.
> For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
> 7/14/2004 3:00:00 PM.
> Can anyone point out wht I'm doing wrong with this one?
> Thanks,
> lq|||Steve,
Thanks a million for that. I was trying all manner of cast, convert
and datepart functions but yours is quick and simple. Thanks!
lq

Steve Kass <skass@.drew.edu> wrote in message news:<8hdLc.8319$mL5.4812@.newsread1.news.pas.earthlink.n et>...
> Lauren,
> It sounds like you are using Enterprise Manager. All datetime columns in SQL Server hold both a date and a time, and if you view the
> data in Query Analyzer, you should find that your StartDate column holds something like 2004-07-16 12:00:00AM and your StartTime column
> holds a value like 1899-12-30 03:00:00PM.
> Tools that allows data input will attach a date when a time only is entered into a SQL Server database column, and unfortunately some
> tools will attach 1900-01-01 and others will attach 1899-12-30. SQL Server stores the first of these as its zero date, but it stores the
> second as -2 (plus whatever fraction of a date the time portion represents, in each case). Enterprise Manager thinks that 1899-12-30 is the
> base date, and both attaches it when a bare time is entered and suppresses it when it appears in a datetime to be displayed.
> You are doing nothing wrong, but to be safe, you can calculate the time portion explicitly before you add. One way to do this is
> StartDate + (StartTime - datediff(day,0,StartTime))
> Storing time-only values in SQL Server is tricky, since there is no appropriate type. You need to be careful, and you might want to
> consider alternatives, such as storing only the StartDateTime in the database, in which case you can make StartDate and StartTime computed
> columns, or calculate them on the fly when you need them.
> Steve Kass
> Drew University
> Lauren Quantrell wrote:
> > I have two columns in a table:
> > StartDate DateTime and StartTime DateTime.
> > The StartDate column holds a value such as 07/16/2004
> > The StartTime column holds a value such as 3:00:00 PM
> > I want to be able to add them in a stored procedure.
> > When I use StartDate + StartTime I get a date two days earlier than expected.
> > For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns
> > 7/14/2004 3:00:00 PM.
> > Can anyone point out wht I'm doing wrong with this one?
> > Thanks,
> > lq