datetime to dd/MM/yyyy for inserting into DB

hi there, i have a calendar that i put into a string ilke this

string str = Calendar1.SelectedDate.ToShortDateString();

the result is dd/mm/yyyy date which is great, but for inserting into my DB (MSSQL) it needs to be a datetime field, however when i convert it

Datetime dtDate = Convert.ToDateTime(str);

it takes my date and adds 00:00:00 onto the end and this is not what i want! i just want the dd/mm/yyy how do i do this, it has to be simple but i have been searching for hours and cant find anything, i am using ASP.NET 2 and C#

Thanks

The datetime object MUST contain the time. You don't have any problem, if you display only the date, you are OK.

|||

ah rite ok, but when i try to get the date back from the db where its stored as e.g 15/06/2007 the datetime string passes it as 15/06/2007 00:00:00 and so it wont reconise it, is there any other way to pass it so that it can compare?

|||

Hi,

Actually, when recieve the datetime as string from the database, you may convert it to DateTime type and use ToShortDataString method, in this way,you can get the datetime string without time part. See the sample below:

string dt_s = ds.Tables[0].Rows[0][0].ToString();// Get the datetime from db. DateTime dt = Convert.ToDateTime(dt_s);// Convert it to DateTime type.this.Label1.Text = dt.ToShortDateString();// Get the short date

Thanks.

DateTime Parameter need yyyy-mm-dd format

I have a parm defined as DateTime in RS2005 (so you see the calendar control
for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
display yyyy-mm-dd as it was when we had date defined as string rather than
DateTime. I went into regional settings on my pc and changed the date
setting in my regional options to display date as yyyy-MM-dd and it still
didnt seem to work. Can someone direct me? ThanksHi.
You have a language setting in the reports properties.
The default setting for language is "English (United States)". You can
change that either to what the user have choosen in IE by chosing "Default"
or what you want it to be. If you need yyyy-MM-dd you just change to your
country's setting.
You can override this setting for certain textboxes if you like. Textboxes
have their own language setting.
Kind regards
Maran
*********************
"MJT" wrote:
> I have a parm defined as DateTime in RS2005 (so you see the calendar control
> for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> display yyyy-mm-dd as it was when we had date defined as string rather than
> DateTime. I went into regional settings on my pc and changed the date
> setting in my regional options to display date as yyyy-MM-dd and it still
> didnt seem to work. Can someone direct me? Thanks|||Thank you Maran,
I had changed my regional settings in control panel prior to posting my
message, but I didnt realize that they wouldnt take effect until I closed out
of VS and re-entered. Which brings another question to mind. If we want the
date displayed as yyyy-MM-dd format can that be accomplished solely by
setting to "English(United States)"? Because when I noticed the problem
initially, I had my windows settings for date format as MM-dd-yyyy and my
language was English(United States) and that seemed to be why I saw the date
format as MM-dd-yyyy when I changed my parameter to DateTime format. I am
wondering if the users have their date format in windows set other than
MM-dd-yyyy if just making the language English(United States) will matter at
all now. Since that was the way my settings were originally. Is there a way
to enforce a date time format? I realize that each textbox has a format
parameter, but this textbox that referenced the date parameter had other code
in it which was being translated into multiple languages in a code block - so
I cant use the format property on the textbox itself - I would have to put it
in the code block somehow. Thanks again for your reply.
"Maran" wrote:
> Hi.
> You have a language setting in the reports properties.
> The default setting for language is "English (United States)". You can
> change that either to what the user have choosen in IE by chosing "Default"
> or what you want it to be. If you need yyyy-MM-dd you just change to your
> country's setting.
> You can override this setting for certain textboxes if you like. Textboxes
> have their own language setting.
> Kind regards
> Maran
> *********************
> "MJT" wrote:
> > I have a parm defined as DateTime in RS2005 (so you see the calendar control
> > for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> > display yyyy-mm-dd as it was when we had date defined as string rather than
> > DateTime. I went into regional settings on my pc and changed the date
> > setting in my regional options to display date as yyyy-MM-dd and it still
> > didnt seem to work. Can someone direct me? Thanks|||I handled this by adding the format to my custom code. Thanks.
"MJT" wrote:
> Thank you Maran,
> I had changed my regional settings in control panel prior to posting my
> message, but I didnt realize that they wouldnt take effect until I closed out
> of VS and re-entered. Which brings another question to mind. If we want the
> date displayed as yyyy-MM-dd format can that be accomplished solely by
> setting to "English(United States)"? Because when I noticed the problem
> initially, I had my windows settings for date format as MM-dd-yyyy and my
> language was English(United States) and that seemed to be why I saw the date
> format as MM-dd-yyyy when I changed my parameter to DateTime format. I am
> wondering if the users have their date format in windows set other than
> MM-dd-yyyy if just making the language English(United States) will matter at
> all now. Since that was the way my settings were originally. Is there a way
> to enforce a date time format? I realize that each textbox has a format
> parameter, but this textbox that referenced the date parameter had other code
> in it which was being translated into multiple languages in a code block - so
> I cant use the format property on the textbox itself - I would have to put it
> in the code block somehow. Thanks again for your reply.
> "Maran" wrote:
> > Hi.
> >
> > You have a language setting in the reports properties.
> >
> > The default setting for language is "English (United States)". You can
> > change that either to what the user have choosen in IE by chosing "Default"
> > or what you want it to be. If you need yyyy-MM-dd you just change to your
> > country's setting.
> >
> > You can override this setting for certain textboxes if you like. Textboxes
> > have their own language setting.
> >
> > Kind regards
> > Maran
> >
> > *********************
> >
> > "MJT" wrote:
> >
> > > I have a parm defined as DateTime in RS2005 (so you see the calendar control
> > > for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> > > display yyyy-mm-dd as it was when we had date defined as string rather than
> > > DateTime. I went into regional settings on my pc and changed the date
> > > setting in my regional options to display date as yyyy-MM-dd and it still
> > > didnt seem to work. Can someone direct me? Thanks

DateTime parameter in report

Hi All:

I wanna have DateTime calendar filter in report, so i create a parameter call EndDate which defualt vavlue today, now i have problem how can i get the value from Date Time calendar after every time user selected new date, then i need to pass this date time value to subreport.

Thanks

Nick

Not sure I understand your request well. Are you asking how to pass a parameter value from a main report to a subreport? You may want to read the following BOL page: http://msdn2.microsoft.com/en-us/library/aa337167.aspx. In particular, you can pass parameter values to subreports by editing the subreport properties in the main report and setting the parameter expression to =Parameters!EndDate.Value

-- Robert

|||

hi Robert.

Thanks for your reply, now i got another error after i set up the date time parameter in subreports.

In the property of sub report , i use =Parameters!WeekEnding.Value, weekEnding is date time type parameter both in main and sub report.

now i run the report. i got error like below:

An error occured during local report processing. The value provided for the report parameter "WeekEnding" is not valid for it's type.

I have no ideas about this error message. it works fine if i change WeekEnding type to string both in main and sub report. is that anything i missing out here?

thanks

Nick

Datetime Parameter Format

Hi,all
I have a datetime parameter,
I use calendar to select value,but I want to format it as "yyyy-MM"
Any suggest?Can I use expression,how to ?
Kevin ChuJust get the value in as datetime format and use
format(Fields!xxx.Value,"yyyy-MM")
--
Tom Stude
"Kevin" wrote:
> Hi,all
> I have a datetime parameter,
> I use calendar to select value,but I want to format it as "yyyy-MM"
> Any suggest?Can I use expression,how to ?
> Kevin Chu
>|||=?Utf-8?B?VG9t?= <membership@.stude.no> wrote in news:ADC758F7-F6A7-4F56-
8C5C-1BAD643ABF30@.microsoft.com:
I want to show "yyyy-MM" format in preview,not get the value
> Just get the value in as datetime format and use
> format(Fields!xxx.Value,"yyyy-MM")
>

DateTime formating.

I am trying to query a calendar table that has the [start] datetime of an event and then a [title] of the event. I am trying to display them as;

* 8:00 AM Get to work
* Independance day

some of the events have a start time and some only have a date. the ones that do not have a start time have a start time of 12:00:00 How do I query the two fields and only show the start time & title of the events that have a start time and just the title for the ones that do not have a start time..

I've tried this...

selectEvent=(CONVERT(CHAR(8),start,8)+' '+ title)fromCalendarwhereCONVERT(CHAR(8),start,8)<>'12:00:00'

It does display the start and event title but only for the ones that have a start time. And how do I get the 8:00 AM format for the time?

Thanks for any help.

Mark

Perhaps something like this might work:

SELECTCASEWHEN(datevalisNOTNULLAND dateval!='')THEN REPLACE(REPLACE(RIGHT(CONVERT(varchar,dateval,100),7),'P',' P'),'A',' A')ELSE''END +' ' + titleFROM yourTableWHERE

|||

--Try somthing like this:

SELECTCASEWHENCONVERT(varchar(8),date,108)='12:00:00'THEN'*'+title --Or CONVERT(varchar(8),date,108)='00:00:00'

ELSE'*'+RIGHT(CONVERT(varchar(19),date,100), 7)+' '+ title

ENDASEvent

FROM calendarwithTitle

|||Perfect thanks alot that took care of it for me!

datetime

I use Calendar asp.net(c#).It gives me Calendar.SelectedDate.Date is string
I convert it to datetime with Convert.toDateTime but Sql server give me
error...
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated.
How can I solve the error and add date to sql server?
Thanks...You're not using a proper datetime format for SQL Server:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Selen" <skiyanc@.yahoo.com> wrote in message
news:OLR5VrmCEHA.2656@.TK2MSFTNGP12.phx.gbl...
> I use Calendar asp.net(c#).It gives me Calendar.SelectedDate.Date is
string
> I convert it to datetime with Convert.toDateTime but Sql server give me
> error...
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value. The statement has been terminated.
> How can I solve the error and add date to sql server?
> Thanks...
>|||two options to go forwards
1. Use a neutral date format. These are the ISO and ISO 8601 standard. These
have the CONVERT sytle numbers of 112 and 126 respectively. Check the SQL S
erver documentation on the CONVERT function for details.
2. Change the language of the SQL Server login used to match the string that
is being sent down.

Dates and Parameters

I have created a report with parameters, I select the dates (01/05/2007) to
(01/07/07) using the calendar and then click view report the dates then reset
to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
are displayed in the report are returning the correct values. When you click
view report again without amending the dates the data is returned and the
parameters values as displayed in the report are incorrect. Please help.On Jul 6, 6:28 am, Changing Dates in reports & parameters <Changing
Dates in reports & paramet...@.discussions.microsoft.com> wrote:
> I have created a report with parameters, I select the dates (01/05/2007) to
> (01/07/07) using the calendar and then click view report the dates then reset
> to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
> are displayed in the report are returning the correct values. When you click
> view report again without amending the dates the data is returned and the
> parameters values as displayed in the report are incorrect. Please help.
You most likely want to check/change your computer's/server's regional
settings. This can be done through the control panel. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Checked regional settings on both the Server and my local pc and both are
British English.
thanks
lisa
"Changing Dates in reports & parameters" wrote:
> I have created a report with parameters, I select the dates (01/05/2007) to
> (01/07/07) using the calendar and then click view report the dates then reset
> to (05/01/07) to (07/01/07) and no data is displayed yet the parameters which
> are displayed in the report are returning the correct values. When you click
> view report again without amending the dates the data is returned and the
> parameters values as displayed in the report are incorrect. Please help.

DATEDIFF

I am trying to get the date difference from a given date and work out the
number of calendar days, which is fine and then work out the number of
saturdays and sundays seperately but I think I'm only getting wends.
The SQL is:
UPDATE not_swcch
SET not_swcch.Calendardays =
DATEDIFF(day, [Entry Date], getdate()),
not_swcch.Saturdays =
DATEDIFF("ww", [Entry Date], getdate()),
not_swcch.Sundays =
DATEDIFF("ww", [Entry Date], getdate())
from not_swcch
How can I differentiate Saturday and Sunday?
ThankyouProbably the easiest and best solution to this problem is to use a
calendar table.
http://www.aspfaq.com/show.asp?id=2519
Stu|||try this.. you can check it and implement it in your update staement. Hope
this helps.
declare @.a datetime, @.b datetime
--you set the number of daysto subtract to test
set @.a = dateadd(dd,-102,getdate())
set @.b = dateadd(dd,3,getdate())
-- find out what date range you have set
select @.a as [start date],@.b as [end date],
datename(dw,@.a) as start_dow,datepart(dw,@.a) as start, datename(dw,@.b) as
end_dow,datepart(dw,@.b) as [end]
select datediff(ww,@.a,@.b) as [no of ws difference],datediff(dd,@.a,@.b) as
[no of days difference]
select datediff(ww,@.a,@.b) + case when datename(dw,@.a) = 'sunday' then 1 else
0 end as [no of sundays],
datediff(ww,@.a,@.b) + case when datename(dw,@.b) = 'saturday' then 1 else 0
end as [no of saturdays]

date/time format with Format_string is not working

with member x as [Date].[Calendar].CurrentMember.Name, format_string = 'mm/yyyy'

select x on 0 ,

[Date].[Calendar].[Date].AllMembers on 1

from [Adventure Works]

When I run this, I get the same value as CurrentMember.name for 'x'. Any ideas?

The .Name is not of type date - instead use .MemberValue:

with member x as [Date].[Calendar].MemberValue, format_string = 'mm/yyyy'

select x on 0 ,

[Date].[Calendar].[Date].Members on 1

from [Adventure Works]