Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
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 July 6th, 2003, 04:20 PM
Registered User
Join Date: Jun 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Server date column and ASP

Hi All,

The installation of SQL Server 2000 on my server always shows a date column in the American format mm/dd/yyyy.

How do I get ASP to convert a date format of dd/mm/yyyy to mm/dd/yyyy using VBScript. Or, how do I get SQL Server 2000 to always have date columns as dd/mm/yyyy. My Regional Settings are set to UK and the date format as dd/mm/yyyy.

Any ideas?

Many thanks,

Old July 7th, 2003, 08:51 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

Dates are always stored the same way in SQL Server, regardless of where the server is located on the planet, and regardless of the language spoken by the people using it. :)

The datetime datatype is stored internally as two 4 byte integers: the first indicating the number of days which have elapsed since Jan 1, 1900. This is a signed number, and dates before Jan 1, 1753 are not allowed. The second integer stores the time of day as the number of milliseconds since midnight on that date.

The smalldatetime is similar, but using only 2 byte integers. The first representing the days since Jan 1, 1900, and the second the minutes since midnight.

So, what you are dealing with is a formatting issue, either when retrieving data and presenting it, or inputting it into the database, or specifying it in a query or in the definition of a database object (like a CONSTRAINT).

You can choose the format of the result when converting a datetime to a character string; see BOL for the CONVERT function; the style parameter of that function allows you to specify just about any format you can think of for the format of the resultant string.

You can choose the format for display in your host language if you wish to delay conversion until the data is returned to your app. See the FORMAT function in VBScript.

You can specify the format of data for input directly into SQL Server. See BOL for the SET DATEFORMAT command. Note that this affects how dates input into SQL Server are interpreted and has no effect on how dates are output.

See also BOL for "Writing International Transact-SQL Statements" for some tips on how to specify dates and time unambiguously regardless of the locale.

Jeff Mason
Custom Apps, Inc.

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
Inserting Date in Sql Server lamdog ASP.NET 1.0 and 1.1 Basics 5 March 18th, 2005 01:05 PM
describe SQL Server 8 column collation bobcorrick SQL Server 2000 2 November 8th, 2004 06:56 AM
SQL Server Date sankar SQL Server 2000 2 December 19th, 2003 07:48 AM

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