October 12, 2012 at 1:38 am
Suppose we are entering data of 2 diff countires in our table. one is US ans another is Uk so both their time formats so how can we manage the insertion od data for diff date formats.
When you (in the United States) run a query to insert U.S. orders into the system, you use the date format mm/dd/yyyy (Dec 19, 2003). When the UK accountant sends you their INSERT statements, they send the data in dd/mm/yyyy format (19 Dec, 2003). The UK orders that look like 19/12/2003 and some fail upon insert with the following error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
I heard about the SET language function and used that like this..
declare @date datetime
set @date=GETDATE()
set language us_english
SELECT DATEADD(d,-1,@date) as US
set language British
SELECT DATEADD(d,-1,@date) as UK
When i run this query i got the following message
Changed language setting to us_english.
(1 row(s) affected)
Changed language setting to British.
(1 row(s) affected)
results are:
US
2012-10-11 13:07:17.760
UK
2012-10-11 13:07:17.760
how can see the conversion as both are displaying in same format here..
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 12, 2012 at 3:02 am
SET DATEFORMAT (which is implicitly set by using SET LANGUAGE) controls the interpretation of character strings into date datatypes.
You can see the effect here:
set dateformat mdy --(which would be set by using SET LANGUAGE us_english)
select cast('10/13/2012' as datetime)
set dateformat dmy --(from SET LANGUAGE British)
select cast('10/13/2012' as datetime)
In your example, your were using the @date variable of datetime datatype, so set dateformat/language would have no effect.
For the problem you're having, use the relevant set dateformat statement for the separate UK/US inserts, or see if there's a way to get the datetime data in ISO 8601 format (YYYY-MM-DDThh:mm:ss), where the values of dateformat & language are not considered.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply