Date format different from US English

Question

Post reply

Date format different from US English

dig

SSC-Addicted

Points: 435
More actions
March 20, 2003 at 11:13 am

#63269

I live in Rio, Brasil, where We use the "Brazilian" regional setting (the language is called "Português (Brasil)".
My Pentium 550MHz is configured to use the Brazilian date format ("dd/mm/aaaa", that means "dd/mm/yyyy"), and it works normally.
In SQL Server 2000, on the other hand, under "SQL Server group", I created my own server.
Then, clicking on "Properties", in "server settings" tab I choosed "Brazilian" for the "default language". Also, in "Security" folder, opening "Logins", I repeated "Brazilian" as the "default language" for all Users.
Let me observe that, in that "Properties" window of my server, the option Language" still shows "Inglês (Estados Unidos)".
Now, the worst: in the database I created, although I insert correctly the date in a register, the "date" field shows (for instance, for the date "31/12/2002") it as "31/12/aaaa" (remember: "aaaa" stands for "yyyy").
I really do not know what else can I do!!! Would You be kind in helping me? Thanks in advance!
Dalton H Iberê Gilson
March 24, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Hans Lindgren SSChampion Points: 10496 More actions · Answer 1

Please specify a little more detailed your problem.

quote:
insert correctly the date in a register

What register? Are you using INSERT INTO statements? (Take a look in BOL under SET DATEFORMAT, then take a look at ISDATE function [if SQL server actually interprets your data as a DATE])

How are you verifying your data? Are you using SELECTs?

dig SSC-Addicted Points: 435 More actions · Answer 2

Hanslindgren

1 - Yes, the inclusion of new data passes through "INSERT INTO (the table....)", as usual;

2 - I checked ISDATE in SQL Query Analyzer and the result returned "1";

3 - looking in my DB tables, in my Server inside SQL Server, I see that the dates are correctly displayed (for instance: "31/12/2001") in my "ddmmyyyy" dateformat...

...?!?

Thanks for trying...!

Dalton

Hans Lindgren SSChampion Points: 10496 More actions · Answer 3

Hmm, sorry, then I don't understand the problem? Or is it resolved?

quote:
the "date" field shows (for instance, for the date "31/12/2002") it as "31/12/aaaa"

Is that the problem then? Where did it appear?

dig SSC-Addicted Points: 435 More actions · Answer 4

Yes, Hanslindgren!, that is the very problem!

This quite strange display (for instance, "31/12/aaaa") is how the date appears in the pages!

...Thanks again!

Dalton

Hans Lindgren SSChampion Points: 10496 More actions · Answer 5

Sorry, but WHERE is it displayed like that? What is displaying it like that?

Are you talking about a third party software? Do you have the source code (otherwise fixing it might be a problem 😉

Pages, what pages? Register, what register?

My questions are many, but for trying to help you I need to know the error situation...

Since you state

quote:
3 - looking in my DB tables, in my Server inside SQL Server, I see that the dates are correctly displayed (for instance: "31/12/2001")

I am not sure that your problem concerns SQL Server but something external...

dig SSC-Addicted Points: 435 More actions · Answer 6

Hanslindgren

I'm building a site about "Contracts", where I'll put (among other things) all Brazilian laws concerning Contracts, directly and/or indirectly.

So, a webpage, called "legislation.asp", will display a list (brought from the data I'll INSERT INTO the respective table in my SQL Server 2000 database, through the inputs I'll give in an "administration" webpage) of all laws about Contracts, for Visitors to choose and pick one up, to read, download and/or print.

And that's that webpage, with that list, I'm talking about: the name-and-nr. of each law, and a sinopsis of its content, appears correctly in the webpage, but the date of the publication of the law, is what is displayed wrong (for instance:"31/12/aaaa").

Although I thank You very much for your efforts in helping me, I'm not quite sure if I made myself sufficiently clear this time -- if not, please help me by querying me!

Thanks again!

Dalton

SQLBill SSC Guru Points: 51440 More actions · Answer 7

You haven't answered the important question that Hanslindgren asked of you...what are you using to get the information? In other words, when you get the response "31/12/aaaa" where do you get that at? Is in SQL Server? Is it in a Crystal Reports? Is is in Visual Basic?

From your information, SQL Server appears to have the date information correctly stored. It's just being displayed wrong and we need to know what you are using to display the information.

-SQLBill

dig SSC-Addicted Points: 435 More actions · Answer 8

SQLBill

You've already observed that (up to now, at least!) I'm just a dummy and self-made-Programmer..., that's why I'm making things seem so difficult! ...but: be patient!

In the beginning of the webpage, written in ASP, the data (and dates) are SELECTed from SQL 2000 Server database registers through ASP statements [open_objConn, Set objRS=Server.CreateObject("ADODB.Recordset"), etc., etc.], and are displayed in tables, inside iframes.

Did I answered your queries? Thanks once more!

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 9

Dalton, could you post the code used to display the data from ADO Recordsets?

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

dig SSC-Addicted Points: 435 More actions · Answer 10

Chrhedga

Sure! This is one of the pages where data will be displayed:

"<%@ LANGUAGE = "VBScript"

ENABLESESSIONSTATE = False %>

<%

Option Explicit

On Error Resume Next

Response.Expires = 0

%>

<%

Dim objConn, objRS, strQ, varcodigolei

Dim strConnection

Call abre_objConn

Set objRS = Server.CreateObject("ADODB.Recordset")

varcodigolei = request.querystring("tit")

Set objRS.ActiveConnection = objConn

strQ = "SELECT codigolei, titulolei, datalei, assuntolei, tipolei, destaquelei FROM legislacaotb WHERE tipolei LIKE 'cpub'"

objRS.Open strQ

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<title>Contratos on Line - Legislação codificada geral, aplicável aos Contratos em geral</title>

/* Preload images script, by JavaScript Kit (http://javascriptkit.com) */

var myimages=new Array()

function preloadimages(){

for (i=0;i<preloadimages.arguments.length;i++){

myimages=new Image()

myimages.src=preloadimages.arguments

}

//Enter path of images to be preloaded inside parenthesis. Extend list as desired.

preloadimages("../../../imagens/back1.gif","../../../imagens/null.gif","../../../imagens/tabArqBoletins0.gif","../../../imagens/tabArtigos0.gif","../../../imagens/tabBoletim0.gif","../../../imagens/tabBoletimAtual0.gif","../../../imagens/tabCadastro0.gif","../../../imagens/tabCoLFavoritos0.gif","../../../imagens/tabCoLPagPrincipal0.gif","../../../imagens/tabContratoMVM0.gif","../../../imagens/tabEventos0.gif","../../../imagens/tabFaleConosco0.gif","../../../imagens/tabFaleConosco20.gif","../../../imagens/tabFerramentas0.gif","../../../imagens/tabForum0.gif","../../../imagens/tabGlossario0.gif","../../../imagens/tabIndices0.gif","../../../imagens/tabJurisprudencia0.gif","../../../imagens/tabLegislacao0.gif","../../../imagens/tabLinkParaCoL0.gif","../../../imagens/tabLinks0.gif","../../../imagens/tabMVM0.gif","../../../imagens/tabNoticias0.gif","../../../imagens/tabNoticiasIntegra0.gif","../../../imagens/tabPesqonline0.gif","../../../imagens/tabQuemSomos0.gif","../../../imagens/tabRecomende0.gif","../../../imagens/tabSobMedida0.gif","../../../imagens/tabSumulas0.gif","../../../imagens/tabTGratis0.gif","../../../imagens/col-caneta.gif","../../../imagens/col-lupa.gif","../../../imagens/col-menu.gif","../../../imagens/col-oculos.gif","../../../imagens/tabContratos0.gif","../../../imagens/tabCodigos1.gif","../../../imagens/tabConsumidor0.gif","../../../imagens/tabSociedades0.gif","../../../imagens/tabTrabalho0.gif","../../../imagens/tabContratosPublicos0.gif","../../../imagens/tabInformatica0.gif","../../../imagens/tabPL-MPs0.gif","../../../imagens/blankEFEBEF.gif","../../../imagens/blankE6E8FA.gif","../../../imagens/atencao6.gif","../../../imagens/lampada1.gif")

</script>

<DIV style="position:absolute; left:-1px; top:95px; width:736px; padding:3px; background:#E7EFE7; font-size:8pt; font-family:verdana,tahoma,arial; color:#444444;"><IMG src="../../../imagens/lampada1.gif" border=0 align="top"> <b>clique no cabeçalho da coluna, e liste em ordem <i>crescente/decrescente</i></b> <IMG src="../../../imagens/atencao6.gif" border=0 align="top"> <b>as <i>datas</i> estão por <i>ano</i>-<i>mês</i>-<i>dia</i></i></b></DIV>

<TD align="left" background="../../../imagens/blankEFEBEF.gif" width="185"><FONT face=Verdana,Sans-serif color=#880000 size=2><B> Legislação</B></FONT></TD>

<TD align="left" background="../../../imagens/blankEFEBEF.gif" width="470"><FONT face=Verdana,Sans-serif color=#880000 size=2><B>Assunto</B></FONT></TD>

<TBODY>

<%

While Not objRS.EOF

Response.Write "<TR><TD align='left' valign='top' background='../../../imagens/blankE6E8FA.gif' width='185'><FONT color=#000088 size=1><a href='../../../legislacaointegra.asp?tit=" & objRs("codigolei") & "'>" & objRS("titulolei") & "</a></FONT></TD>"

Response.Write "<TD align='left' valign='top' background='../../../imagens/blankE6E8FA.gif' width='60'><FONT color=#880000 size=1>" & objRS("datalei") & "</FONT></TD>"

Response.Write "<TD align='left' valign='top' background='../../../imagens/blankE6E8FA.gif' width='470'><FONT color=#880000 size=1>" & objRS("assuntolei") & "</FONT></TD>"

objRS.MoveNext

Wend

objRS.close

Call fecha_objConn

Set objRS = Nothing

%>

</TBODY></TABLE></DIV></layer></ilayer></FORM>

</DIV></DIV></DIV></BODY></HTML>"

Thanks, many thanks!

Dalton

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 11

Hmm, the forum code is having fun with us.

I guess it is this row of code that displays the date in the strange way:

quote:
Response.Write "<TD align='left' valign='top' background='../../../imagens/blankE6E8FA.gif' width='60'><FONT color=#880000 size=1>" & objRS("datalei") & "</FONT></TD>"

Have you tried running the select statement in Query Analyzer? What does it return there?

What data type is the column datalei?

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

SQLBill SSC Guru Points: 51440 More actions · Answer 12

Dig,

We were all beginners at one time. I'm not an ASP user, so I can't tell you where the problem is exactly. But I can help you a bit.

Here's the thing about datetime data type:

SQL Server STORES the datetime data type as a 8 bit integer. It's the number of seconds after a specific date. So, it really doesn't store it as 2003-03-26 12:00:00.000 or any other 'datetime' format. So, that's how it is STORED.

Whatever method is used to retrieve the datetime determines how it is DISPLAYED. Query Analyzer will return it in the format you have set SQL Server in unless you do a CONVERT. ASP, Visual Basic, etc. all DISPLAY the datetime in whatever format they are set for as default unless you use their coding to change the format.

In your case, you have SQL Server set for Brazilian time and as you said, SQL Server displays the datetime correctly. But, you haven't told us what your ASP is set for as default. That's where the problem is. Your ASP code is taking the SQL Server data and displaying it incorrectly.

This issue with STORING and DISPLAYING of datetime data type can be one of the most confusing things to learn about SQL Server. I hope I explained it well enough for you.

Again, the problem isn't with SQL Server, it's with your ASP program.

-SQLBill

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 13

SQLBill, Dig, and everyone. Just to make sure I understand dig correct, and if I do make sure all you others also understand the problem this way. As I understand it, the problem is that when dig prints the date, using ASP, what gets printed is the exact characters below:

31/12/aaaa

Note that I woth aaaa don't mean 'any four digits representing a year', I mean the character 'a' displayed 4 times. This is how I understand you dig, is that correct? If I am correct, then this seems very strange indeed. No matter what way your ASP is set to display dates I can't see how it would mask the year with aaaa. That is why I asked what the datatype is for the column datalei, and why I wanted you to run the SELECT-statement in Query Analyzer and tell us what it returns.

Other than that, SQLBill makes some very good points about the confusion regarding storage and representation of dates in SQL Server.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/