RE: Sql Server Date problems

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Evan Nelson (EvanNelson_at_discussions.microsoft.com)
Date: 12/06/04 

Date: Mon, 6 Dec 2004 12:37:03 -0800

"SET DATEFORMAT dmy" should make it so that you don't have to use an
unambiguous date format in the SELECT statement.

It works for me so my bet would be that there is some sort of setting that
is overriding the SET DATEFORMAT. I'd really like to know what it is when
you find it.

"Alejandro Mesa" wrote:

> Try,
>
> SELECT project.id AS pid,* FROM project_date,project
> WHERE project_date.cms_id=project.cms_id AND project_date.dfrom<'20050601'
> AND project_date.dto>'20050601' ORDER BY strand,dfrom
>
>
>
> AMB
>
>
> "Mookoo" wrote:
>
> > Hi, this is one of those questions on dates. I'm completely stumped so any
> > help would be great. I'm querying the server with -
> >
> > SET DATEFORMAT dmy SELECT project.id AS pid,* FROM project_date,project
> > WHERE project_date.cms_id=project.cms_id AND project_date.dfrom<'1/6/2005'
> > AND project_date.dto>'1/6/2005' ORDER BY strand,dfrom
> >
> > the result set however returns for the 6th of january but I'm looking for
> > the 1st of June. If, however, I change the date to lets say 13/6/2005 -
> >
> > SET DATEFORMAT dmy SELECT project.id AS pid,* FROM project_date,project
> > WHERE project_date.cms_id=project.cms_id AND project_date.dfrom<'13/6/2005'
> > AND project_date.dto>'13/6/2005' ORDER BY strand,dfrom
> >
> >
> > then I get the correct date for the results - for the 13th of June 2005.
> > and so it goes for every month, anything below the 13th becomes read as mdy
> > whereas anything above the 12th gets read as dmy.
> >
> > This has me mystified so I'd be grateful for any advice/information
> >
> >

Relevant Pages

  • Re: Date and Time
    ... format i must put the directive (set dateformat) ... ... > absolutely no point to trying to store a datetime with any format. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: Determine 1st day of each month
    ... SET LANGUAGE US_ENGLISH ... SET DATEFORMAT DMY ... The only safe format in SQL Server, when converting from string to date, is ... Every other format will require care... ...
    (microsoft.public.sqlserver.programming)
  • Re: Date in SQL insert
    ... bear in mind that if you do not follow Andrea's advice to use ISO ... > you can use the SET DATEFORMAT xxx hint, ... > but please keep in mind the format SQL Server better accepts, ...
    (microsoft.public.sqlserver.msde)
  • Re: Date settings with SQL Server 2000
    ... Have a look into "SET DATEFORMAT" command in books online. ... DECLARE @var1 datetime ... > recognise the format from the SCADA system. ...
    (microsoft.public.sqlserver.programming)
  • Re: Does SET DATEFORMAT only set the format for the session?
    ... alter the current session handling of specific information. ... SET DATEFORMAT is one such SET Function which will chage the date format for ... > Can anyone confirm that if I use SET DATEFORMAT that it ... > will only change the date format for the SQL session and ...
    (microsoft.public.sqlserver.server)