Date range problem in crystal report 8.5

Hi all

I have a problem in using date range

If i select a date range,need to get values for proveious years june 1st to may 31st of current year

ex: 02/01/2006 - 02/28/2006(Current)

need to get values for 06/01/2005 - 05/31/2006 date range

could any one help me in writing formula for this

Thanks in advancedoesn't look like real question ,, , pls be specific|||Thanks for your reply

i would like to give you more specific

In my requiremnet if i selected a date range ex:01/06/2006 - 31/06/2006

need to get the values of preveious years of June 1st - current years May 31st

01/06/2005 - 31/05/2006 values should display

actually we have two fields in that in first field need to display the first date range values and in second one need to do like that

We have two fields named

current and year to date

current means date range the user selected

Year to date means data from June 1 of previous year to May 31 st of Current year|||Hi
use this formula
datetime('01-jan-2006')-365

include if condition to handle leap year
Hope it helps|||It's very simple just use this formula in formula field

formula =DateAdd ("yyyy",-1 , {YOUR DATE })

and use this formula wherever you want to use same date of previous year

Best Luck...|||Thanks for your reply

its useful for me

could you pls tell how to take fixed date 1st june to end date 31st may

date range parameter

i have a need by date in my report. i would like to create a date parameter
where the user can select today, 1 week, 1 month, 60, 90, etc. days between
current date and need by date. i've created parameters before, but this
one's giving me trouble. any ideas?Hi,
I think I would create 2 parameters like to work with that kind of
requirements
- Datevalue: would hold the value (Number)
- DateType: would hold the parameter type (DropDown) filled with value
like (Days,Weeks,Months,...)
After that i would do the calculation at the stored procedure level
using the T-SQL DATEADD function..
HTH,
Eric|||Create parameters using non-queried and put in the following (as example)
Label Value
Today 0
1 Week 7
30 Days 30
60 Days 60
90 Days 90
Then in your query do this:
select * from whatever where mydatefield < dateadd(dd,?, getdate())
Anyway, gives you an idea of how to do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> i have a need by date in my report. i would like to create a date
parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||In your where clause, you could have a statement such as:
WHERE
MyDate >= DATEADD(DAY, -@.DaysBack, GETDATE())
Then, in your parameter dropdown, you'll specify the number of days that
correlate to each value in the dropdown, e.g. 1 week - 7, etc.
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
>i have a need by date in my report. i would like to create a date
>parameter
> where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> current date and need by date. i've created parameters before, but this
> one's giving me trouble. any ideas?|||Thanks for your help, but when I tried to do this I got an error message,
"argument data type datetime is invalid for argument 2 of dateadd function"
My where statement reads:
WHERE (mydate <= DATEDIFF(dd, @.date , GETDATE()))
@.date being the name of my parameter.
"Bruce L-C [MVP]" wrote:
> Create parameters using non-queried and put in the following (as example)
> Label Value
> Today 0
> 1 Week 7
> 30 Days 30
> 60 Days 60
> 90 Days 90
> Then in your query do this:
> select * from whatever where mydatefield < dateadd(dd,?, getdate())
> Anyway, gives you an idea of how to do this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "jmann" <jmann@.discussions.microsoft.com> wrote in message
> news:55BB6537-69B8-4294-8CC8-82CFBA40E382@.microsoft.com...
> > i have a need by date in my report. i would like to create a date
> parameter
> > where the user can select today, 1 week, 1 month, 60, 90, etc. days
> between
> > current date and need by date. i've created parameters before, but this
> > one's giving me trouble. any ideas?
>
>|||Hi,
After giving it a little more tought, Here is what i would do:
Create 2 parameters
- DateValue (Integer) holding the value
- DateType (Dropdown) holding the parameter type
Label Value
Days 0
Weeks 1
Months 2
...
After that i would do the calculation at the query of stored procedure
level with something like this:
SELECT *
FROM Table1
WHERE Table1.DateField >= CASE
WHEN @.DateType = 0 THEN
DATEADD(dd,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 1 THEN
DATEADD(wk,CAST(@.DateNumber AS
INTEGER),GETDATE())
WHEN @.DateType = 2 THEN
DATEADD(mm,CAST(@.DateNumber AS
INTEGER),GETDATE())
END
HTH,
Eric|||WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))|||This will work, or, just set the parameter type as integer instead of
string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aiwa" <eric__brochu@.hotmail.com> wrote in message
news:1105996187.872921.265900@.f14g2000cwb.googlegroups.com...
> WHERE (mydate <= DATEDIFF(dd, CAST(@.date AS INTEGER) , GETDATE()))
>

Date Range

I have a query that I have to place a beginning and ending date to select the
information needed. The date is always 30 days for the current date. Is
there a way to set up a query to select current date minus 30 days that I
will not have to change the date each time?
Thanks in advance for the help.
On Tue, 28 Feb 2006 12:41:27 -0800, Daniell wrote:

>I have a query that I have to place a beginning and ending date to select the
>information needed. The date is always 30 days for the current date. Is
>there a way to set up a query to select current date minus 30 days that I
>will not have to change the date each time?
>Thanks in advance for the help.
Hi Daniell,
SELECT DATEADD(day, -30, CURRENT_TIMESTAMP)
Hugo Kornelis, SQL Server MVP
|||Thanks Hugo, I will give that a try.
"Hugo Kornelis" wrote:

> On Tue, 28 Feb 2006 12:41:27 -0800, Daniell wrote:
>
> Hi Daniell,
> SELECT DATEADD(day, -30, CURRENT_TIMESTAMP)
> --
> Hugo Kornelis, SQL Server MVP
>
sql

Date Range

------------------------

sqladapter.SelectCommand = New OdbcCommand("SELECT * from " & table & "where date between @.from_date and @.to_date", conn)

sqladapter.SelectCommand.Parameters.Add("@.from_dat e", OdbcType.date, "date").Value = Me.DateTimePicker1.Value.ToShortDateString

sqladapter.SelectCommand.Parameters.Add("@.to_date" , OdbcType.date, "date").Value = Me.DateTimePicker2.Value.ToShortDateString

i tried these codes but when was prompt error " date type cannot convert to int"

how do i solve the prob? any idea?

Quote:

Originally Posted by girl

------------------------

sqladapter.SelectCommand = New OdbcCommand("SELECT * from " & table & "where date between @.from_date and @.to_date", conn)

sqladapter.SelectCommand.Parameters.Add("@.from_dat e", OdbcType.date, "date").Value = Me.DateTimePicker1.Value.ToShortDateString

sqladapter.SelectCommand.Parameters.Add("@.to_date" , OdbcType.date, "date").Value = Me.DateTimePicker2.Value.ToShortDateString

i tried these codes but when was prompt error " date type cannot convert to int"

how do i solve the prob? any idea?

Add single quote

' "& table & " '

sqladapter.SelectCommand = New OdbcCommand("SELECT * from '" & table & "' where date between @.from_date and @.to_date", conn)|||sqladapter.SelectCommand = New OdbcCommand("SELECT * from '" & table & "'where date between @.from_date and @.to_date", conn)

sqladapter.SelectCommand.Parameters.Add("@.from_date", OdbcType.Date).Value = Me.MonthCalendar1.SelectionStart.Date

sqladapter.SelectCommand.Parameters.Add("@.to_date", OdbcType.Date).Value = Me.MonthCalendar2.SelectionEnd.Date

sqladapter.Fill(ds, "" & table & "")

i did what you say but it prompt me @. the .fill statement
Optional feature not included!! what should i do??

Date Question

I want to select all records where the date is less than today minus 30. I have tried (Current_Date - 30) but the syntax is wrong. any ideas?

Maybe something like this:

declare @.whatTime table ( aTime datetime )
insert into @.whatTime values ('1/1/7')
insert into @.whatTime select dateadd (mi, -12, getdate())

select * from @.whatTime
where aTime >= getdate() - cast ('0:30:00.00' as datetime)

-- aTime
--
-- 2007-03-08 15:12:45.920

Another alternative would be something like:

select * frm @.whatTime
where aTIme >= dateadd (mi, -30, getdate())

|||

It wasn't clear from your original post exactly what time/date units the '30' (that you want to subtract) should be in.

Kent provided examples that will subtract 30 minutes, for a wider range of units for use in his DATEADD example then see the following - quoted from BOL:

DATEADD (datepart , number, date )

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Chris

|||

Oh, brother! You are SOO right! I think the question had to do with days and not minutes. :-) ( Still laughing at myself! )

If it is days try:

getdate() - 30

That should work fine.

|||I am laughing too!!! I was referring to days, and the gedate works fine. Thanks|||

Chris:

I so appreciate your answer. I had bricked this so badly and you did such a good job of re-directing AND leaving me an easy out. I really admire the work with words.

Kent

|||

No worries. :) Your answers were fundamentally correct - I also interpreted the requirement as being for 30 minutes when I first read the original post.

Thanks for the info over GETDATE() - x, I didn't realise you could do that...

Cheers
Chris

Date Query

Good Morning to all

I wrote a query to access some data from sql server ,my query is as follows

strsel = "select * from schedule where sector_id='" & str_sec_id & "' And dep_date = " & bb & ""

bb is the date transferred from the other module

i want to check the dep_date as short date format.because bb is in short date format.

in the above checking i am not getting the query result

please look on this code

Regards

Unni

what you are passing in bb, if bb is datetime variable just do this bb.toshortDateString().

furthermore, try Response.write(strsel). so that you can get the actualy query on browser then try to run it in sql see what u get

cheers

|||

unnik_pulikkal:

strsel = "select * from schedule where sector_id='" & str_sec_id & "' And dep_date = " & bb & ""

Also it looks like you are forgetting the single quotes around your date field:

"select * from schedule where selector_id = 3 And dep_date = 1/3/2007"will not give you the results that you want

"select * from schedule where selector_id = 3 And dep_date = '1/3/2007'"will give you the results you want

Peace,

Date Query

Hi all:

I am trying to get data that wasn't used in the last six month. Created_on (date field)

select Category_tree_value, Created_on from workitem
where Created_on (field was used from the last six months)

Created_on format (2005-10-01 00:00:00.000)

Thanking everyone in advance.

LystraTry

where Created_on < DateAdd(Month, -6, GetDate())

Date problem in Selection formula

Hi,
I am using Crystal Reports 9 and i am facing some problem in creating a selection formula to select some records in between a range of given dates.

MY DATE PARAMETER IS A STRING AND I NEED TO CONVERT IT TO DATE TYPE

Actually I don't know how to make to needed selection formula. What i really needed is to select record sets in between a range of dates which is given as parameters to the reports

Pls help
JOBINuse record selection formula

urfield >=dateparameter1 and urfield<=dateparameter2

date problem : dynamic SQL in an OLE DB Source component

Hello,

I have an OLEDB Source component with a Oracle OLEDB connection manager.
In my SQL statement I must do something like this ...

SELECT * FROM OracleTable
WHERE convert(datetime, OracleDate) = parameter

I've dynamicaly build the statement like this : http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

Unfortunately this doesn't work because the variable is a datetime value and not a string.

Maybe another way is to store the date in a table in my SQL database and include it in my Oracle SQL statement ? This would mean that there are multiple connection managers being used in one SQL statement ... I have no idea if this would be possible ...

Any other options ?

Yes that would be possible but is a far far more complicated solution than it needs to be.

Keep persuing the expression solution. If the variable is a DateTime then you need to cast it as a string. Remember - the expression is populating a string property so it should return a string.

This is a string manipulation problem - pure and simple!

-Jamie

|||thanks, it works ... I now convert the date to integer format and then to string format so it can be used in the dynamic SQL query

Date Problem

I have a problem with date parameter. I have selected data type as date time. I would like the date to be in British format. When I select a date from Calendar control, for example 25 December, then I get a error>

The value provided for the report prameter Startdate is not valid for its type.

Can anyone please help me on this?

regards

Josh

Hello JoshKer.

In Reporting Servies you can define the default Language of the reports, this Language is used for the Date Culture...

To edit him : In Layout Tab / Click on the Yello Part (out of the report) / Properties or F4 / then you accessed the Report Properties...

Alter the Language Propertie, to English (United KingDown)

If this not correct your error, then you can make the CAST on the Date Parameter on the Query....

I Hope Have helped you !

|||See if the problem is dedicated to this entry, if yes, vote for it.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=271928

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for your reply. But it gives the same problem with the date picker.

date part of date time

SELECT ltrim(str(datepart(yyyy,getdate()))) +'-'+
replicate('0',2-len(ltrim(str(datepart(mm,getdate())))))+ltrim(str (datepart(mm,getdate())))+'-' +replicate('0',2-len(ltrim(str(datepart(dd,getdate())))))
+ltrim(str(datepart(dd,getdate())))

This is how i am getting datepart of datetime.Is there any other way to get the date and also time seperately..

Thanks.Convert(char(10), getdate(), 120)|||SELECT CONVERT(varchar(10),GetDate(),101), CONVERT(varchar(10),GetDate(),108)|||Beat you by one second, Kaiser! Nyah nyah nyah! Too slow! Ya snooze, ya looze! :p :p :p :p :p|||SELECT CONVERT(varchar(10),GetDate(),101), CONVERT(varchar(10),GetDate(),108)

Where should i know to use 101 and 108 in convert functions.I could get that from BOl...
Can u help me with that.

Thanks and sorry for asking such a silly question.|||I'm sorry i got that myself.

Thank u all.|||I'm sorry i got that myself.

Thank u all.

Congratulations! Because of forum a lot of people became so lazy but not you.|||Beat you by one second, Kaiser! Nyah nyah nyah! Too slow! Ya snooze, ya looze! :p :p :p :p :p

Yeah...see what happens when work gets in the way...

I'm giving out specs over the phone to a noob while I'm doing this...

And this ain't sqlteam...that's 1 minute increments

Damn...[sniped] anyway

And what's with 120?

bleeeeeach...

:D|||And what's with 120?Wah, wah, waaaaah!

120 is just the "junior grade" version of 121, my preferred style!

-PatP|||121? How pretentious. 120 shines in its simplicity and elegance.

Dude, 121 is SOOOOO last version.....

And what's with the varchar, eh?|||Personally I like 126..talk about pretentions...but that's just my mainframe db2 talking...

too bad they lost pos 4-6 of their milliseconds...gotta be the cpu's fault..right?

Figured he wanted 101 though..nice and pretty...

And geez...I need to speak with Karolyn or ms_sql_dba to straighten you out?

:D

I saw you suck'n up blind dude... :)

Varchar...force of habit...

we need more banter in the corral...

http://www.dbforums.com/showthread.php?p=3672844#post3672844|||Don't threaten to sick your dogs on me!

Like I'm gonna be scared of a "126"er...|||Dude, 121 is SOOOOO last version.....Oh, they used to say that about my Leisure Suit and silver tipped cowboy boots too!

-PatP|||Hold on to those. They're coming back.|||Hold on to those. They're coming back.

I hated the 70's..

What do you guys think about this:

http://www.dbforums.com/t996699.html

date parameter

One of the fields in the table is of type smalldatetime.
When I select a date from the report parameter (a calender), the report produces an error: The value provided for the report parameter 'ValueDate' is not valid for its type.
Please note that if I use a report parameter of type string and enter 21 may 2007 the report works. But if a date is selected from the calender control i.e. 21/05/2007 the report produces the above error.

How is it possible to use the calender parameter without the report giving the error pls?
Thanks

i think u should go to report parameter ->properties -> change datatype to datetime|||

Hi,
Yes I am doing just that.
That is the problem because even though the parameter is set to datetime, it does not accept the format such as 21/05/2007 which is what you get if the date is selected from the calender.

Thanks

|||

Hi there,

Have ever you tried to fill a data in other format? This error semms like a invalid format date. Try to fill 05/21/2007 (English format - mm/dd/yyyy).

Let me know if this solved your problem.

Lant

|||you have to pass it in mm/dd/yyyy format

Date Parameter

hi All
I am quiet new to sql reporting so would really appreciate some help. I am
using this with CRM 3.0
I want to do a report where user can select a to date and a from date to see
all completed phone calls. However both the to and the from are based on one
field called actualend.
Actual end is a date time field but in the layout tab i have used the function
=Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
into just a dd-mm-yy value. Now i want to create a paramater where user can
select a start date and an end date to report. Can someone please guide me
how to go about this.
I have tried using the @.>=actualend parameter under the data tab. It works
for that but does not work under the reports tab. I can see it as a parameter
under report - report parameters but when i run the report with a date say
14/05/07 i get the error as
An error occured during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the data set CRM_MSCRM
Arithmetic overflow error converting expression to data type datetime.
I would really appreciate some help.
Thanks and Regards
Ridhimai have managed to go one step beyond and set up a parameter which uses the
value as mm/dd/yy... how can i change it to so user can enter a value in
dd-mm-yy format. the parameter type is strng.
"Ridhima Sood" wrote:
> hi All
> I am quiet new to sql reporting so would really appreciate some help. I am
> using this with CRM 3.0
> I want to do a report where user can select a to date and a from date to see
> all completed phone calls. However both the to and the from are based on one
> field called actualend.
> Actual end is a date time field but in the layout tab i have used the function
> =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> into just a dd-mm-yy value. Now i want to create a paramater where user can
> select a start date and an end date to report. Can someone please guide me
> how to go about this.
> I have tried using the @.>=actualend parameter under the data tab. It works
> for that but does not work under the reports tab. I can see it as a parameter
> under report - report parameters but when i run the report with a date say
> 14/05/07 i get the error as
> An error occured during local report processing.
> An error has occurred during report processing.
> Cannot read the next data row for the data set CRM_MSCRM
> Arithmetic overflow error converting expression to data type datetime.
> I would really appreciate some help.
> Thanks and Regards
> Ridhima|||That option is not available instead you can use date picker or seperate all
three in 3 parameters like mm, dd, yyyy and then concatenate..
Amarnath
"Ridhima Sood" wrote:
> i have managed to go one step beyond and set up a parameter which uses the
> value as mm/dd/yy... how can i change it to so user can enter a value in
> dd-mm-yy format. the parameter type is strng.
> "Ridhima Sood" wrote:
> > hi All
> >
> > I am quiet new to sql reporting so would really appreciate some help. I am
> > using this with CRM 3.0
> >
> > I want to do a report where user can select a to date and a from date to see
> > all completed phone calls. However both the to and the from are based on one
> > field called actualend.
> >
> > Actual end is a date time field but in the layout tab i have used the function
> > =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> > into just a dd-mm-yy value. Now i want to create a paramater where user can
> > select a start date and an end date to report. Can someone please guide me
> > how to go about this.
> >
> > I have tried using the @.>=actualend parameter under the data tab. It works
> > for that but does not work under the reports tab. I can see it as a parameter
> > under report - report parameters but when i run the report with a date say
> > 14/05/07 i get the error as
> > An error occured during local report processing.
> > An error has occurred during report processing.
> > Cannot read the next data row for the data set CRM_MSCRM
> > Arithmetic overflow error converting expression to data type datetime.
> >
> > I would really appreciate some help.
> >
> > Thanks and Regards
> > Ridhima|||hi Amarnath
When i use the date picker it doesnt work either.. displays and error:(
"Amarnath" wrote:
> That option is not available instead you can use date picker or seperate all
> three in 3 parameters like mm, dd, yyyy and then concatenate..
> Amarnath
> "Ridhima Sood" wrote:
> > i have managed to go one step beyond and set up a parameter which uses the
> > value as mm/dd/yy... how can i change it to so user can enter a value in
> > dd-mm-yy format. the parameter type is strng.
> >
> > "Ridhima Sood" wrote:
> >
> > > hi All
> > >
> > > I am quiet new to sql reporting so would really appreciate some help. I am
> > > using this with CRM 3.0
> > >
> > > I want to do a report where user can select a to date and a from date to see
> > > all completed phone calls. However both the to and the from are based on one
> > > field called actualend.
> > >
> > > Actual end is a date time field but in the layout tab i have used the function
> > > =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> > > into just a dd-mm-yy value. Now i want to create a paramater where user can
> > > select a start date and an end date to report. Can someone please guide me
> > > how to go about this.
> > >
> > > I have tried using the @.>=actualend parameter under the data tab. It works
> > > for that but does not work under the reports tab. I can see it as a parameter
> > > under report - report parameters but when i run the report with a date say
> > > 14/05/07 i get the error as
> > > An error occured during local report processing.
> > > An error has occurred during report processing.
> > > Cannot read the next data row for the data set CRM_MSCRM
> > > Arithmetic overflow error converting expression to data type datetime.
> > >
> > > I would really appreciate some help.
> > >
> > > Thanks and Regards
> > > Ridhima|||What I meant was you can use date picker for just picking dates in any ormat,
because this type of formatting upfront is not possible.
Amarnath
"Ridhima Sood" wrote:
> hi Amarnath
> When i use the date picker it doesnt work either.. displays and error:(
> "Amarnath" wrote:
> > That option is not available instead you can use date picker or seperate all
> > three in 3 parameters like mm, dd, yyyy and then concatenate..
> >
> > Amarnath
> >
> > "Ridhima Sood" wrote:
> >
> > > i have managed to go one step beyond and set up a parameter which uses the
> > > value as mm/dd/yy... how can i change it to so user can enter a value in
> > > dd-mm-yy format. the parameter type is strng.
> > >
> > > "Ridhima Sood" wrote:
> > >
> > > > hi All
> > > >
> > > > I am quiet new to sql reporting so would really appreciate some help. I am
> > > > using this with CRM 3.0
> > > >
> > > > I want to do a report where user can select a to date and a from date to see
> > > > all completed phone calls. However both the to and the from are based on one
> > > > field called actualend.
> > > >
> > > > Actual end is a date time field but in the layout tab i have used the function
> > > > =Format(Fields!Phone_Call_Completed_Date.Value,"dd-MM-yy") to format this
> > > > into just a dd-mm-yy value. Now i want to create a paramater where user can
> > > > select a start date and an end date to report. Can someone please guide me
> > > > how to go about this.
> > > >
> > > > I have tried using the @.>=actualend parameter under the data tab. It works
> > > > for that but does not work under the reports tab. I can see it as a parameter
> > > > under report - report parameters but when i run the report with a date say
> > > > 14/05/07 i get the error as
> > > > An error occured during local report processing.
> > > > An error has occurred during report processing.
> > > > Cannot read the next data row for the data set CRM_MSCRM
> > > > Arithmetic overflow error converting expression to data type datetime.
> > > >
> > > > I would really appreciate some help.
> > > >
> > > > Thanks and Regards
> > > > Ridhima

date in a database

dear experst:

i have a web site and a calendar where a client can select a day week or a month to see some statistics, for the day i know how to do it. and i have also stored procedures. what i want is how to store the startday and end day in a variable when the client select a week or a month, so i can assign these variables to the stored procedure so the stored procedure can query the database and return the data for just the specific selected date.

this problem is realy annoying me and i can not continue my work without solving it. any help will be more than appreciated.
thanksvarname1 = startday
varname2 = endday|||thanks for your reply,
what i want is how to know the start date and the end date when i user select a week or a month and how to calculate the value so when give it to the stored procedure it will know that is a week?
thanks|||to get the Frist/Last day-of-the-week, Frist/Last day-of-the-month u can use the following queries. this assumes that Sunday is the first day of the week and Saturday is that last one. also the dates r calculated with respect to date variable.

declare @.DtVar smalldatetime
set @.DtVar = '2007-03-22' -- an example date
select @.DtVar+1-datepart(dw,@.DtVar) as StartingDayOfTheWeek
select @.DtVar+7-datepart(dw,@.DtVar) as LastDayOfTheWeek
select @.DtVar+1 - datepart(dd,@.DtVar) as FristDateOfTheMonth
select dateadd(mm,1,@.DtVar) - datepart(dd,@.DtVar) as LastDateOfTheMonth|||thanks a lot for your answer, but what if i want monday be the first day of the week and sunday the last day of the week? what should i changed.
thanks|||dude, please play around with the number. when you got a solution for a problem, you shall learn to understand it and explore further. By asking, "what if i want Monday", you're asking the same question...|||Set @.@.datefirst 1|||thanks a lot for your answer, but what if i want monday be the first day of the week and sunday the last day of the week? what should i changed.
thanks

You can assign the days into an int array and pass to your stored procedure.

Date from Oracle

I am trying to show data from Oracle database. This data has a date field.
Here is an SQL statement I am using:
select job_work_date, work_asgn_id, work_terminal, work_craft from
assignment_history
where trunc(job_work_date) >= to_date(@.From, 'MM/DD/YYYY')
It says named parameters cannot be used. Used unnamed parameters. What do I
do?
ThanksOn Dec 6, 3:14 pm, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am trying to show data from Oracle database. This data has a date field.
> Here is an SQL statement I am using:
> select job_work_date, work_asgn_id, work_terminal, work_craft from
> assignment_history
> where trunc(job_work_date) >= to_date(@.From, 'MM/DD/YYYY')
> It says named parameters cannot be used. Used unnamed parameters. What do I
> do?
> Thanks
Here's the trick:
1. If you can't use a Named Parameter, it means that the query is
expecting a parameter designator of "?". Your query would then be
TO_DATE( ?, 'MM/DD/YYYY' )
2. Make this change, then click on the DataSet Properties button at
the top [...]. Go to the Parameters tab. This list is now the
parameters that line up with the parameters used in the query, and
they *don't* have to be the same Report Parameters that you are
passing back into your report. Not that there are Expression options
all here.
3. Note that the Parameter Name is "?" and the expression is now
=Parameters!Parameter1.Value . What RS just did was create a new
parameter for you... how nice. What you really want is to change the
expression here to use the Named Parameter that you are using to drive
the report. But, since TO_DATE() expects a string, and your From
Parameter is probably a DateTime (so you can use the Calendar), you
want to convert the types with the Format command; that ,and Oracle is
really picky about formatting. Change the expression for ? to
= Format( Parameters!From.Value, "MM/dd/yyyy" )
4. Close out, go to Report Parameters, and delete the Parameter1
-- Scott|||It might have to do with use of the @. sign. I know that Oracle doesn't need
that for parameters, unlike MSSQL. You could try without the @., but I'm not
sure SSRS will like that.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%2351ivSEOIHA.2140@.TK2MSFTNGP03.phx.gbl...
>I am trying to show data from Oracle database. This data has a date field.
>Here is an SQL statement I am using:
> select job_work_date, work_asgn_id, work_terminal, work_craft from
> assignment_history
> where trunc(job_work_date) >= to_date(@.From, 'MM/DD/YYYY')
> It says named parameters cannot be used. Used unnamed parameters. What do
> I do?
> Thanks
>

Date from a Datetime field

Hi,

a colleague need to get the date from a datetime field with a select query.

Is there an easy way to get this date? I need an excample.

Thanks

Thomas

select convert(varchar,datepart(mm,DateTimeCol)) + '/' +convert(varchar,datepart(dd,DateTimeCol)) + '/' + convert(varchar,datepart(yy,DateTimeCol)) from DataTable|||

select CONVERT(CHAR(10),GETDATE(),110)

select CONVERT(CHAR(10),mydatetimefield,110) from mytable

|||

Thomas:

Try SELECT CONVERT (VARCHAR(10), urDateTime, 101)

Check in BOL under CONVERT > CAST and CONVERT if there is an alternate format that you would rather use.

Dave

|||

Use the ISO unseparated date format style and CONVERT:

select convert(varchar, dtcol, 112)

All other formats are region or language specific so you will have issues interpreting it correctly depending on how you consume or use it.

|||

The suggestions so far would return your column as a string. If you want the data type of the returned column to be DateTime (with the time component removed) you can do this instead.

Select DateAdd(Day, DateDiff(Day, 0, dtcol), 0) As dtcol

Date Formats

Hi,
While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
the following error :
"The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value"
But the same statement executes without any error if I connnect the
application to a different SQL Server.
I'm using SQL Server 7.
Why this error, do I need to set anything the server or the client ?
Thanks
Rajeev RIs DATEOFLEAVING of datetime datatype?
If so try the following:
set dateformat dmy
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = '08/31/2003'
or this
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = convert(datetime, '08/31/2003', 103)
--
Dean Savovic
www.teched.hr
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client ?
> Thanks
> Rajeev R
>|||Try changing the statement to:
SELECT STAFFNO FROM MASTER WHERE DATEOFLEAVING = CAST('2003-08-31 00:00:00'
AS DATETIME)
Pete
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message
news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm
getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect
the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client
?
> Thanks
> Rajeev R
>

date format, select convert(varchar(8), getdate(), 1)

With this clause "select convert(varchar(16), getdate(), 101)" I can get mmddyyyy but

How can I get time such as mmddyyyyhhmm

Thanks
Daniel

I do not believe there is a standard format that will give you exactly what you are wanting. Here is how to do it using DATEPART and REPLICATE to pad the items out.

Code Snippet

SELECT REPLICATE('0', 2-LEN(DATEPART(mm, getdate()))) + CAST(DATEPART(mm, getdate()) AS VARCHAR(2)) + REPLICATE('0', 2-LEN(DATEPART(dd, getdate()))) + CAST(DATEPART(dd, getdate()) AS VARCHAR(2)) + CAST(DATEPART(YY, getdate()) AS VARCHAR(4)) + REPLICATE('0', 2-LEN(DATEPART(hh, getdate()))) + CAST(DATEPART(hh, getdate()) AS VARCHAR(2)) + REPLICATE('0', 2-LEN(DATEPART(mi, getdate()))) + CAST(DATEPART(mi, getdate()) AS VARCHAR(2))

|||

select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

This will get the format of hh:mms (108) and you just replace the : with nothing and take the last 2 characters off.

|||

Very good

I have mmddyyyyhhmm but a little error

I get 04/19/20071223

should be 04/19/2007 12:23

Thanks
Daniel

|||

Code Snippet

create function dbo.fn_FormatADate(

@.dtInput datetime

)

returns char(13)

as

begin

return replace(Replace( convert(varchar,@.dtInput,101) + left(convert(varchar,@.dtInput,108),5) ,'/',''),':','')

end

GO

select dbo.fn_FormatADate(getdate())

GO

|||

select convert(varchar(16), getdate(), 101)+' '+LEFT(convert(varchar, getdate(), 108),5)

That should give you the format that you illustrated in your "should be".

|||

Hi Ben,

Glad to see you here.

Regards,

Alejandro Mesa

|||

Just a thought...since we always know the len of the output, replicate is a bit more combersome than using "RIGHT"...

Code Block

declare @.TheDate datetime

set @.TheDate = GetDate()

select

right('0' + convert(varchar,datepart(Month,@.TheDate)), 2) --The Month

+ right('0' + convert(varchar,datepart(Day,@.TheDate)), 2) --The Day

+ convert(varchar,datepart(Year,@.TheDate)) --For the Year

+ right('0' + convert(varchar,datepart(hour,@.TheDate)), 2) --The hour

+ right('0' + convert(varchar,datepart(minute,@.TheDate)), 2) --The minute

date format transfer

Hi,
My data type of date field is smallint,
I select it and get 2004-08-16 00:00:00:000
How could I modify it as 93/08/16 (Chinese year)
(93 is Chiense year, 2004 - 1911 = 93)
Thanks!
AngiSELECT CONVERT(CHAR(8), DATEADD(YEAR, -11, column), 11) FROM table
http://www.aspfaq.com/
(Reverse address to reply.)
"Angi" <angi@.microsoft.com> wrote in message
news:O6NtrG6gEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> My data type of date field is smallint,
> I select it and get 2004-08-16 00:00:00:000
> How could I modify it as 93/08/16 (Chinese year)
> (93 is Chiense year, 2004 - 1911 = 93)
> Thanks!
> Angi
>

date format transfer

Hi,
My data type of date field is smallint,
I select it and get 2004-08-16 00:00:00:000
How could I modify it as 93/08/16 (Chinese year)
(93 is Chiense year, 2004 - 1911 = 93)
Thanks!
Angi
SELECT CONVERT(CHAR(8), DATEADD(YEAR, -11, column), 11) FROM table
http://www.aspfaq.com/
(Reverse address to reply.)
"Angi" <angi@.microsoft.com> wrote in message
news:O6NtrG6gEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> My data type of date field is smallint,
> I select it and get 2004-08-16 00:00:00:000
> How could I modify it as 93/08/16 (Chinese year)
> (93 is Chiense year, 2004 - 1911 = 93)
> Thanks!
> Angi
>