Date format issue

[gustaf111]

Hello, I have date format issue.

The date: 25 th of December 2011

In spain they write the date: 25/12/2011
In USA they write the date: 12/25/2011
In Sweden they write the date: 2011-12-25

They problems occur if people change the region settings. I write the date to an MS SQL Server database and the value 25/12/2011 is not a valid date.

Is is possible in VBA in excel to convert the date to as string variable with the "The international standard date notation" YYYY-MM-DD that I can write to the SQL server.

Cheers Gustaf

 

[Andrzejek]

I write the date to an MS SQL Server database and the value 25/12/2011 is not a valid date.

I don't think that's right - you can use any valid Date to keep in MS SQL Server database, as long as the field in your table is defined as Date.
Database does not keep the Date in any format, it is just a number. When you need the data as Date, you (or any tools you use) convert the data to be displayed as Date you can recognize


Have fun.

---- Andy

 

[gustaf111]

Hello Andrzejek,

I have tried .... in MS SQL Server 2008 does not take 25/12/2011 as a date ...

Cheers Gustaf

 

[PHV]

I write the date to an MS SQL Server database
How ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gustaf111]

Like this:

INSERT INTO Test (Datum) VALUES ('25/12/2011') Whch does not work:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

 

[SkipVought]

I would use the UNAMBIGUOUS yyyy/mm/dd structure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

And this ?
INSERT INTO Test (Datum) VALUES ('2011-12-25')

Use the Format function if you buid the SQL code in VBA.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

[tt]INSERT INTO Test (Datum) VALUES ('25/12/2011')[/tt]
Does not work because SQL Server cannot convert your String to Date.

In Oracle, I do:
[tt]INSERT INTO Test (Datum) VALUES ([blue]TO_DATE('[/blue]25/12/2011[blue]', 'DD/MM/YYYY')[/blue])[/tt]
I am sure there is something like TO_DATE in SQL Server, too.

Have fun.

---- Andy

 

[gustaf111]

Thanks all. This will do the trick for me:

Test = Format(mSelectedDate, "yyyy-mm-dd")