Date format

Question

Date format

soulchyld21

Mr or Mrs. 500

Points: 524
More actions
January 31, 2012 at 7:50 am

#252135

I have a date field in one of my tables and am having issues with the format, some records are not being inserted into the table because of this, I am trying to insert UK format dates, eg 12/1/2011 for the 12th of Jan, instead of 1 Dec (US format) any ideas on how I can fix this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

soulchyld21 (1/31/2012)
I have a date field in one of my tables and am having issues with the format, some records are not being inserted into the table because of this, I am trying to insert UK format dates, eg 12/1/2011 for the 12th of Jan, instead of 1 Dec (US format) any ideas on how I can fix this?

Use ISO standards, so instead of inserting them in UK or US format insert them as "yyyy-MM-dd".

This will mean either a change to your import/app, but it's worth it in the long run.

If you can't change that, then you'll have to consider a staging the data as a string then converting to DATETIME via either a CLR or T-SQL routine.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

upstart Ten Centuries Points: 1255 More actions · Answer 2

You can try this:

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

--31/01/2012

More SQL Time Formatting Examples:

Date Formatting

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 3

You may also be able to change the default date format for your session so that it correctly interprets the pieces.

http://msdn.microsoft.com/en-us/library/ms189491.aspx

jcb SSCertifiable Points: 6574 More actions · Answer 4

The records seing insert are wrong since only dates like '30-01-2012' ill fail, poping a error message.

Your database language most probabily is seted to US-en (default value).

You can change instance, database language but the data sent to the database dependens on a lot of factors.

If its a application sending data, if its was developed prone to internationalization issues even different users in different machines ill sent data in different formats.

Anyway, if you are sending strings to the database aways use ANSI formats like Celko stated since they are "no language/culture dependent".

To read data from the database at a particular format use Convert like upstart said.