February 22, 2007 at 4:10 am
Hi,
colleagues in The Uk have sent an unload file for us to load in to our database.
however the date is the Uk Format dd/mm/yyyy and the database is in the us format mm/dd/yyyy.
what is the best way to deal with this? change the database(and how would i do this?)? or the file?
Thanks
February 22, 2007 at 7:02 am
Changing the database will make no difference if, as you should be, storing the dates as datetime.
The way the date is read dd/mm/yyyy or mm/dd/yyyy is dependant on the language of the login used to load the data.
If this a one off load then change the data.
If it will be regular then use a login with the correct language for the data
or check out
SET DATEFORMAT in BOL (this may not work depending on how you load the data)
Note that dates in the form yyyymmdd will always be processed correctly regardless of login etc
Note that if you are importing this using DTS than you could change the column transformation to meet the criteria
Far away is close at hand in the images of elsewhere.
Anon.
February 23, 2007 at 8:32 am
As I understand it, you have some text file with dates and want to import it into datetime column correctly...
Look into the "CAST and CONVERT" topic in BOL. You can do a lot using CONVERT with the appropriate style (style can be used both when converting from datetime to character and from character to datetime).
See this example :
DECLARE @yourdate varchar(10)
SET @yourdate = '21/01/2007'
SELECT CONVERT(DATETIME, @yourdate, 103) as 'UK'
--SELECT CONVERT(DATETIME, @yourdate, 101) as 'US' /*fails*/
SET @yourdate = '01/21/2007'
--SELECT CONVERT(DATETIME, @yourdate, 103) as 'UK' /*fails*/
SELECT CONVERT(DATETIME, @yourdate, 101) as 'US'
That is, you can e.g. import the data from text file into SQLS staging table (with varchar columns) and then use convert when inserting into the actual data table as datetime.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply