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?
shorakConnect With a Mentor Commented:
try format(datefield, "yyyy/MM/dd")

Éric MoreauConnect With a Mentor Senior .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:
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.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.