format date val to insert into sql

Hi, i am using 2005
I have a date value in a variable declared as date, I am then trying to insert it into sql table. on one my dev machine the value in my SQL insert statement is 2008/06/30, but on my dev server its being formatted to 30/08/2008. I have checked the regional settings on both machines and they are set to dd/MM/yyyy
So my question is, is there away for me then to "force" the date to yyyy/MM/dd in my insert statement so then its in the correct format on my dev server?
My SQL field is defined as a DateTime datatype

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
if your field datatype is DateTime (as you said) then your date are stored correctly.

the format of the date is not forced by the server but by the computer that runs the query. if you want to force the format, you can always do something like this:
try format(datefield, "yyyy/MM/dd")


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You can use the format command:

Dim stdDate As String = Format(Now, "yyyy/MM/dd")
Note that the format identifier *is* case sensitive. 'MM' is 2 character representation of the month, 'mm' , though, is minutes.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

CraigLazarAuthor Commented:
Hi emoreau,
ok i thought as much, however on my dev server the sql statement looks like this when passed to sql
INSERT INTO tblLoadSet (LoadSetID,ManCoID,QED,Processed,FileCount,BatchID)  VALUES ('a1d5bb7e-41ac-4b97-87a0-771cc21f2389','CORO','30/06/2008','No',1,4);

On my dev machine its
INSERT INTO tblLoadSet (LoadSetID,ManCoID,QED,Processed,FileCount,BatchID)  VALUES ('a1d5bb7e-41ac-4b97-87a0-771cc21f2389','CORO','2008/06/30','No',1,4);

So the date is swopped around, so sql generates an error on the first sql statement

to shorak,
I tried that and it seems to have inserted into the table on the dev server, however using this format, will it swop the day and month around with a date like 7th of June to the 6th of July?

thanks allot
Éric MoreauSenior .Net ConsultantCommented:
then you have to modify your code. what is creating this INSERT statement in your code? this is the place where you have to format the date correctly using YourDateVariable.ToString("yyyy/MM/dd")
CraigLazarAuthor Commented:
ok i am building a sql string set to to a string valriable then passing that to a function which then executes the sql statement using the sql command object

i have adjusted it to the following to make sure that the month is in long format and to hopefully make sure that the day and month do not get swopped around
SqlInsert = "INSERT INTO tblLoadSet (LoadSetID,ManCoID,QED,Processed,FileCount,BatchID)  VALUES ('" & LoadSETID_GUID.ToString() & "','" & strData(1) & "','" & Format(CurQEDate, "yyyy/MMMM/dd") & "','No'," & FileCount & "," & strData(2) & ");"
Éric MoreauSenior .Net ConsultantCommented:
so is it working now?

BTW, you shouldn't use Format(CurQEDate, "yyyy/MMMM/dd") just in case you run your query against a non-english installation of SQL Server or a non-english client.
Hi Craig

The format function should not swap around no matter what maching you use it on because the function is forcing the date out in the format specified.

As to whether this will be affected by how the data is stored in the table, I am going to make a big assumption here since my technical knowledge of SQL server is limited. The date format will probably depend on how the data was entered into the table in the first place. if all the data was entered in yyyy-mm-dd format then it would not allow a user to enter yyyy-dd-mm hence all the source data in the table should be in this fixed format. When using the format function, It will know how the original date is stored in the table and hence know which numbers are the month and which numbers are the day.

But since your using the long month name format then you should be okay.

If you're using a Date type then I tend to call the .ToLongDateString method before I pass it into SQL Server as text - that makes it write it out verbose (e.g 02/02/08 would be output as '2 February 2008') and then SQL doesn't get the chance to get it messed up, I'm not sure this is a technique most experts would recommend but unless I'm using a parameter of a datetime type then I find it the most convenient way of doing it.

If you're using a SQLCommand object then you can always do something like this:
Dim dtmDateValue as date="02 February 2008"
Dim oCmd as new sqlcommand("Insert into tblTest (testitem1,aDateColumn) values ('testitem1',@YourDateParameter)")
If you're using a .net date type and passing it to a SQLCommand date type parameter then I don't think it can get it wrong.
CraigLazarAuthor Commented:
ok managed to sort it out, for Inserting into MSSQL i am using yyyy/MMM/dd and then for Inserting into MySQL u have to drop the 1 m to yyyy/MM/dd other wise MySQL does not store the date at all

thanks allot for the help

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.