Date in subject line of email subscription

Hi,
is there any way to automatically put the date of previous day in
subject line of email subscription? Can anybody tell me how to do it?The only way to do this would be with Data Driven subscriptions. First you
have to have Enterprise edition or Data Driven subscriptions are not
present. If you do have the Enterprise edition then you can generate the
subject from a SQL query.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"mentor" <daniel.pater@.pl.ibm.com> wrote in message
news:1126166552.090643.170840@.f14g2000cwb.googlegroups.com...
> Hi,
> is there any way to automatically put the date of previous day in
> subject line of email subscription? Can anybody tell me how to do it?
>|||Thanks a lot
i resolved this problem in different way. I save the reports to shared
folder and send them using vbs file :)

Date functions

Hi all,

Are there a functions for last day of the month or first day of the month?

Thanks in advance,

Jen

--
Fast Track On Line -Web Design and Development
Portfolio http://www.fasttrackonline.co.uk

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004Hi, Jenny!
You wrote on Fri, 21 May 2004 12:58:00 +0100:
J> Are there a functions for last day of the month
dateadd(month,1,dateadd(day,-day(getdate())+1,getdate()))
J> or first day of the month?
dateadd(day,-day(getdate())+1,getdate())

J> Thanks in advance,
NE ZA CHTO, ZAKHODITE ESCHYO!

J> Jen

-
exexe!|||"Jenny" <jennyysplace@.eidosnet.co.uk> wrote in message news:40adef3e@.212.67.96.135...
> Hi all,
> Are there a functions for last day of the month or first day of the month?
> Thanks in advance,
> Jen
>
> --
> Fast Track On Line -Web Design and Development
> Portfolio http://www.fasttrackonline.co.uk
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

Last day of month:

SELECT DATEADD(MONTH, 1, CURRENT_TIMESTAMP) -
DAY(DATEADD(MONTH, 1, CURRENT_TIMESTAMP))

First day of month:

SELECT CURRENT_TIMESTAMP - DAY(CURRENT_TIMESTAMP) + 1

--
JAG

Date formatting problems

Hi,

I have around 1000 records each with two dates in a database in MSDE onmy PC. I need to move the data to an on line SQL server and havetried to use Microsoft Web Data Administrator to do this. I canexport the data from the MSDE to an SQL file but it will not importbecause the date format in the SQL file is "dd,mm,yyyy".

If I export from either the MSDE or the SQL server both produce fileswith date in the format "dd,mm,yy" and yet I cannot import either!?!

It is impractical to change all the dates by hand. I am on abudget and do not have access to anything other than free software.

Can anyone advise me of the best way forward.

Thanks in anticipation.

MikeIf you have compatible versions of MSDE and SQL Server, you can detatch the msde database and reattach it to sql server without going through the export/import|||Hi

I am not sure if they are "compatable". The sql server is on myweb servers computer and I know little about it. I don't know howto attach or reattach tables but I will check on sql server 2000 bookson line.

Thanks

Mike|||

Mike,

Maybe you could import the data to a varchar column (instead of adatetime column), and write a small query to fix the formatting.

|||Hi

I have checked Books On Line and attaching a database does not sound tobe an option. I do not have access to the files of the SQLserver, but can access it through Web Data Administrator orADO.NET. Also, I want to add data to a single table withoutdisturbing the rest of my database.
Any one got any other ideas?

Mike|||Hi Geojan

That sounds an interesting idea. Not sure exactly how to do itbut I would have thought it should work OK. I could even writesome VB.NET code to do it for me (I am probably better with VB thanqueries). I am not usually quick at this sort of thing but willlet you know in the next day or two how I get on.

Thanks

Mike|||It pretty simple actually, say you have a Table (TblA), that's where you import your data, and the data column is defined as varchar(10) (named vc_Date).

And you have secound table (TblB), where the data column is defined as datetime (named dt_Date).

The sql statement needed to transfer the data from TblA to TblB, would be (Just for the example I added ColB, ColC and ColD):

Insert TabB (dt_Date, ColB, ColC, ColD)
Select Convert(datetime, Substring(Vc_Date, 7, 4) + Substring(Vc_Date, 4, 2) + Substring(Vc_Date, 1, 2))
,ColB, ColC, ColD
From TblA

If you like you can execute the statement form vb, or simply run it in query analyzer.

|||Hi Geojan

I ran the sql statement as an ExecuteNonQuery command and it worksgreat. My dates have been inserted correctly into the appropriatecolumns. Many thanks for the advice.

Mike|||

Thanks for the feedback Mike, nice to know it helped!

Date Diff

I need to return the number of min from a table I am using the following query. But it gives me an error "Msg 241, Level 16, State 1, Line 1
Syntax error converting date time from character string". can someone please help.

SELECT DateDiff(Mi, CAST((SCHDATE + ' ' + SUBSTRING(SCHTIME, 1,2) + ':' + SUBSTRING(SCHTIME, 3,4)) AS DateTime),
CAST((ACTDATE + ' ' + SUBSTRING(ACTIME, 1,2) + ':' + SUBSTRING(ACTIME, 3,4)) AS DateTime))
AS StopMinutes,
BACPY, BARTRM, BAORD, BSAPOR, BABLN, BSASSQ, BSACNO, CSTRDATA,
BSASCY, BSASST, TTLREV, SHAALP, SCHDATE, SCHTIME, ACTDATE, ACTIME,
OQTCOD, BAADES, PCS, WGT, Tractor, Driver
FROM dbo.JCI_Delivery_Report

Make sure you are constructing the datetime string correctly. You have to make sure the string you are constructing is a string SQL Server understands as a datetime; otherwise you're going to get a casting error.

German Afanador

|||Can you explain what you are trying to do?|||

SUBSTRING(SCHTIME, 3,4) should be SUBSTRING(SCHTIME, 3,2)

SUBSTRING(ACTTIME, 3,4) should be SUBSTRING(ACTTIME, 3,2)

and while I think it's bad that you've created text fields in your database for dates and times instead of storing them as they should be (in datetime format), this will get you closer to what you want.

Date conversion error on 2005

Getting following error on 2005 on a query that works fine on 2000:
---
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
---
Here is the query: T_DATE column datatype is varchar(30) and the table does
have some rows with non-date data (zero) outside of the where clause.
---
SELECT col_names
FROM tablename
WHERE
CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
---
Query works fine after I run the following update:
----
update tablename set T_DATE = null where isdate(T_DATE) = 0
---
Is there any way we could make it work as-is, the way it was running in 2000
without any changes."Amit" <amitjn_ca@.yahoo.ca> wrote in message
news:O$OrWfIGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Getting following error on 2005 on a query that works fine on 2000:
> ---
> Msg 241, Level 16, State 1, Line 1
> Conversion failed when converting datetime from character string.
> ---
> Here is the query: T_DATE column datatype is varchar(30) and the table
> does have some rows with non-date data (zero) outside of the where clause.
> ---
> SELECT col_names
> FROM tablename
> WHERE
> CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
> ---
> Query works fine after I run the following update:
> ----
> update tablename set T_DATE = null where isdate(T_DATE) = 0
> ---
> Is there any way we could make it work as-is, the way it was running in
> 2000 without any changes.
>
If this works in 2000 and not in 2005 then check that the LANGUAGE and
DATEFORMAT settings are the same in each case. On my system I get the
"conversion failed" or "syntax error" in both versions when trying to
convert the string '0'.
CASE should prove more reliable. See the following example and notice that
I've specified a value for the style parameter of the CONVERT function - do
the same if you can and use LIKE to find valid dates rather than rely on the
implicit conversions that ISDATE uses.
CREATE TABLE tablename (t_date VARCHAR(30));
INSERT INTO tablename VALUES ('0');
SELECT t_date
FROM
(SELECT CASE WHEN ISDATE(t_date)=1 THEN t_date END AS t_date
FROM tablename) AS T
WHERE CONVERT(DATETIME, t_date,1) < DATEADD(d,7, GETDATE());
Result:
(1 row(s) affected)
t_date
--
(0 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Date conversion error on 2005

Getting following error on 2005 on a query that works fine on 2000:
---
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
---
Here is the query: T_DATE column datatype is varchar(30) and the table does
have some rows with non-date data (zero) outside of the where clause.
---
SELECT col_names
FROM tablename
WHERE
CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
---
Query works fine after I run the following update:
----
update tablename set T_DATE = null where isdate(T_DATE) = 0
---
Is there any way we could make it work as-is, the way it was running in 2000
without any changes."Amit" <amitjn_ca@.yahoo.ca> wrote in message
news:O$OrWfIGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Getting following error on 2005 on a query that works fine on 2000:
> ---
> Msg 241, Level 16, State 1, Line 1
> Conversion failed when converting datetime from character string.
> ---
> Here is the query: T_DATE column datatype is varchar(30) and the table
> does have some rows with non-date data (zero) outside of the where clause.
> ---
> SELECT col_names
> FROM tablename
> WHERE
> CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
> ---
> Query works fine after I run the following update:
> ----
> update tablename set T_DATE = null where isdate(T_DATE) = 0
> ---
> Is there any way we could make it work as-is, the way it was running in
> 2000 without any changes.
>
If this works in 2000 and not in 2005 then check that the LANGUAGE and
DATEFORMAT settings are the same in each case. On my system I get the
"conversion failed" or "syntax error" in both versions when trying to
convert the string '0'.
CASE should prove more reliable. See the following example and notice that
I've specified a value for the style parameter of the CONVERT function - do
the same if you can and use LIKE to find valid dates rather than rely on the
implicit conversions that ISDATE uses.
CREATE TABLE tablename (t_date VARCHAR(30));
INSERT INTO tablename VALUES ('0');
SELECT t_date
FROM
(SELECT CASE WHEN ISDATE(t_date)=1 THEN t_date END AS t_date
FROM tablename) AS T
WHERE CONVERT(DATETIME, t_date,1) < DATEADD(d,7, GETDATE());
Result:
(1 row(s) affected)
t_date
--
(0 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Date conversion error on 2005

Getting following error on 2005 on a query that works fine on 2000:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
Here is the query: T_DATE column datatype is varchar(30) and the table does
have some rows with non-date data (zero) outside of the where clause.
SELECT col_names
FROM tablename
WHERE
CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
Query works fine after I run the following update:
update tablename set T_DATE = null where isdate(T_DATE) = 0
Is there any way we could make it work as-is, the way it was running in 2000
without any changes.
"Amit" <amitjn_ca@.yahoo.ca> wrote in message
news:O$OrWfIGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Getting following error on 2005 on a query that works fine on 2000:
> Msg 241, Level 16, State 1, Line 1
> Conversion failed when converting datetime from character string.
> Here is the query: T_DATE column datatype is varchar(30) and the table
> does have some rows with non-date data (zero) outside of the where clause.
> SELECT col_names
> FROM tablename
> WHERE
> CONVERT(DATETIME, T_DATE) < dateadd(d,7, getdate())
> Query works fine after I run the following update:
> ----
> update tablename set T_DATE = null where isdate(T_DATE) = 0
> Is there any way we could make it work as-is, the way it was running in
> 2000 without any changes.
>
If this works in 2000 and not in 2005 then check that the LANGUAGE and
DATEFORMAT settings are the same in each case. On my system I get the
"conversion failed" or "syntax error" in both versions when trying to
convert the string '0'.
CASE should prove more reliable. See the following example and notice that
I've specified a value for the style parameter of the CONVERT function - do
the same if you can and use LIKE to find valid dates rather than rely on the
implicit conversions that ISDATE uses.
CREATE TABLE tablename (t_date VARCHAR(30));
INSERT INTO tablename VALUES ('0');
SELECT t_date
FROM
(SELECT CASE WHEN ISDATE(t_date)=1 THEN t_date END AS t_date
FROM tablename) AS T
WHERE CONVERT(DATETIME, t_date,1) < DATEADD(d,7, GETDATE());
Result:
(1 row(s) affected)
t_date
(0 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Date Breakdown Question

Can anyone please explain how this line of SQL will give me the first day of the week of the date passed in. With 7 being the parameter at the back end dateadd parameter that will return sunday as the first day of the week but I don't get the datediff portion.

Select dateadd(wk, datediff(wk, 6, '04/09/2007'), 7)

I will separate the operation into two separate components.

First, this portion calculates the number of full weeks between day 6 of the first week and today. (For 2007/04/30, that is 5599.) It is necessary to remember that day 0 is actually the first day, day 1 is the second day, etc.

SELECT datediff(wk, 6, getdate())

--
5599

Then that value is used to find the first date of the week if you added 5599 weeks to Day 6 of the first week.

SELECT dateadd(wk, 5599, 6)

2007-04-29 00:00:00.000

|||

Thanks for replying

I thought that is what was happening but the datediff gave me a hard time.

Thanks again