Re: Date Problems - ASP/SQL

From: Aaron Bertrand - MVP (aaron_at_TRASHaspfaq.com)
Date: 05/13/04 

Date: Thu, 13 May 2004 10:51:59 -0400

> I have a date field in SQL server - that holds dates as DD/MM/YYYY format
> (GB).

(a) SQL Server has columns, not fields.

(b) no, a DATETIME column does NOT store DD/MM/YYYY format. Maybe that's
what Enterprise Manager shows you, based on your regional settings. But it
is NOT what's stored in the database.

(c) the safest format is ISO standard: YYYYMMDD. YYYY/MM/DD could still
fail in certain scenarios, e.g. run this script on a scratch system, and
you'll see that YYYY/MM/DD will fail.

PRINT '--- FRENCH ---'
SET LANGUAGE FRENCH
DECLARE @dt SMALLDATETIME
SET @dt = '2004/11/13'
PRINT @dt
GO
DECLARE @dt SMALLDATETIME
SET @dt = '20041113'
PRINT @dt
GO
PRINT '--- US ENGLISH ---'
SET LANGUAGE ENGLISH
DECLARE @dt SMALLDATETIME
SET @dt = '2004/11/13'
PRINT @dt
GO
DECLARE @dt SMALLDATETIME
SET @dt = '20041113'
PRINT @dt
GO
PRINT '--- UK ENGLISH ---'
SET LANGUAGE BRITISH
DECLARE @dt SMALLDATETIME
SET @dt = '2004/11/13'
PRINT @dt
GO
DECLARE @dt SMALLDATETIME
SET @dt = '20041113'
PRINT @dt
GO 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
>
> Now, i have an ASP application that Adds/Edits records in this table; and
i
> am having real problems with the date field in the ADD and EDIT (update)
> part for this app.
>
> Basically, i receive and Error as below:
>
> ****
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char
data
> type to a datetime data type resulted in an out-of-range datetime value.
> /add.asp, line 105
> ****
>
> This occurs when i try to input a date in the format of DD/MM/YYYY through
> an ASP Form. When i try it as MM/DD/YYYY it works great, I need it as GB
not
> US!
>
> Any ideas how i can fix this?
> I have already entered this at the top of the ADD/Edit page:
>
> <%session.LCID=2057%> (just a stab in the dark)
>
> Any ideas much appreciated
>
> -- 
> Thanks in advance
>
> Fawke
>
> Please remove ANTI and SPAM
> from my email address before emailing me.
>
> www.bradflack.com
>
>

Relevant Pages

  • Re: Date Problems - ASP/SQL
    ... The ASP returns the date in DDMMYYYY to the page and it also enters the date ... The Views work great using the DDMMYYYY format, ... > DECLARE @dt SMALLDATETIME ...
    (microsoft.public.inetserver.asp.general)
  • Re: convert a date to a string?
    ... converting it to a CHARACTER string. ... Also, don't convert it back to a datetime, or put it in a datetime variable, ... or you'll lose the format. ... >>> Declare @m int ...
    (microsoft.public.sqlserver.programming)
  • Re:Date Problem
    ... create table test(dt datetime) ... declare @dt varchar ... >database using asp.net. ... mm/dd/yyyy format. ...
    (microsoft.public.sqlserver.security)
  • Re: DateTime Variable in Where Cluase Slows SP to a Crawl
    ... Is your column really named "DateTime"? ... DECLARE @eHour TINYINT, @baseDate SMALLDATETIME, ... > for the where clause in the stored proc. ...
    (microsoft.public.sqlserver.programming)
  • Re: Using variables in Dynamic SQL
    ... >converting datetime from character string." ... the CONVERT function in this piece of the code to format the date back to ... If you print the resulting string in @sql, you'll see that you have some ... DECLARE @StartDate datetime ...
    (microsoft.public.sqlserver.programming)