Date range for daily job

Hi!
I have a SQL job that runs a query. That job has to be run daily (or
couple of times per day) The query has to collect information for some
created apps
WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
[Right Now]. How can dynamically program the First Day of Current
Month at 12:00am. Anybody know of any function that can give me this
value no matter at what time I run that job?
Thank you,
T.T.
declare @.date datetime
-- Figure out the first day of the month
select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
-- Trim off the time.
select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
SELECT @.date
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
> Hi!
> I have a SQL job that runs a query. That job has to be run daily (or
> couple of times per day) The query has to collect information for some
> created apps
> WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> [Right Now]. How can dynamically program the First Day of Current
> Month at 12:00am. Anybody know of any function that can give me this
> value no matter at what time I run that job?
> Thank you,
> T.
>|||On Mar 15, 9:41 am, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> T.
> declare @.date datetime
> -- Figure out the first day of the month
> select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
> -- Trim off the time.
> select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
> SELECT @.date
> RLF
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
>
>
>
>
Thanks. It works.sql

Date range for daily job

Hi!
I have a SQL job that runs a query. That job has to be run daily (or
couple of times per day) The query has to collect information for some
created apps
WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
[Right Now]. How can dynamically program the First Day of Current
Month at 12:00am. Anybody know of any function that can give me this
value no matter at what time I run that job?
Thank you,
T.
T.
declare @.date datetime
-- Figure out the first day of the month
select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
-- Trim off the time.
select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
SELECT @.date
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1173964963.027568.47310@.n59g2000hsh.googlegro ups.com...
> Hi!
> I have a SQL job that runs a query. That job has to be run daily (or
> couple of times per day) The query has to collect information for some
> created apps
> WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> [Right Now]. How can dynamically program the First Day of Current
> Month at 12:00am. Anybody know of any function that can give me this
> value no matter at what time I run that job?
> Thank you,
> T.
>
|||On Mar 15, 9:41 am, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> T.
> declare @.date datetime
> -- Figure out the first day of the month
> select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
> -- Trim off the time.
> select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
> SELECT @.date
> RLF
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1173964963.027568.47310@.n59g2000hsh.googlegro ups.com...
>
>
Thanks. It works.

Date Query

Hi
I am trying to write a SQL statement that gives me a date in the
future. The requirements are:-
The date should be Wednesday at 17.30.
The date must be at least 5 full days in advance.
So if it is Monday 1st, return Wednesday 10th 17.30.
If it is Friday 5th 17.29, return Wednesday 10th 17.30.
If it is Friday 5th 17.31, return Wednesday 17th 17.30.
Any help would be appreciated!
Thanks
G
Try this one here (partly tested)
ALTER Function NextWantedDay
(
@.Startdate datetime,
@.EstWeekday INT,
@.EstTime DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @.EstDate DATETIME
IF (DATEPART(dw,@.Startdate) = @.EstWeekday AND
CONVERT(VARCHAR(10),@.Startdate,108) <=
CONVERT(VARCHAR(10),@.EstTime,108))
RETURN
CONVERT(VARCHAR(50),CONVERT(Varchar(10),@.Startdate ,112),113) + @.EstTime
BEGIN
SET @.Startdate = @.Startdate +1
WHILE DATEPART(dw,@.Startdate) <> @.EstWeekday
BEGIN
SET @.Startdate = @.Startdate + 1
END
END
RETURN CONVERT(VARCHAR(50),CONVERT(Varchar(10),@.Startdate ,112),113) +
@.EstTime
END
Select dbo.NextWantedDay(getdate(),1,'09:00')
HTH, Jens Suessmeyer.

Date Query

Hi
I am trying to write a SQL statement that gives me a date in the
future. The requirements are:-
The date should be Wednesday at 17.30.
The date must be at least 5 full days in advance.
So if it is Monday 1st, return Wednesday 10th 17.30.
If it is Friday 5th 17.29, return Wednesday 10th 17.30.
If it is Friday 5th 17.31, return Wednesday 17th 17.30.
Any help would be appreciated!
Thanks
GTry this one here (partly tested)
ALTER Function NextWantedDay
(
@.Startdate datetime,
@.EstWeekday INT,
@.EstTime DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @.EstDate DATETIME
IF (DATEPART(dw,@.Startdate) = @.EstWeekday AND
CONVERT(VARCHAR(10),@.Startdate,108) <=
CONVERT(VARCHAR(10),@.EstTime,108))
RETURN
CONVERT(VARCHAR(50),CONVERT(Varchar(10),
@.Startdate,112),113) + @.EstTime
BEGIN
SET @.Startdate = @.Startdate +1
WHILE DATEPART(dw,@.Startdate) <> @.EstWeekday
BEGIN
SET @.Startdate = @.Startdate + 1
END
END
RETURN CONVERT(VARCHAR(50),CONVERT(Varchar(10),
@.Startdate,112),113) +
@.EstTime
END
Select dbo.NextWantedDay(getdate(),1,'09:00')
HTH, Jens Suessmeyer.

date parameter in reporting services

Hi
I need to have the following result format into a date time parameter with
the current date
='1/07/2005 5:00:00 AM'
so when i schudule this report to run it will look at the current date at 5a
m.
Thankyou
toddTango
Can you eloborate what you expect
Regards
R.D
"Tango" wrote:

> Hi
> I need to have the following result format into a date time parameter with
> the current date
> ='1/07/2005 5:00:00 AM'
> so when i schudule this report to run it will look at the current date at
5am.
> Thankyou
> todd
>|||my expectation is that when i schedule this report to run it looks at the da
y
at 5am & reports back. All records in my database have a date of at 5am.
so the parameter output should be in datetimeformat showing d/MM/yy h:mm:ss
tt
Thanks
Todd
"R.D" wrote:
> Tango
> Can you eloborate what you expect
> Regards
> R.D
> "Tango" wrote:
>sql

Date function

Hi!

I have a report that needs to be run on the seventh of every month for
the dates from 6th of the previous months to the 5th of the current
month. For example, I have to run a report on February 7th for the
01/06/2007 to 02/05/2007. Right now I am doing it manually but I was
curious if there a function or something that will give me the
required date range on the 7th of every month.

Any ideas?

Thanks,
T.tolcis (nytollydba@.gmail.com) writes:

Quote:

Originally Posted by

I have a report that needs to be run on the seventh of every month for
the dates from 6th of the previous months to the 5th of the current
month. For example, I have to run a report on February 7th for the
01/06/2007 to 02/05/2007. Right now I am doing it manually but I was
curious if there a function or something that will give me the
required date range on the 7th of every month.

SELECT @.today = convert(char(8), getdate(), 112)
IF DAY(@.today) = 7
BEGIN
SELECT @.startdate = dateadd(DAY, -2, dateadd(MONTH, -1, @.today))
SELECT @.enddate = dateadd(DAY, -1, @.today)
-- Run uery
END

You can learn more about the date functions in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Date format US/UK

Hi

I have installed SQL server 2005 developer edition.When trying to insert a date in the UK format '16/04/2007' I get the following error...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

if I change the date to be inserted to an American format then I am able to insert it.

I don't want to change the sql as this is something that has always worked - but not with my local install of sql server.

other info that may be of relevance...

My regional settings are all set to UK and this has been applied to the default profile
The server & database collation is Latin1_General_CI_AS
If I attach this database to another instance of sql server installed on a another machine then the insert works.
My pc is a Dell XP Professional SP2

any/all help is appreciated!

chris

Before your insert query add the following statement

SET DATEFORMAT dmy

Insert .....|||

Hi

Thanks for the post

I am thinking maybe I have posted in the wrong forum?

what I don't want to do is write more TSQL, rather I would like to know

what is causing the date to be inserted in the US format so that I can change the setting.

thanks

chris

|||Yes you are absolutly correct.. |||

Rather than doing an implicit conversion by inserting the text into a datetime field use an explicit conversion (onto which you can place a style identifier.

Rather than '16/04/2007' Use CONVERT(datetime, '16/04/2007', 103)|||

Thanks for the posts

I have posted this in the database engine forum so please dont post anymore to this thread.

regards

chris

Date format UK/US

Hi

I have installed SQL server 2005 developer edition.When trying to insert a date in the UK format '16/04/2007' I get the following error...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

if I change the date to be inserted to an American format then I am able to insert it.

I don't want to change the sql as this is something that has always worked - but not with my local install of sql server.

other info that may be of relevance...

My regional settings are all set to UK and this has been applied to the default profile
The server & database collation is Latin1_General_CI_AS
If I attach this database to another instance of sql server installed on a another machine then the insert works.
My pc is a Dell XP Professional SP2

What I don't want to do is write more TSQL but change settings that can alter the behaviour.

any/all help is appreciated!

chris

Chris,

See your other post in Transact-SQL for responses.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

Thanks for your reply

If you read my other post I realise that I have posted in the wrong forum and I clearly state that! as I do not want a TSQL solution.

hence I posted here afterwards

but thank you for your help

|||

Oh and this has not been answered so all constructive help is still appreciated

thanks

chris

|||

Ok one of my colleagues has fixed it.

It turns out that us guys in England don't speak English - we speak British English?

The default language on the Login had to be set to British English.

thanks

chris

Date Format MM/YYYY

Hi:
I need to format a date like this:
01/2004 -> 'MM/YYYY'
I know that I can do this...
SELECT CAST(datepart(mm,getdate()) AS VARCHAR) + '/' + CAST(datepart(yyyy,getdate()) AS VARCHAR)
but , Is there is another better solution?select right(convert(char(10), getdate(), 103), 7)|||Originally posted by joejcheng
select right(convert(char(10), getdate(), 103), 7)

Thanx a lot :D.
You save my life !!

Date Difference

Hi
I'm new to this whole SQL thing(electrical technician, heavy current). I use
TOAD to write queries on our db.
Question is : How do I subtract 2 dates from each other & format the result
to HH:mm:ss in new collumn. I usually do this in MsExcell after extracting
the data.
Thanks in advance.The update statement for the new column would be something
like:
UPDATE YourTable
SET NewColumn =
RIGHT('00' + CAST(datediff(ss, datefield1, datefield2)
/3600 as varchar(10)), 2) + ':'
+ RIGHT ('00' + CAST(datediff(ss, datefield1, datefield2)
%3600/60 as varchar(2)),2) + ':'
+ RIGHT ('00' + CAST(datediff(ss, datefield1, datefield2)
%60 as varchar(2)),2)
-Sue
On Fri, 21 Jan 2005 15:01:53 +0200, muffin1975_1@.eskom.co.za
wrote:

>Hi
>I'm new to this whole SQL thing(electrical technician, heavy current). I us
e
>TOAD to write queries on our db.
>Question is : How do I subtract 2 dates from each other & format the result
>to HH:mm:ss in new collumn. I usually do this in MsExcell after extracting
>the data.
>Thanks in advance.
>

Date comparison problem

Hi

I have a table that contains information that has start dates and end
dates. They are stored in short date format.

I have built a web page that initially returned all the information. I
then want to return information spcific to todays date, where I used

dim todDate
todDate = now

then I did

shTodDate = FormatDateTime(todDate, 2)

which returned the date to a web page that was in the same format as
what was in the access table.

SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"

This was fine beacuse it returned all events that started on that day.
This doesn't help though because some events last a few days, so I
tried the following

SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
endDate<=#'&shTodDate&'#;

This still only returned events that started on the current day.

Does anyone know how I can use <= or >= when comparing dates?

Thanks
puREp3s+puREp3s+ (colin42@.btinternet.com) writes:
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?

Maybe the folks over in comp.datatabases.ms-access knows? It seems
that you are using Access, from the syntax. At least it is not MS
SQL Server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It looks like you have your comparison operators backwards. In TSQL syntax,
this is what you would do to get any events in progress at this moment:

DECLARE @.t DATETIME
SET @.t = CURRENT_TIMESTAMP

SELECT *
FROM Events
WHERE stDate <= @.t AND endDate >= @.t;

... or, if the end date may be NULL, then:

SELECT *
FROM Events
WHERE stDate <= @.t AND (endDate >= @.t OR endDate IS NULL);

Hope that helps,
Rich

"puREp3s+" <colin42@.btinternet.com> wrote in message
news:7a6f18a3.0310211330.7ad2a1d0@.posting.google.c om...
> Hi
> I have a table that contains information that has start dates and end
> dates. They are stored in short date format.
> I have built a web page that initially returned all the information. I
> then want to return information spcific to todays date, where I used
> dim todDate
> todDate = now
> then I did
> shTodDate = FormatDateTime(todDate, 2)
> which returned the date to a web page that was in the same format as
> what was in the access table.
> SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"
> This was fine beacuse it returned all events that started on that day.
> This doesn't help though because some events last a few days, so I
> tried the following
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?
> Thanks
> puREp3s+

Date calculation stored in table to be executed by SP

Hi
I've looked far and wide across a number of forums to find an answer to
the query below, but don't seem to have had any luck. If you know of
any existing posts already dealing with this issue, I'd be grateful for
any pointers.
I have a table STD_REPORTS_LIST in a SQL2k database. The table records
identify reports that a user of an ASP/SQL app can run; each record
points at a given SP which will generate a set of results to return to
the user.
Each record covers a given pre-defined time period: Today, This Week,
This Month, This Quarter, This Year YTD etc. To calculate the
appropriate start and end dates for the reports, two formulae are
stored against each record: the first formula determines the start date
of the report; the second formula determines the end date. The
formulae are stored in varchar fields.
Example data:
SRL_ID 1
SRL_TITLE This Week
SRL_SP_NAME stat_report_prod_views
SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
So the example report above should run SP named stat_report_prod_views
supplying it with start and end dates for the current week.
When a report is selected by the user at the front-end (e.g. "this
week's user activity"):
1. the report ID is passed from ASP to a SP named run_stat_report.
2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
SELECTed from the table,
4. and the dates generated by the formulae SHOULD be passed to the SP
in question (stat_report_prod_views, in the example above).
However, when I pull the fields into the SP using SELECT, the formula
itself comes into the SP as it's a varchar.. it doesn't get executed
... whereas if I run
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
question is: how can I return or calculate the date within the SP based
on the formula in question, ready to pass on to the secondary SP? i.e.
how can I execute the formula rather than just pulling it from the
table?
Please let me know if this is unclear or you require further details to
assist.
Best regards
OwenHi Owen
To use the formula you will need to use dynamic-sql, see
http://www.sommarskog.se/dynamic_sql.html for examples including how to use
sp_executesql to return a value.
John
<owain.williams@.medrus-consulting.co.uk> wrote in message
news:1136234972.052972.49150@.g44g2000cwa.googlegroups.com...
> Hi
> I've looked far and wide across a number of forums to find an answer to
> the query below, but don't seem to have had any luck. If you know of
> any existing posts already dealing with this issue, I'd be grateful for
> any pointers.
> I have a table STD_REPORTS_LIST in a SQL2k database. The table records
> identify reports that a user of an ASP/SQL app can run; each record
> points at a given SP which will generate a set of results to return to
> the user.
> Each record covers a given pre-defined time period: Today, This Week,
> This Month, This Quarter, This Year YTD etc. To calculate the
> appropriate start and end dates for the reports, two formulae are
> stored against each record: the first formula determines the start date
> of the report; the second formula determines the end date. The
> formulae are stored in varchar fields.
> Example data:
> SRL_ID 1
> SRL_TITLE This Week
> SRL_SP_NAME stat_report_prod_views
> SRL_START_DATE DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
> SRL_END_DATE DATEADD(wk, DATEDIFF(wk, 5,GETDATE()), 5)
> So the example report above should run SP named stat_report_prod_views
> supplying it with start and end dates for the current week.
> When a report is selected by the user at the front-end (e.g. "this
> week's user activity"):
> 1. the report ID is passed from ASP to a SP named run_stat_report.
> 2. the fields for the relevant record are pulled from STD_REPORTS_LIST.
> 3. the date formulae fields (SRL_START_DATE and SRL_END_DATE) are
> SELECTed from the table,
> 4. and the dates generated by the formulae SHOULD be passed to the SP
> in question (stat_report_prod_views, in the example above).
> However, when I pull the fields into the SP using SELECT, the formula
> itself comes into the SP as it's a varchar.. it doesn't get executed
> ... whereas if I run
> SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) then I get the
> appropriate dates returned (e.g. 01/01/06 and 07/01/06) ... so the
> question is: how can I return or calculate the date within the SP based
> on the formula in question, ready to pass on to the secondary SP? i.e.
> how can I execute the formula rather than just pulling it from the
> table?
> Please let me know if this is unclear or you require further details to
> assist.
>
> Best regards
> Owen
>|||Hi John,
Thanks for the feedback... I'd heard once or twice of Dynamic SQL but
didn't know much about it... looks like this is going to be very useful
!!
I'll update the post if I need any further info but many thanks in
advance.
Kind regards

Date between 12 and 12

Hi

I am trying to create a daily report that will only select the values between 12 o' clock last night and 12 o'clock the previous night. I want to see all the data for the previous day. If someone will please be able to help me with this i would be very greatful.

Here is my query, i even tried to use the NOW() function:

I'm pasting a few queries that i tried, maybe just a slight adjustment will do the trick

Code Snippet

Select distinct c.clientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime, SUBSTRING(CONVERT(VARCHAR(25),m.SentDateTime),0,15) as 'NewDate'

from Members m, client c

WHERE c.clientID = m.clientID

AND m.SentDateTime < {fn NOW()}

AND m.Active = 1

Group By c.ClientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime

Order By c.ClientName

Another one i tried

Code Snippet

Select distinct c.clientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime, DateDiff(hh,({fn NOW()}-1),MAX(m.SentDateTime)) as 'NewDate'

from Members m, client c

WHERE c.clientID = m.clientID

AND m.SentDateTime > (GETDATE() - DAY(0.5))

AND m.Active = 1

Group By c.ClientName, m.MemberID, m.Name, m.Surname, m.Email, m.SentDateTime

Order By c.ClientName

And this one too. (Although this is from a seperate report.

Code Snippet

SELECT v.ContentId, v.ContentType, COUNT(h.Description) AS Hits, h.Description, DATEPART(yy, v.RenderDate) AS y, DATEPART(dy, v.RenderDate) AS d,

CONVERT(CHAR(12), v.RenderDate, 106) AS date

FROM ViewerPaneRenderHistory AS v INNER JOIN

Members AS M ON v.MemberId = M.MemberID INNER JOIN

HealthBytes AS h ON v.ContentId = h.HealthbyteID

WHERE (v.ContentType = 1)

GROUP BY h.Description, v.ContentType, v.ContentId, DATEPART(yy, v.RenderDate), DATEPART(dy, v.RenderDate), CONVERT(CHAR(12), v.RenderDate, 106)

HAVING (CONVERT(CHAR(12), v.RenderDate, 106) > { fn NOW() } - 1)

ORDER BY Date, COUNT(v.MemberId) DESC

I'v tries a few different ways, but just can't seem to get it right.

Any help would be greatly appreciated.

Kind Regards

Carel Greaves

use the following contion on the where clause...

Code Snippet

ColumnName < Cast(Convert(varchar,@.EndDate,101) as datetime)

and ColumnName >= Cast(Convert(varchar,@.EndDate,101) as datetime) - 1

or

ColumnName < Cast(Convert(varchar,GetDate(),101) as datetime)

and ColumnName >= Cast(Convert(varchar,GetDate(),101) as datetime) - 1

|||

Thanks

|||

Hi,carel:

I am not sure if i known your really means from the upstair threes samples,Following is my reply;

About figure 1:

i think the correct sql is :

Code Snippet

select c.clientname,m.memberid,m.name,m.surname,m.email,m.sentdatetime,SUBSTRING(CONVERT(VARCHAR(25),m.SentDatetime),0,15) as 'NewDate'

from Members as m

inner join client as c

on m.clientID=m.clientID

where m.Active=1

and m.SentDateTime>=Convert(varchar(10),dateadd(dd,-1,getdate()),120)
and m.SentDateTime<Convert(varchar(10),getdate(),120)

i don't know why you use "group by" in the first example.

if you only want previous day's data, i think you could use the condition (Marked with green) only.

NOTE:

the dateformat i used in this example Convert(varchar(10),getdate(),120) returns Date fromat 'YYYY-MM-DD'. and it length is 10

please correct this as your local format, meanwhile you should notice the length.