More errors in convert function

In hijri calender any month can be 30 or 29 day
in convert function
if I write
select convert (datetime ,'29-12-1426,131) it is ok
this year 12 month is 30 days
but if I write
select convert (datetime ,'30-12-1426,131)
ther is an error

Thanks for any help

Tamer229,
There are many variations of the Islamic calendar. In some versions,
there are 29 days in the month Thou Alhajja in the year 1426, and in
others there are 30 days. There are only 29 days in this month
according to the Islamic calendar version used by Microsoft, which
is why you are getting an error when you try to convert the 30th day
of this month. In the Microsoft version, the western date January
30, 2006 is the first day of Muharram, 1427. In other versions of
the Islamic calendar, January 30, 2006 is the 30th day of Thou Alhajja,
1426.
See http://www.phys.uu.nl/~vgent/islam/islamyear_en.htm
for a calculator that shows several versions. The version used
by Microsoft is labeled "Ia [15, astronomical = “Kuwaiti algorithm”]"
Steve Kass
Drew University

Month/Year comparison against a date

Hi,

Is there a way to get the last day of a month, given a date in a datetime variable?

I have a stored procedure that accepts a datetime parameter. I need to find the last day of the month for that parameter value. For example, if the stored procedure is passed the datetime value, '6/13/2007', I need to be able to get from that '6/30/2007.'

Here's the bigger picture: The sp actually takes two datetime parameters (unfortunately, I don't have access to change the user interface). The sp needs to select records between the month/years of those dates, not including the first but including the second. For example, if the user specifies the following dates:

6/7/2006
7/15/2007

the sp needs to select all records that come after 6/30/2006 and on or before 7/31/2007.

I've tried this: ("ReportDate" is the name of the datetime field in a table in the sp and "@.StartDate" and "@.EndDate" are the datetime parameters in the sp)

Month(ReportDate) > Month(@.StartDate) And Year(ReportDate) >= Year(@.StartDate) And
Month(ReportDate) <= Month(@.EndDate) And Year(ReportDate) <= Year(@.EndDate)

But this returns fewer records than when I enter 6/30/2006 and 7/31/2007 as the parameters and just compare dates like this:

ReportDate > @.StartDate And ReportDate <= @.EndDate

Thank you.

Something like this:

Code Snippet

WHERE ( ReportDate >= dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )
)

You do NOT want to enclose ReportDate in a function since that will most likely result in not using indexing.

|||

WHERE ( ReportDate > dateadd( month, datediff( month, 0, @.StartDate ) , -1 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )
)

I just edited the previous reply to make it more close to your requirement..

|||

I don't think that is exactly 'right'. (I did make the assumption that the OP wanted ONLY JULY 2007 dates.)

Follow this code:

Code Snippet

DECLARE
@.StartDate datetime,
@.EndDate datetime

SELECT
@.StartDate = '06/7/2007',
@.EndDate = '7/15/2007'

-- Arnie's Variation
-- Starting at midnight, 7/1/2007, Ending at midnight, 8/1/2007
SELECT
'Arnie',
dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )

-- Mandip's Variation
-- Starting at midnight, 5/31/2007, ending at midnight, 8/1/2007
SELECT
'Mandip',
dateadd( month, datediff( month, 0, @.StartDate ) , -1 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )

-- OP Requested
-- the sp needs to select all records that come
-- after 6/30/2006 and on or before 7/31/2007

-- -
Arnie 2007-07-01 00:00:00.000 2007-08-01 00:00:00.000

Mandip 2007-05-31 00:00:00.000 2007-08-01 00:00:00.000

Month Format

Hello Experts!

I am trying to format the datetime field in my grouping. Right now I am using the expression =Month(Fields!TestDateTime.Value) in the edit group properties and it returns (for example) May 01 for the group. I would like it to show up as May 2007 instead. Would someone be kind enough to post what the correct expression should be?

Thanks,

Clint

Not sure if there is a better way, but this works:

Code Snippet

=MonthName(Month(Fields!DateOrderCreated.Value)) + " " + format(Fields!DateOrderCreated.Value,"yyyy")

|||Thanks, but that also does not work. It returns the same "May 01".|||What datatype is the field you are formatting?|||It is a datetime data typ|||

If it is a datetime data type, then you can use Format() on it using any of the standard formatting codes, like this:

Code Snippet

=Format(myDataField, "Mon yyyy")

|||Hmmmm still not working. What area should the code be put in to? I have tried the group properties, the field property value, and the custom format are of the field to no avail. It will either error out or return the same "May 01". I am working from the report layout area of the reporting services.|||LOL. Found the answer. Simply in the Format area, I just put a "Y" in the custom format field. thanks so much for your help though.

Month Days

How can you find the days in a particular month in crystal reports v10.
(e.g. as in vb.net MYDAYS_MONTH = DateTime.DaysInMonth({?Year}, {?Month}))Try Day(Datefield)

Month data

HI,
I have a datetime column and it has 2 years of data. How
do I select the data that is only belong to the current
month (Whether it is the beginning/middle/end of the
month) '
Thanks for any help.There's a couple of different options for doing this, but this one should do
the trick:
select <insert your column list here>
from table1
where month(YourDate) = month(current_timestamp)
and year(YourDate) = year(current_timestamp)
--Brian
(Please reply to the newsgroups only.)
"Chris" <anonymous@.discussions.microsoft.com> wrote in message
news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
> HI,
> I have a datetime column and it has 2 years of data. How
> do I select the data that is only belong to the current
> month (Whether it is the beginning/middle/end of the
> month) '
> Thanks for any help.|||Thanks.......
I found it
Select * from mytable
where column >= cast(month(getdate()) as varchar(2))
+ '/01/' + cast(year(getdate()) as varchar(4))

>--Original Message--
>There's a couple of different options for doing this, but
this one should do
>the trick:
>select <insert your column list here>
> from table1
> where month(YourDate) = month(current_timestamp)
> and year(YourDate) = year(current_timestamp)
>--
>--Brian
>(Please reply to the newsgroups only.)
>
>"Chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
>
>.
>sql

Month data

HI,
I have a datetime column and it has 2 years of data. How
do I select the data that is only belong to the current
month (Whether it is the beginning/middle/end of the
month) '
Thanks for any help.There's a couple of different options for doing this, but this one should do
the trick:
select <insert your column list here>
from table1
where month(YourDate) = month(current_timestamp)
and year(YourDate) = year(current_timestamp)
--
--Brian
(Please reply to the newsgroups only.)
"Chris" <anonymous@.discussions.microsoft.com> wrote in message
news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
> HI,
> I have a datetime column and it has 2 years of data. How
> do I select the data that is only belong to the current
> month (Whether it is the beginning/middle/end of the
> month) '
> Thanks for any help.|||Thanks.......
I found it
Select * from mytable
where column >= cast(month(getdate()) as varchar(2))
+ '/01/' + cast(year(getdate()) as varchar(4))
>--Original Message--
>There's a couple of different options for doing this, but
this one should do
>the trick:
>select <insert your column list here>
> from table1
> where month(YourDate) = month(current_timestamp)
> and year(YourDate) = year(current_timestamp)
>--
>--Brian
>(Please reply to the newsgroups only.)
>
>"Chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5e7601c40092$e4c66910$a101280a@.phx.gbl...
>> HI,
>> I have a datetime column and it has 2 years of data. How
>> do I select the data that is only belong to the current
>> month (Whether it is the beginning/middle/end of the
>> month) '
>> Thanks for any help.
>
>.
>

month ('jan','feb',...) string to date conversion fails

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]]

Error: The component "Derived Column" failed because error code

0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

srem wrote:

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]] Error: The component "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

Its not an ideal situation but if you want to achieve this in a single derived column component what you will need to do is build some nested conditional operators. Its a bit of a fraught process to begin with but once you get the hang of it it isn't too bad.

I hope Microsoft give us the ability to extend the expression language in the next version by allowing us to build our own custom expression functions.

-Jamie

|||I suppose you could build a quick lookup table using a simple stored procedure. Then all you'd have to do is join on that "Jan 02 2005" column to get you a real date field in return.

Might even work better than doing all of the CASTs and conditional logic tests.|||

If you know exactly what the month strngs are, and they are all 3 chars long, you could do something like the following to get month number:

(FINDSTRING(month, "JANFEBMARAPR....DEC",1) + 2 ) / 3

If there are multiple options, or the lengths are irregular you could still do something similar, but it might not be worth it anymore over the nested conditionals.