Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 11th, 2005, 09:30 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date problems

Hi guys,

I'm a bit of a newbie to SQL so i was hoping someone could help!
The server is set to uk date format. However if i run this code:

select * from events where StartDate < 30/06/2005

I get nothing when i should get results. Thinking something is set to american date format somewhere i tried

select * from events where StartDate < 06/30/2005

Still nothing. However if i change it to:

select * from events where StartDate > 30/06/2005

All results are returned from the database including the ones with a date less than that!!? I get the same results whether i use Query Analyser or Enterprise Manager to run these queries.

Any help greatly appreciated

Steve
  #2 (permalink)  
Old July 11th, 2005, 10:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Try quoting your dates, as, e.g.:

Code:
    select * from events where StartDate < '30/06/2005'
DateTime constant values are represented as character strings whose value is interpreted according to the current language settings.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #3 (permalink)  
Old July 11th, 2005, 10:49 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: India.
Posts: 224
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to ashu_from_india Send a message via Yahoo to ashu_from_india
Default

hi StevieB,

whenever u r comparing Date field in SQL Server, u shud always use yyyy/MM/dd format

e.g., select * from events where StartDate < '2005/01/31'

it works well for all formats

Ashu


  #4 (permalink)  
Old July 11th, 2005, 11:05 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Jeff,

Thanks for your help:

select * from events where StartDate < '30/06/2005'

didn't work, but

select * from events where StartDate < '06/30/2005' did!!

This is really confusing now - the front end of the website interrogates the databases using UK date format and works fine but when i use enterprise manager i have to use American!!?

I checked the servers regional settings and everythings UK??
  #5 (permalink)  
Old July 11th, 2005, 11:13 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Ashu,

Thanks a lot for that - i'll know for next time!

Cheers
Steve
  #6 (permalink)  
Old July 11th, 2005, 11:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:
...[you should]...always use yyyy/MM/dd format
IMO, it is better to use the ISO format for datetime character strings. This format takes the form:
Code:
 
    yyyy-mm-dd Thh:mm:ss:mmm
(Note that constant "T" in there if you include a time).

This is unambiguous no matter what your language settings are on the server, in the tools you are using to execute a query and/or in the connection used to access the server (which, as you've found out, are not necessarily consistent with one another). Other formats may work, but are not necessarily guaranteed to give consistent results from language setting to setting.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #7 (permalink)  
Old July 11th, 2005, 12:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The ANSI date format 'yyyy.mm.dd' also seems to avoid the ambiguities of mm/dd/yyyy vs. dd/mm/yyyy.

Rand


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL and ISO date problems CallumBeveridge SQL Server 2000 6 August 17th, 2007 02:14 PM
copy date values between date controls Alcapone Javascript How-To 1 April 13th, 2006 03:13 AM
Date problems RobCarter Access VBA 3 November 10th, 2005 10:39 AM
DTS Import ( Date string to Date field) gfowajuh SQL Server 2000 1 September 30th, 2003 06:28 AM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.