Date query

I have a query which finds records that are >= todays date. The user has
requested that I should display the records a year back from todays date in
addition to >= todays date. I'm using the following "where" clause.
WHERE (ED_COURSE_CL_1.CLASS_DATE >= GETDATE())
How can I acheive this.
ThanksLook up DATEADD function in SQL Server Books Online.
The various arguments of this function should allow you to do generate an
expression that is equal to a date last year.
--
Anith|||Here are a few date calculations that will give you a good idea of selecting
date ranges. Note that these calculations include entire days. In your
example using GETDATE cuts at the current time of the day which can leave
some rows for the current day out.
-- select past year including today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
20, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate() + 1)
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select past year excluding today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 21, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, -1, DATEADD(year, -1,
getdate()))
-- select past year excluding today and including the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select today and all future dates
WHERE ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, getdate())
Regards,
Plamen Ratchev
http://www.SQLStudio.com

Date query

I have a query which finds records that are >= todays date. The user has
requested that I should display the records a year back from todays date in
addition to >= todays date. I'm using the following "where" clause.
WHERE (ED_COURSE_CL_1.CLASS_DATE >= GETDATE())
How can I acheive this.
Thanks
Look up DATEADD function in SQL Server Books Online.
The various arguments of this function should allow you to do generate an
expression that is equal to a date last year.
Anith
|||Here are a few date calculations that will give you a good idea of selecting
date ranges. Note that these calculations include entire days. In your
example using GETDATE cuts at the current time of the day which can leave
some rows for the current day out.
-- select past year including today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
20, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate() + 1)
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select past year excluding today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 21, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, -1, DATEADD(year, -1,
getdate()))
-- select past year excluding today and including the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select today and all future dates
WHERE ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, getdate())
Regards,
Plamen Ratchev
http://www.SQLStudio.com

Date query

I have a query which finds records that are >= todays date. The user has
requested that I should display the records a year back from todays date in
addition to >= todays date. I'm using the following "where" clause.
WHERE (ED_COURSE_CL_1.CLASS_DATE >= GETDATE())
How can I acheive this.
ThanksLook up DATEADD function in SQL Server Books Online.
The various arguments of this function should allow you to do generate an
expression that is equal to a date last year.
Anith|||Here are a few date calculations that will give you a good idea of selecting
date ranges. Note that these calculations include entire days. In your
example using GETDATE cuts at the current time of the day which can leave
some rows for the current day out.
-- select past year including today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
20, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate() + 1)
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select past year excluding today and the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 21, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, -1, DATEADD(year, -1,
getdate()))
-- select past year excluding today and including the day a year ago
-- if today is Feb 20, 2007, then it will include from Feb 20, 2006 to Feb
19, 2007
WHERE ED_COURSE_CL_1.CLASS_DATE < DATEDIFF(day, 0, getdate())
AND ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, DATEADD(year, -1,
getdate()))
-- select today and all future dates
WHERE ED_COURSE_CL_1.CLASS_DATE >= DATEDIFF(day, 0, getdate())
Regards,
Plamen Ratchev
http://www.SQLStudio.com

Date Parameter Question

Hi all,
I am still kind of new at this...
I am trying to have a date parameter that the user selects a month and it
brings back all the data for that month. Say the users picks August, the
report comes back for all the data for the month of August.
I hope I explained it well enough.
Any help would be great.
Thanks in advance,
KerrieConsider creating a parameter (called @.Month for example) and enter in the
"Available Values" (Report -> Report Parameters) the following:
Jan 1
Feb 2
Mar 3
etc, for each month. Then in your query use the following on your date
column:
SELECT
*
FROM
tblData
WHERE
DATEPART(mm, dateStart) = @.Month
to explain:
DATEPART(mm, <date>) returns the month of a date, e.g. for the 16th of July
it would return "7" for July.
Hope that helps,
-Geoff R G Williams
Primal Blaze Ltd.
"KS" <KS@.discussions.microsoft.com> wrote in message
news:303428AC-348D-45C1-BBCD-9EC5C032AEAE@.microsoft.com...
> Hi all,
> I am still kind of new at this...
> I am trying to have a date parameter that the user selects a month and it
> brings back all the data for that month. Say the users picks August, the
> report comes back for all the data for the month of August.
> I hope I explained it well enough.
> Any help would be great.
> Thanks in advance,
> Kerrie
>
>

Date of Database Last Back

Hi,
Anyone know how to determine the
datetime of the last backup for a
database, in code?
Thanks,
RogerCheck out the system table msdb..backupset. It should contain the
information you are looking for.
Anith|||Yes, thank you.
I was looking to use the DBID, but this column
is not in backupset.
So, must use DBName and db.creation date;
but the create datetime is just a little different
in sysdatabases compared to backupset, but since
it is only < 1 second, truncating to the minute
will work.
Thanks,
Roger

> Check out the system table msdb..backupset. It should contain the
> information you are looking for.
> --
> Anith
>sql

date need back off four year

I have a table in sql server, i need to import this table to another
database in same sql server using DTS, In the table, we have a field called
'qualDate', I need to import the record that the qualDate is in the date of
today and back off four years, for example, today is 8/10/2004, back off four
year should be 8/10/2000, so i need only the record that qualDate is between
8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it
should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.You can use the expression DateAdd(year, -4, GetDate()) in order to find the date four years ago. Without knowing a lot more about your table structures, etc. I can't make a good guess at what code you'll need.

-PatP|||thanks pat, i am using DTS and schedule to import the table to another database every night. My table has fields: Name, Address, County, QualityDate. Quality is short date type. Is that good for you to figure out when i create job how to write a query in where clause, such as, select Name, Address, County, QualityDate from table1 where ...... (i don't know how to do it) .Thanks.|||This won't be absolutely perfect, but you could get really close using:SELECT Name, Address, County, QualityDate
FROM SourceServer.SourceDatabase.dbo.SourceTable
WHERE QualityDate
BETWEEN Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121)
AND Convert(CHAR(10), DateAdd(year, -4, GetDate()), 121) + ' 23:59'That snippet will pick up the rows that occured anytime on the day that is four years ago today. This should work Ok for 90+ years, which will be well past the point that SMALLDATETIME can represent!

-PatP|||thanks pat, i got it. Have a nice day!

Date formatting!

Just started looking at Reporting Services, I have created a report but
the date format I'm getting back is incorrect. I've seen something
that talks about .Net being responsible but I don't think I could code
anything to help. I'm not a programmer!
My statement returns the results as I would like in SQL, but clearly
doesn't apply to Reporting Services, as I'm getting an American format.
Code below for SQL which returns dd/mm/yy as I would like. How do I
got about getting this format into the report?
(invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
CONVERT(DATETIME, @.EndDate, 3))
Any help, gratefully received!
GaryIf you look at the rdl code for the report, and search for <Language>
you will find the language tag and notice that it has defaulted to
en-US. If you change it to en-GB you will get =A3 symbols and UK
formatted. If you still want to format the date further, you can use
FormatDateTime()
regards
weelin
On Oct 25, 12:43 pm, gdav...@.hotmail.com wrote:
> Just started looking at Reporting Services, I have created a report but
> the date format I'm getting back is incorrect. I've seen something
> that talks about .Net being responsible but I don't think I could code
> anything to help. I'm not a programmer!
> My statement returns the results as I would like in SQL, but clearly
> doesn't apply to Reporting Services, as I'm getting an American format.
> Code below for SQL which returns dd/mm/yy as I would like. How do I
> got about getting this format into the report?
> (invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
> CONVERT(DATETIME, @.EndDate, 3))
> > Any help, gratefully received!
> > Gary|||Hi,
The only safe solution I have found is to use "safe" format for
date-time as a string
yyyy-MM-dd (yyyy-MM-dd HH:mm:ss.mmm) and then to pass strings in
between reports.
CONVERT(VARCHAR(20),GETDATE(),120) -- from SQL
=Format(Now,"yyyy-MM-dd HH:mm:ss") -- inside reporting services
=Cdate("2006-10-25 14:15:00") -- string to date
This seems to work fine regardless of PC setups on the network.
The drawback is that you lose the date picker.
-- This one adds 16 hours to STRING parameter called TheDay
=format(DateAdd("h",16,cdate(Parameters!TheDay.Value)),"yyyy-MM-dd")
-- if you want to re-format string for display try
=Format(cdate("2006-10-25"),"dd/MM/yy") --October 25, 2005
I'm in Canada and working with British-American formats always ends up
with lots of errors and headache.
As a general rule I tend to use only two date formats whenever
possible:
1. 2006-10-25
2. October 25, 2006
Everything else is ambiguous.
Sincerely,
Damir
gdavid9@.hotmail.com wrote:
> Just started looking at Reporting Services, I have created a report but
> the date format I'm getting back is incorrect. I've seen something
> that talks about .Net being responsible but I don't think I could code
> anything to help. I'm not a programmer!
> My statement returns the results as I would like in SQL, but clearly
> doesn't apply to Reporting Services, as I'm getting an American format.
> Code below for SQL which returns dd/mm/yy as I would like. How do I
> got about getting this format into the report?
> (invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
> CONVERT(DATETIME, @.EndDate, 3))
> Any help, gratefully received!
> Gary|||Thanks for the replies guys, I eventually figured out that I needed MM
rather than mm. I do agree that using a date similar to 10 October
2006 would rule out any potential issues as this does seem to be
somewhat of a common problem.
Thanks
Gary
Damir wrote:
> Hi,
> The only safe solution I have found is to use "safe" format for
> date-time as a string
> yyyy-MM-dd (yyyy-MM-dd HH:mm:ss.mmm) and then to pass strings in
> between reports.
> CONVERT(VARCHAR(20),GETDATE(),120) -- from SQL
> =Format(Now,"yyyy-MM-dd HH:mm:ss") -- inside reporting services
> =Cdate("2006-10-25 14:15:00") -- string to date
> This seems to work fine regardless of PC setups on the network.
> The drawback is that you lose the date picker.
> -- This one adds 16 hours to STRING parameter called TheDay
> =format(DateAdd("h",16,cdate(Parameters!TheDay.Value)),"yyyy-MM-dd")
> -- if you want to re-format string for display try
> =Format(cdate("2006-10-25"),"dd/MM/yy") --October 25, 2005
> I'm in Canada and working with British-American formats always ends up
> with lots of errors and headache.
> As a general rule I tend to use only two date formats whenever
> possible:
> 1. 2006-10-25
> 2. October 25, 2006
> Everything else is ambiguous.
> Sincerely,
> Damir
> gdavid9@.hotmail.com wrote:
> > Just started looking at Reporting Services, I have created a report but
> > the date format I'm getting back is incorrect. I've seen something
> > that talks about .Net being responsible but I don't think I could code
> > anything to help. I'm not a programmer!
> >
> > My statement returns the results as I would like in SQL, but clearly
> > doesn't apply to Reporting Services, as I'm getting an American format.
> > Code below for SQL which returns dd/mm/yy as I would like. How do I
> > got about getting this format into the report?
> >
> > (invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
> > CONVERT(DATETIME, @.EndDate, 3))
> >
> > Any help, gratefully received!
> >
> > Gary

Date format reset

Hi,
I have a date field in a dataset that is bringing back UK formatted dates
(dd/MM/yyyy) from a UK configured SQL Server on a UK configured server. In RS
the preview displays these dates, again as UK - everything is fine so far...
My problem is when I run the report the dates are output in US format
(MM/dd/yyyy). Even if I set the textbox's format to be the correct style
date, the values are always US style.
Any help much appreciated.
AlIf there is no language information set on the text box, the language
of the report is used. If the language of the report is not set, the
language of the Web browser is used. If the language of the Web browser
is not set, the language of the operating system of the report server
is used. For example, if you set a specific language on a text box that
displays date information, then that text box is always displayed with
the date format for that language even if the report, Web browser or
server is set to a different language.

Date conversion and formatting

Hi all,
Need some help. I have a report which brings back data over a period
of time from one field (date_required). This is all returned in one
column. I would like to create a report whereby the data is filtered
into the appropriate Month's column, jan, feb etc etc.
I have used the Month statement to extract this and tried using the
visibility expression to create this but without any joy.
Could someone give me some pointers on how to achieve this?
Thanks
GaryHere's what I did. In the query, use the datepart(MM,datefield) to extra the
month number, 1-12. Then in the matrix use the sorting tab of the group
properites to sort ascending. This will sort the columns 1 --> 12. Then go
into the properties for the month field and set the expession to something
like this:
=Left(MONTHNAME(Fields!MonthField.Value),3)
the columns will show Jan, Feb, Mar,etc. rather the 1,2,3...
Hope this helps you.
"gdavid9@.hotmail.com" wrote:
> Hi all,
> Need some help. I have a report which brings back data over a period
> of time from one field (date_required). This is all returned in one
> column. I would like to create a report whereby the data is filtered
> into the appropriate Month's column, jan, feb etc etc.
> I have used the Month statement to extract this and tried using the
> visibility expression to create this but without any joy.
> Could someone give me some pointers on how to achieve this?
> Thanks
> Gary
>

Date calculation

Hi,
I am simply looking for a calculation to get back always the last day of the month. Can anybody help me ?Originally posted by dajm
Hi,

I am simply looking for a calculation to get back always the last day of the month. Can anybody help me ?

declare @.month int,
@.year int
select @.month = 2,
@.year = 2000
select dateadd(dd,-1,convert(datetime,convert(varchar,(@.month+1))+ '/01/'+convert(varchar,@.year)))

I assume you must be passing the year & month to get the last day of the month. The above will work for that|||Must'nt forget about december

DECLARE @.x datetime
SELECT @.x = GetDate()
SELECT DATEADD(mm,1,CONVERT(datetime,CONVERT(varchar(2),D ATEPART(mm,@.x))+'/01/'+CONVERT(varchar(4),DATEPART(yy,@.x))))-1

What happens with this?

declare @.month int,
@.year int
select @.month = 12,
@.year = 2000
select dateadd(dd,-1,convert(datetime,convert(varchar,(@.month+1))+ '/01/'+convert(varchar,@.year)))

???|||Good point brett !!!
My Bad :)|||I dont seem to unserstand .. why all my posts are being posted in the duplicate today ?? i mean for every post i am making ... its being submitted twice|||Originally posted by Brett Kaiser
Must'nt forget about december

DECLARE @.x datetime
SELECT @.x = GetDate()
SELECT DATEADD(mm,1,CONVERT(datetime,CONVERT(varchar(2),D ATEPART(mm,@.x))+'/01/'+CONVERT(varchar(4),DATEPART(yy,@.x))))-1

What happens with this?

declare @.month int,
@.year int
select @.month = 12,
@.year = 2000
select dateadd(dd,-1,convert(datetime,convert(varchar,(@.month+1))+ '/01/'+convert(varchar,@.year)))

???

Sorry Brett, your function gives me back the same day of the last month. I am searching for something following: today is 2003/12/02 and what I need the get returned is 2003/11/30. How about this ? Any chance ?|||Sorry guys,

I am really dump !!!

All i needed is the following:

select getdate()-(datepart(dd,getdate() )

thx for your effort|||Originally posted by dajm
select getdate()-(datepart(dd,getdate() )


Brilliant !!!

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