CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetime
user2418900
user2418900

Reputation: 79

Store DateTime as culture independent in SQL Server 2008

I currently have a challenge of storing a DateTime value in a NVarChar field so that it's culture independent.

I've read that you can convert the value to an int by using CONVERT(int, GETDATE(), 112) which should make it culture independent but the former statement doesn't store the time.

What is the industry standard of storing a DateTime as culture independent?

Please note that I can't use DateTime in my scenario. It must be NVarChar.

Alright, found the answer to my own question.

To convert a DateTime to it's binary(8) raw format: convert(binary(8), GETDATE())

I then store the value in a VARCHAR field as follows: CONVERT(VARCHAR(MAX), convert(binary(8), GETDATE()), 2)

To retrieve it back from the varchar field and convert it to DateTime: CONVERT(DateTime,CONVERT(binary(8), [TextField], 2))

As var as I'm concerned, this will store a DateTime as culture independent.

It seems like user Kaf has the best solution. I will rather use format 126 to convert it to text and then back to DateTime from text.

Thanks everyone and sorry for the confusion.

Upvotes: 0

Views: 843

Answers (3)

Philippe Grondier
Philippe Grondier

Reputation: 11138

I do not get this idea to store a date in a varchar field so that it is 'culture independant'. dateTime data type is culture independant. What is culture dependent is the way date values are displayed: 

MM/dd/YYYY
dd/MM/YYYY
YYYY-MM-DD
etc

But, if the display changes, the underlying value itself is still the same ... and this is why you can easily 'convert' dates from one format to another....

So, for the sake of simplicity, I do strongly advise you to switch to a culture-independant, datetime field. Otherwise any further use of this field's content (calculation, display, print out, etc) will be a real PITA ...

Upvotes: 0

Kaf
Kaf

Reputation: 33809

If you CANNOT store date as Datetime, you can use style 126 which gives ISO8601 format (yyyy-mm-ddThh:mi:ss.mmm (no spaces)). I think it is culture independent.

Fiddle demo

select convert(nvarchar(50),getdate(),126)

Best thing is to store Date as a DateTime/Date type.

Upvotes: 3

Szymon
Szymon

Reputation: 43023

You should use DATETIME or DATETIME2 data type to store date and time values. They are stored in binary format in the database and are culture independent.

You can read more on MSDN here: http://msdn.microsoft.com/en-us/library/ms187819(v=sql.100).aspx

More on how SQL Server stores the datetime values: "It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time." (from: http://sqlmag.com/sql-server/solving-datetime-mystery)

Upvotes: 2

Related Questions