Dates

• garethl

  Newbie

  Points: 1

  More actions

  November 18, 2005 at 7:27 am

  #169755

  I have encountered a problem where sqlserver seems to switch dates around a little. it works fine on client site but no on our development server

   

  If I enter the date 10/11/2005 and do an update it is stored as 11/10/2005. if I then enter 28/10/2005 it is ok and stays the same. If there was a problem with British or American dates I could understand and get a problem for the 28/10  if switched to 10/28 but it accepts it. Currently the date format is set to dmy and the language is British for server and users.  Can anyone figure this out.

  Many thanks

• Jo Pattyn

  SSC-Dedicated

  Points: 32478

  More actions

  November 19, 2005 at 6:07 am

  #604790

  * are your insert and update 2 different actions/inserts. Could it be that one connections uses the default dateformat (us_english m/d/y)

  the only things that really helps is having the client updating it in the universal date format. years, month, day.

   

  Syntax

  SET DATEFORMAT { format | @format_var }

  Arguments

  format | @format_var

  Is the order of the dateparts. Can be either Unicode or <A class=PopLink id=PopUp title="View definition" href="mk:@MSITStore:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_set-set_052s.htm#sql:double_byte_character_set__dbcs_">DBCS converted to Unicode. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym. The U.S. English default is mdy.

  Remarks

  This setting is used only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values.

  The setting of SET DATEFORMAT is set at execute or run time and not at parse time.

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  November 19, 2005 at 8:57 am

  #604797

  garethl: were you aware that DATEFORMAT only controls the conversion of characters to dates but does not control the conversion of dates to characters that you see on a client such as Query Analyzer?

  If you are using Query Analyzer, you can set the display formats using the Regional Settings by using the menu items "Tools" --> "Options', then pick the "connections" tab and set "Use Regional Settings ..."

  Here is a test case, which you can run after setting the QA option and then setting the Regional Options first to "English (United States)" and a second time with Regional Options" of "English (United Kingdom)"

  use tempdb

  go

  create table #dates

  (SetDateFormat varchar(255) not null

  ,DateAsChar varchar(255) not null

  ,MyDatedatetime not null

  )

  go

  set dateformat mdy

  go

  insert into #dates

  (SetDateFormat , DateAsChar ,MyDate)

  select 'mdy','10/11/2005','10/11/2005' union all

  select 'mdy','10/28/2005','10/28/2005'

  go

  set dateformat dmy

  go

  insert into #dates

  (SetDateFormat , DateAsChar ,MyDate)

  select 'dmy','11/10/2005','11/10/2005' union all

  select 'dmy','28/10/2005','28/10/2005'

  go

  select *

  , Year(MyDate) as MyDateYear

  , Month(MyDate) as MyDateMonth

  , Day(MyDate) as MyDateDay

  from #dates

  go

  drop table #dates

  SQL = Scarcely Qualifies as a Language