problem with inserting or updating dates in mssql 2000

Hello.I've read many topics about this problem but i couldn't figure it out.
I use form where user must insert 2 dates using texboxes.-One is required and other is optional.
Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report.
Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?
question 2. What to do if user left optional texbox date empty.

I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)
and parameters @.date1, sqldbtype date =texbox1.textYou can get the date as;

DateTime myDate = DateTime.ParseExact(txtDate.Text, "dd.M.yyyy", System.Globalization.CultureInfo.InvariantCulture);

Now that you have your text box's date as a DateTime variable you can supply that to a SQL parameter query in any format you want, such as dd MMM yyyy

|||I understand that.
I wanna know how to change format of date wich is inserted as d.M.yyyy in texbox to yyyy.M.d because it is passed to sp parameter and it must be yyyy.M.d to do successful insert/update?

problem with inserting date in a datetime field

Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer.
That's my problem:
if the server is in english version, I have to insert date with this code:

DateTime.Today.ToString("MM/dd/yyyy")

instead if the server is in italian version, I have to insert date with this code:

DateTime.Today.ToString("dd/MM/yyyy")

Is there a way to insert a date in standard way, without knowing the server version?

bye and thanks in advanceI usually find that yyyy-mm-dd does the trick. But I can't swear it will always work. Actually what am I talking about, you should be using params and this won't be a problem.|||What if you insert the date in datetime format instead of converting it to string? Then maybe, just maybe sql server will recognize the format.|||>> What if you insert the date in datetime format instead of converting it to string? Then maybe, just maybe sql server will recognize the format.

You're on the road to SQL hell if you do that. What culture is your client, what culture is the database? Use params, there is hardly *ever* a reason not too almost always a problem with an alternative - unless you've written very well thought out DB classes.

Problem with external images

Hi!

I've got a real problem with inserting images into report. The problem is that I work with Reporting Services and SharePoint Portal. My employer wants to have CV created dynamically. Information for CV is taken from database under special account with read-only access. And photos of employees can be found with URL

http://corporate-site/Resources/Team/Name_surname.jpg (this string is also generated dynamically). When I preview report on my local machine, everything is Ok, I can see the images. But when deployed the images are not displayed. I have created an Unattended Report Processing Account to get photos. But if I want to use it I must change account for reading data from Data Source (in my case it is SharePointDB.rds) for “Credentials are not required”. And after it I can't get the information for CV.

Does someone know how to use one account for database and another one for getting external image? Or maybe there is another solution for this problem?

Thanks.

Yuliya

The configuration of the unattended account have changed as of February but there is also a solution of how to use url for your reports images, another option is to use stored procedure which means you have to use rectangle for the images and still another option is to store your images with your project. I did the later for a very complex report with just minor problems but it was accepted by the customer. The image control is just like visio shape so your can adjust your images as needed if you did not use the rectangle.

http://blog.toddtaylor.com/PermaLink,guid,b1517702-ad4b-48e1-a6e3-b736e8a982cb.aspx

http://technet.microsoft.com/en-us/library/ms156302.aspx

Problem with dates

I am having a problem inserting dates into SQL server 2000. I have an aspx page which contains a simple form.

This form data is inserted into the database on submission. On submission to SQL Server the date field is changed to 01/01/1900

This is the concatenated INSERT statement which is generated:

INSERT INTO Applicant (appTitle, appFname, appSname, appDOB, appEmail, appTel, appTelB, appAdd1, appAdd2, appAdd3, appAdd4, appPostcode, appPassword) VALUES ('Mr', 'John', 'Smith', 01/04/78, 'j.smith@.hello.com', '01258 698523', '', '1 The Smithy', 'Old Town', 'Oldville', '', 'OS6 8IU', 'Smith3969')

I have tried wrapping the date in ' ', but this doesn't work either.

Any help would be greatly appreciated

ThanksIt will not work without the quotes. Absent that, you are inserting 1/4 (0.25) /78 (a really small number).

I woulduse parameters instead, which isolates you from the strangeness with handling dates otherwise.|||Thank you for this.

I have almost got it working now using parameters. However I have noticed that dates submitted in the format DD/MM/YYYY or DD/MM/YY are being flipped in SQL Server to the incorrect format MM/DD/YYYY.

This is creating incorrect dates and in some cases is generating the following error:
"Arithmetic overflow error converting expression to data type datetime. The statement has been terminated."

Any ideas?|||Inside SQL Server, the dates (if DateTime type) are binary types, without any formatting applied to them. You can explicitly format the datetime retrieved from SQL Server to a useful representation using DateTime.ToString() (one of the overloads allows a format string that specifies the display format).|||Thats ok, so I can format them correctly in my application.

But what is causing the error:
"Arithmetic overflow error converting expression to data type datetime. The statement has been terminated."

It only occurs on some dates and not on others. This is really confusing me. I may have to resort to storing the dates as text, and then perform any calculations I require by changing them back to dates in my application.

If you have some ideas on this they would be greatfully received.

Thanks

Charlie|||You are likely sending a date to SQL Server (as a string) in a format it does not expect (DD/MM/YYYY rather than MM/DD/YYYY).

Show the code around when the error is occuring. On SQL Server, I would use SQL Profiler and see what is being sent to SQL Server.