Wrox Programmer Forums
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
Old December 19th, 2003, 06:16 AM
Authorized User
Join Date: Jul 2003
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to sankar
Default SQL Server Date

Hi experts,
Can u suggest a way? I have a scenario where I need to maintain an event calendar for my site. The event details are entered via an online interface, where two mandetory fields are there: start_date and end_date. If the start_date value is greater than system date of a particular day, the event details is displayed under the 'Forthcoming event section'. My prblem is, not every time I get the exact start_date value, e.g. June 2004. I have to show the forthcoming events sorted by date. The date fields are of date type. If I enter June 2004, it is taking 6/1/2004, which is faulty information. Can you suggest any solution to this problem? Any help in this regard will be appreciated.
Thx in adv.

Sankar Sengupta
Sankar Sengupta
Striving for the BEST
Old December 19th, 2003, 07:21 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts

'6/1/2004' is not faulty, it's the input data that is faulty. If you just specify a month, then it's a fairly reasonable thing to do to assume the first day of that month as the day. Same as if you input a full date but no time, SQL Server defaults to midnight for the time. BTW you are aware that your system is using US date format, and you're not seeing a date of 6 Jan 2004 ;)
The solution to your problem is to force the users to input the correct data, ie finalise the event's details before attempting to enter it into the database, or live with a default of the first of the month. There isn't any way to enable telepathy or seeing into the future for SQL server, yet :)


There are two secrets to success in this world:
1. Never tell everything you know
Old December 19th, 2003, 07:48 AM
joefawcett's Avatar
Wrox Author
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts

Most non US developers have problems with dates because of the default format, perhaps this is the reason. If I have a string that I want SQL server to treat as a date I either convert it to format yyymmdd with no spaces etc. or even better dd MMM yyyy e.g. 20030601 or 01 Jun 2003 respectively.




Similar Threads
Thread Thread Starter Forum Replies Last Post
comparing date column with sql server mallikalapati ASP.NET 2.0 Professional 3 March 12th, 2008 06:06 PM
INSERT DATE INTO SQL SERVER prasanta2expert ASP.NET 1.0 and 1.1 Basics 1 January 4th, 2007 08:27 AM
date in sql server and in visual studio saif44 ASP.NET 2.0 Professional 3 March 22nd, 2006 05:02 PM
HELP!!! ..Date Range Problem in SQL Server kuku SQL Server 2000 5 June 28th, 2005 09:19 PM
Inserting Date in Sql Server lamdog ASP.NET 1.0 and 1.1 Basics 5 March 18th, 2005 01:05 PM

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