Showing posts with label app. Show all posts
Showing posts with label app. Show all posts

Tuesday, March 27, 2012

Date problem with ASP.NET but not in VB.NET Winform App

Edited by SomeNewKid. Please post code between<code> and</code> tags.


Hello, I have given up after 3 hours of trying to get a DBNull.Value to be inserted into SQL 2000 DB. Below is the code that exists in an object that I use both from a VB.NET Windows application and from an ASP.NET application. (same exact compiled object dll). Has ran fine with windows application for almost a year - still does. Use the same object in my new ASP.NET application and I get a SQL Overflow error that says date must be between 1753 12 am etc etc... when trying to insert null using the code below.

This one has me stumped. The lines below where I set the date are basically this (what the logic equates to):

oDR.Item("CT_CustomDate1") = DBNull.Value
oDR.Item("CT_CustomDate2") = DBNull.Value

Error gets thrown when this line is executed:

oDA.Update(oDT)

Also, the CT_CustomDate1 & 2 fields in the SQL Server 2000 DB are of type SmallDateTime and the variables in object used in code function below are Date. I've tried every combination of Date, DateTime, SmallDateTime, etc. etc. to no avail.

Thanks for you help!

 Public Function AddNew(ByVal sGUID As String) As Boolean
'Saves all new contact information to database
Dim sSQL As String
sSQL = "SELECT * FROM Contact WHERE 1=2"
Dim oConn As New SqlConnection(sConnectionString)
Dim oDT As New DataTable
Dim oDA As New SqlDataAdapter(sSQL, oConn)
Dim oDR As DataRow
Dim bSuccess As Boolean

Try
oDA.Fill(oDT)

Dim dataCommandBuilder As New SqlCommandBuilder(oDA)
oDA.InsertCommand = dataCommandBuilder.GetInsertCommand
oDA.DeleteCommand = dataCommandBuilder.GetDeleteCommand
oDA.UpdateCommand = dataCommandBuilder.GetUpdateCommand

oDR = oDT.NewRow

oDR.Item("CT_GUID") = sGUID
oDR.Item("CT_CompanyGUID") = CTCompanyGUID
oDR.Item("CT_Prefix") = CTPrefix
oDR.Item("CT_FirstName") = CTFirstName
oDR.Item("CT_MiddleName") = CTMiddleName
oDR.Item("CT_LastName") = CTLastName
oDR.Item("CT_Suffix") = CTSuffix
oDR.Item("CT_Title") = CTTitle
oDR.Item("CT_ContactAddress") = CTContactAddress
oDR.Item("CT_Address1") = CTAddress1
oDR.Item("CT_Address2") = CTAddress2
oDR.Item("CT_City") = CTCity
oDR.Item("CT_State") = CTState
oDR.Item("CT_Zip") = CTZip
oDR.Item("CT_Country") = CTCountry
oDR.Item("CT_InternationalPhone") = CTInternationalPhone
oDR.Item("CT_Phone") = CTPhone
oDR.Item("CT_Fax") = CTFax
oDR.Item("CT_Cell") = CTCell
oDR.Item("CT_AltPhone") = CTAltPhone
oDR.Item("CT_Assistant") = CTAssistant
oDR.Item("CT_AssistantPhone") = CTAssistantPhone
oDR.Item("CT_NoEmail") = CTNoEmail
oDR.Item("CT_EmailAddress") = CTEmailAddress
oDR.Item("CT_Confidential") = CTConfidential
oDR.Item("CT_TypeGUID") = CTTypeGUID
oDR.Item("CT_Designation") = ""
oDR.Item("CT_LastUpdate") = CTLastUpdate
oDR.Item("CT_UpdatedByWho") = CTUpdatedByWho
oDR.Item("CT_Location") = CTLocation
oDR.Item("CT_CustomBit1") = CTCustomBit1
oDR.Item("CT_CustomBit2") = CTCustomBit2
oDR.Item("CT_CustomBit3") = CTCustomBit3
oDR.Item("CT_CustomBit4") = CTCustomBit4
oDR.Item("CT_CustomBit5") = CTCustomBit5
oDR.Item("CT_CustomBit6") = CTCustomBit6
oDR.Item("CT_CustomBit7") = CTCustomBit7
oDR.Item("CT_CustomStr1") = CTCustomStr1
oDR.Item("CT_CustomStr2") = CTCustomStr2
oDR.Item("CT_CustomStr3") = CTCustomStr3
oDR.Item("CT_CustomStr4") = CTCustomStr4
oDR.Item("CT_CustomStr5") = CTCustomStr5
oDR.Item("CT_CustomStr6") = CTCustomStr6
oDR.Item("CT_CustomStr7") = CTCustomStr7
oDR.Item("CT_CustomStr8") = CTCustomStr8
oDR.Item("CT_CustomStr9") = CTCustomStr9
oDR.Item("CT_CustomStr10") = CTCustomStr10
oDR.Item("CT_CustomStr11") = CTCustomStr11
oDR.Item("CT_CustomStr12") = CTCustomStr12
oDR.Item("CT_CustomStr13") = CTCustomStr13
oDR.Item("CT_CustomPhone1") = CTCustomPhone1
oDR.Item("CT_CustomPhone2") = CTCustomPhone2
oDR.Item("CT_CustomPhone3") = CTCustomPhone3
oDR.Item("CT_CustomPhone4") = CTCustomPhone4
oDR.Item("CT_CustomDate1") = IIf(CTCustomDate1 = #12:00:00 AM#, DBNull.Value, CTCustomDate1)
oDR.Item("CT_CustomDate2") = IIf(CTCustomDate2 = #12:00:00 AM#, DBNull.Value, CTCustomDate2)
'oDR.Item("CT_CustomDate1") = CDate(#1/1/1753#)
'oDR.Item("CT_CustomDate2") = CDate(#1/1/1753#)
oDR.Item("CT_CustomAmount1") = CTCustomAmount1
oDR.Item("CT_CustomAmount2") = CTCustomAmount2
oDR.Item("CT_CustomType1GUID") = CTCustomType1GUID
oDR.Item("CT_CustomType2GUID") = CTCustomType2GUID
oDR.Item("CT_CustomCompany1GUID") = IIf(CTCustomCompany1GUID = "00", DBNull.Value, CTCustomCompany1GUID)
oDR.Item("CT_CustomCompany2GUID") = IIf(CTCustomCompany2GUID = "00", DBNull.Value, CTCustomCompany2GUID)
oDR.Item("CT_CustomContact1GUID") = IIf(CTCustomContact1GUID = "00", DBNull.Value, CTCustomContact1GUID)

oDT.Rows.Add(oDR)
oDA.Update(oDT)
CTGUID = sGUID
bSuccess = True
Catch err As Exception
MsgBox("Error saving new contact..." & vbCrLf & vbCrLf & err.Message, MsgBoxStyle.Critical + MsgBoxStyle.OKOnly, "Database Error")
bSuccess = False
End Try

AddNew = bSuccess

End Function

Are you sure your ASP.NET application are referencing the same assemblies as the
WinForm?
Are they running under the same Framework version?
DataAccessApplicationBlock?

Regards
Fredriksql

Monday, March 19, 2012

Date inputs (UK format) in SQL svr 2000 with OLE DB

Hi all,

I'm building an ASP app on a Windows 2000/IIS machine which interfaces
with our SQL Server 2000 database via OLE DB.

Since we're based in the UK I want the users to be able to type in
dates in UK date format to input into the database. In Enterprise
Manager on the SQL Server I can manually enter a record into a table
and just type in a UK date (MM/DD/YYYY e.g. 25/12/2004) and it accepts
it happily.

However, if I enter the exact same record on the form on the web page,
again using the UK date format, I get this back from the IIS box:

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
The conversion of a char data type to a datetime data type resulted
in an out- of-range datetime value.
/ictest/eventadm.asp, line 78

I'm assuming that despite SQL Server accepting the date in UK format,
OLE DB will not. Can the OLE provider be configured to allow such
date formats, either (I imagine) in the registry or by altering the
connection string?

TIA,
NiallMake sure the SQL Server user is a UK English User otherwise SQL Server will
do this

(presume user language is set to English (American))

This guy is American so he is giving me a date of 25/12/2004. Americans use
MMDDYYYY so what he means is

the 12th day of the 25th month

this as we can guess is way out of range.

Here is another example

SET LANGUAGE us_english
GO

select cast('25/12/2004' as datetime)

Changed language setting to us_english.
Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

SET LANGUAGE British
GO

select cast('25/12/2004' as datetime)

2004-12-25 00:00:00.000

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

"Niall Porter" <niallporter@.yahoo.co.uk> wrote in message
news:2db8d05e.0407280624.773f87ff@.posting.google.c om...
> Hi all,
> I'm building an ASP app on a Windows 2000/IIS machine which interfaces
> with our SQL Server 2000 database via OLE DB.
> Since we're based in the UK I want the users to be able to type in
> dates in UK date format to input into the database. In Enterprise
> Manager on the SQL Server I can manually enter a record into a table
> and just type in a UK date (MM/DD/YYYY e.g. 25/12/2004) and it accepts
> it happily.
> However, if I enter the exact same record on the form on the web page,
> again using the UK date format, I get this back from the IIS box:
>
> HTTP 500.100 - Internal Server Error - ASP error
> Internet Information Services
> Technical Information (for support personnel)
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E07)
> The conversion of a char data type to a datetime data type resulted
> in an out- of-range datetime value.
> /ictest/eventadm.asp, line 78
> I'm assuming that despite SQL Server accepting the date in UK format,
> OLE DB will not. Can the OLE provider be configured to allow such
> date formats, either (I imagine) in the registry or by altering the
> connection string?
> TIA,
> Niall|||"Niall Porter" <niallporter@.yahoo.co.uk> wrote in message
news:2db8d05e.0407280624.773f87ff@.posting.google.c om...
> Hi all,
> I'm building an ASP app on a Windows 2000/IIS machine which interfaces
> with our SQL Server 2000 database via OLE DB.
> Since we're based in the UK I want the users to be able to type in
> dates in UK date format to input into the database. In Enterprise
> Manager on the SQL Server I can manually enter a record into a table
> and just type in a UK date (MM/DD/YYYY e.g. 25/12/2004) and it accepts
> it happily.
> However, if I enter the exact same record on the form on the web page,
> again using the UK date format, I get this back from the IIS box:
>
> HTTP 500.100 - Internal Server Error - ASP error
> Internet Information Services
> Technical Information (for support personnel)
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E07)
> The conversion of a char data type to a datetime data type resulted
> in an out- of-range datetime value.
> /ictest/eventadm.asp, line 78
> I'm assuming that despite SQL Server accepting the date in UK format,
> OLE DB will not. Can the OLE provider be configured to allow such
> date formats, either (I imagine) in the registry or by altering the
> connection string?
> TIA,
> Niall

The best way to handle this would probably be to use a date format which
works regardless of any client and server settings - there are two:

'20040728' -- no time needed
'2004-07-28T20:20:00' -- ISO8601, includes time

Although I don't know much about ADO, I believe that if ADO is aware that
the target is a datetime parameter or column, it will automatically handle
the conversion for you. That might be one option for you - pass the value to
a stored procedure, and when you use the Command.CreateParameter method, ADO
will be aware that the parameter is datetime. But I admit that I haven't
tried it myself, so I may be wrong about this.

Simon|||Niall Porter (niallporter@.yahoo.co.uk) writes:
> Since we're based in the UK I want the users to be able to type in
> dates in UK date format to input into the database. In Enterprise
> Manager on the SQL Server I can manually enter a record into a table
> and just type in a UK date (MM/DD/YYYY e.g. 25/12/2004) and it accepts
> it happily.
> However, if I enter the exact same record on the form on the web page,
> again using the UK date format, I get this back from the IIS box:
>
> HTTP 500.100 - Internal Server Error - ASP error
> Internet Information Services
> Technical Information (for support personnel)
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E07)
> The conversion of a char data type to a datetime data type resulted
> in an out- of-range datetime value.
> /ictest/eventadm.asp, line 78
> I'm assuming that despite SQL Server accepting the date in UK format,
> OLE DB will not. Can the OLE provider be configured to allow such
> date formats, either (I imagine) in the registry or by altering the
> connection string?

So how does your code look like? Since you get the error from OLE DB,
I assume that you are passing the value as a parameter to a prepared
statement, and that you declare the parameter to be of type
adDBTimeStamp. In such case you must use ISO format, 2004-12-25.

If you want to support regional settings, you should use some ASP
routine to convert the date value. CDate() might be your guy, but
we're straying beyond the realms of this newsgroup. You may want to
try an ASP forum.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all the help from everyone so far, I'm not quite out of the
woods yet tho!

"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message news:<4107c9f7$0$7125$db0fefd9@.news.zen.co.uk>...
> Make sure the SQL Server user is a UK English User otherwise SQL Server will
> do this
> (presume user language is set to English (American))

The database user (a pure SQL Server user) was set to English. I
found another language called "British English", I've changed that
user to British English and now I can input dates in a UK format
(DD/MM/YYYY) which is great, thanks for that. I appreciate the 'best'
way to do it is to use an ISO format but my users wouldn't cope with
such technicalities as this...

However, dates *returned* are still in US format (MM/DD/YYYY)! Is
there some other esoteric setting I have to tweak somewhere?

> This guy is American so he is giving me a date of 25/12/2004. Americans use
> MMDDYYYY so what he means is
> the 12th day of the 25th month
> this as we can guess is way out of range.
>
> Here is another example
> SET LANGUAGE us_english
> GO

Won't that set it for the all databases on the whole server? We have
a number of applications that use the SQL server for their databases,
some of which may well have been developed in the states. If I change
the setting for the whole server, any other applications that don't
use the ISO or other non-regional date formatting will break surely?

> select cast('25/12/2004' as datetime)

Tried this also, didn't make any difference.

> Changed language setting to us_english.
> Server: Msg 242, Level 16, State 3, Line 2
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> SET LANGUAGE British
> GO
> select cast('25/12/2004' as datetime)
> 2004-12-25 00:00:00.000
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>|||Niall Porter (niallporter@.yahoo.co.uk) writes:
> However, dates *returned* are still in US format (MM/DD/YYYY)! Is
> there some other esoteric setting I have to tweak somewhere?

I'm still not sure why you post this in an SQL Server forum, I would
expect this to be an ASP problem. Then again, since you have not posted
any code, I have no idea of what you are doing.

>> SET LANGUAGE us_english
>> GO
> Won't that set it for the all databases on the whole server? We have
> a number of applications that use the SQL server for their databases,
> some of which may well have been developed in the states. If I change
> the setting for the whole server, any other applications that don't
> use the ISO or other non-regional date formatting will break surely?

SET LANGAUGE changes the language for the current session. However, this
option controls how date literals are interpreted on *SQL Server* and
you got the conversion error on client level.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland.

If the client connects as a US user on a UK server entering a data of
'25/12/2004' then wouldn't the error returned be this error. My expereince
of mixing regional settings says it would.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9536ECDAE92D1Yazorman@.127.0.0.1...
> Niall Porter (niallporter@.yahoo.co.uk) writes:
> > However, dates *returned* are still in US format (MM/DD/YYYY)! Is
> > there some other esoteric setting I have to tweak somewhere?
> I'm still not sure why you post this in an SQL Server forum, I would
> expect this to be an ASP problem. Then again, since you have not posted
> any code, I have no idea of what you are doing.
> >> SET LANGUAGE us_english
> >> GO
> > Won't that set it for the all databases on the whole server? We have
> > a number of applications that use the SQL server for their databases,
> > some of which may well have been developed in the states. If I change
> > the setting for the whole server, any other applications that don't
> > use the ISO or other non-regional date formatting will break surely?
> SET LANGAUGE changes the language for the current session. However, this
> option controls how date literals are interpreted on *SQL Server* and
> you got the conversion error on client level.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Allan Mitchell (allan@.no-spam.sqldts.com) writes:
> If the client connects as a US user on a UK server entering a data of
> '25/12/2004' then wouldn't the error returned be this error. My
> expereince of mixing regional settings says it would.

Depends. The error Neill got was:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value.

The only way to get that error from Query Analyzer is to connect a linked
server with a date-format conflict along the line.

The problem here is that things can to wrong on several levels. In Neill's
case it got wrong already on client level. I suspect because he was
using adDBTimeStamp to which he must feed an ISO format.

It is also confusing since the regional settings on the client neither the
server on what happens in SQL Server which uses its own language
definitions. You can set language when you connect, but then you have to
pass that in the connection string - and it has to be a language as SQL
Server knows it.

The main problem in Neill's case is that he has not posted any code, so
we are left to guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, March 8, 2012

Date format isn't working

Hello, all. We've got a table that holds the begin and end date for allowing
people into a voting app. Here's the DDL,
CREATE TABLE [tblVotingPeriod] (
[pk] [tinyint] IDENTITY (1, 1) NOT NULL ,
[beginVote] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_beginVote]
DEFAULT (getdate()),
[endVote] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_endVote]
DEFAULT (getdate()),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVotingPeriod_dateAdded]
DEFAULT (getdate()),
CONSTRAINT [PK_tblVotingPeriod] PRIMARY KEY CLUSTERED
(
[pk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
We need it to store "3/15/2005 12:00:00 AM" as the beginVote and "3/18/2005
12:00:00 AM" as the endVote. However, each time we try entering the
"12:00:00 AM" part, SQL Server ignores it. When we retrieve the date, it
won't display the time in the ASP page -- just the date. We've tried using
the CONVERT(smalldatetime, beginVote,109) to no avail. Any ideas? Thanks
much."dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:ekmwoibGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 12:00:00 AM" as the endVote. However, each time we try entering the
> "12:00:00 AM" part, SQL Server ignores it. When we retrieve the date, it
Sounds like a problem with your client code, not SQL Server. SQL Server
DATETIME and SMALLDATETIME datatypes must have a time component -- they
can't be ignored. Or are you getting an error of some sort?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Even in SQL Server when viewing the table in Enterprise Mgr., it shows the
date as "3/15/2005" without the "12:00:00 AM" part. Each time I type it in
and tab out, it ignores it. Is there something special about 12:00:00 AM? Is
that the "default" for the date, so it doesn't even show it? What if you do
want it shown?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O7BswobGFHA.544@.TK2MSFTNGP12.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:ekmwoibGFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Sounds like a problem with your client code, not SQL Server. SQL
> Server
> DATETIME and SMALLDATETIME datatypes must have a time component -- they
> can't be ignored. Or are you getting an error of some sort?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Even in SQL Server when viewing the table in Enterprise Mgr., it shows the
> date as "3/15/2005" without the "12:00:00 AM" part. Each time I type it in
> and tab out, it ignores it. Is there something special about 12:00:00 AM?
Is
> that the "default" for the date, so it doesn't even show it? What if you
do
> want it shown?
EM does appear to truncate the date if the time is 12:00:00 AM. And
yes, that is the "default" time -- if you insert a row with '20050315' and
no time component, the time will automatically be set to 12:00:00 AM.
(Actually, 00:00:00). You should be able to format the date to see the time
component in ASP using VBScript's date formatting functions. It is being
returned by SQL Server, you're just not displaying it. Note also, Query
Analyzer does not trucate the date.
One side note, by the way: You should re-consider that date format, as
it's ambiguous (it can change with locale); for example, what does the
following represent: '01/02/2005' ? Depends on what country you're in.
The preferred format in SQL Server is YYYYMMDD.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks, Adam. I just noticed that it took 12:01:00 AM, so 12:00:00 am is
just not being shown. Do you know of a less ambigious date format? Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Is
> do
> EM does appear to truncate the date if the time is 12:00:00 AM. And
> yes, that is the "default" time -- if you insert a row with '20050315' and
> no time component, the time will automatically be set to 12:00:00 AM.
> (Actually, 00:00:00). You should be able to format the date to see the
> time
> component in ASP using VBScript's date formatting functions. It is being
> returned by SQL Server, you're just not displaying it. Note also, Query
> Analyzer does not trucate the date.
> One side note, by the way: You should re-consider that date format, as
> it's ambiguous (it can change with locale); for example, what does the
> following represent: '01/02/2005' ? Depends on what country you're in.
> The preferred format in SQL Server is YYYYMMDD.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:e9lQX6bGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Thanks, Adam. I just noticed that it took 12:01:00 AM, so 12:00:00 am is
> just not being shown. Do you know of a less ambigious date format? Thanks.
YYYYMMDD HH:MM:SS - '20050315 00:00:00'
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Which format do I use if I want the date to come out "Mar 21 2005"? Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e2773zbGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> Is
> do
> EM does appear to truncate the date if the time is 12:00:00 AM. And
> yes, that is the "default" time -- if you insert a row with '20050315' and
> no time component, the time will automatically be set to 12:00:00 AM.
> (Actually, 00:00:00). You should be able to format the date to see the
> time
> component in ASP using VBScript's date formatting functions. It is being
> returned by SQL Server, you're just not displaying it. Note also, Query
> Analyzer does not trucate the date.
> One side note, by the way: You should re-consider that date format, as
> it's ambiguous (it can change with locale); for example, what does the
> following represent: '01/02/2005' ? Depends on what country you're in.
> The preferred format in SQL Server is YYYYMMDD.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||Thanks, Adam. I got it working, thanks to your help! I had a mistake in my
code where I was doing the convert -- instead of converting it to a char or
varchar, I was changing it to a smalldatetime, which it already was! I'm
using the 100 format to convert it to this: Mar 15 2005 12:00AM
Thanks for your help :) Much appreciated.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e6D5c9bGFHA.2416@.TK2MSFTNGP14.phx.gbl...
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:e9lQX6bGFHA.3724@.TK2MSFTNGP10.phx.gbl...
> YYYYMMDD HH:MM:SS - '20050315 00:00:00'
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||I suggest you check out my article on the subject:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message news:eTbYl9bGFHA.2356@.TK2MSFTNGP12.phx.g
bl...
> Which format do I use if I want the date to come out "Mar 21 2005"? Thanks
.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23gY$73bGFHA.1476@.TK2MSFTNGP09.phx.gbl...
>

Wednesday, March 7, 2012

date format in raiserror description

I need to return a date to the app if a stored procedure fails (when I raise
an error in it). I have just included the date in the error string, but
wanting to internationalise the app, need to be able to display the date is
the local format. What is the best way to return this information to the
app/user?Return it as an unambiguous date format (e.g. YYYYMMDD). Let the app
localize it (e.g. VBScript / ASP will apply locale to formatdatetime()).
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
news:R5CdnUa6P8Y7vcnfRVn-uA@.mycybernet.net...
> I need to return a date to the app if a stored procedure fails (when I
raise
> an error in it). I have just included the date in the error string, but
> wanting to internationalise the app, need to be able to display the date
is
> the local format. What is the best way to return this information to the
> app/user?
>|||I do not want to add any output parameters. Is there a way of returning an
error description and the date when raising the error?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O5ORqGtOFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Return it as an unambiguous date format (e.g. YYYYMMDD). Let the app
> localize it (e.g. VBScript / ASP will apply locale to formatdatetime()).
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "David J Rose" <david.rose@.newsgroup.reply.only.com> wrote in message
> news:R5CdnUa6P8Y7vcnfRVn-uA@.mycybernet.net...
> raise
> is
>|||On Wed, 6 Apr 2005 14:33:49 -0400, David J Rose wrote:

>I do not want to add any output parameters. Is there a way of returning an
>error description and the date when raising the error?
Hi David,
As far as I know: no.
BTW, I don't think Aaron intended to suggest you use an output
parameter. I think that he wanted to suggest that you include the date
in YYYYMMDD format in the error message, let your client intrercept the
error message, find the YYYYMMDD value and replace it with a formatted
date.
(And if Aaron didn't mean to suggest that, then I will <g> )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> (And if Aaron didn't mean to suggest that, then I will <g> )
No, that's exactly what I meant. Currently I presume he is doing:
SET @.msg = 'this is an error on '+CONVERT(VARCHAR, GETDATE())+'...'
And I was suggesting an unambiguous format, such as:
SET @.msg = 'this is an error on '+CONVERT(CHAR(8), GETDATE(), 112)+'...'
Let the client interpret it however it wants to, or just leave it in the
unambiguous format. Whether you're in the UK or the US or anywhere else,
YYYYMMDD or YYYY-MM-DD is a heck of a lot more clear than 06/05/04 ...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.