ASP > SQL date problem!?

[Jerome]

Hi,

I know this is an old problem and I've already tried to look the
solution up on the web but I didn't find what I need.

So, there's the following situation:

I've got an ASP page with a form, containing two fields that poses problems

1. birthday field
SQL server will only accept it if entered in MM/DD/YYYY. Which is bad
since we're in Europe, people are accustomed to DD/MM/YYYY. I tried to
use LCID but that didn't work either. Funny thing is when the birthday
is entered in MM/DD/YYYY on the page, it later gets displayed as
DD/MM/YYYY in SQL Server!? Which is good though! (It would be better of
course if it could be entered in DD/MM/YYYY and be recorder in SQL as such)

2. field with the current date & time
The ASP page displays it correctly as 02/09/2004 (September 2nd). But
SQL Server saves it as 09/02/2004 (February 9th). And I bet there'll be
an error or September 13th ... Or is there a way to define in SQL Server
something similar to the Default Value: Now() in Access? Then I could
drop this field on the ASP page.

So somehow there's a complete confusion!?

How can I streamline that or where on the Internet could I find more
info about it?

Thank you very much,

Jerome

[Evertjan.]

Re: ASP > SQL date problem!?

Jerome wrote on 02 sep 2004 in microsoft.publi c.inetserver.as p.general:[color=blue]
> 1. birthday field
> SQL server will only accept it if entered in MM/DD/YYYY.[/color]

No, the general accepted format is yyyy-mm-dd
[color=blue]
> Which is bad
> since we're in Europe, people are accustomed to DD/MM/YYYY. I tried to
> use LCID but that didn't work either. Funny thing is when the birthday
> is entered in MM/DD/YYYY on the page, it later gets displayed as
> DD/MM/YYYY in SQL Server!?[/color]

SQL server does not "display"
[color=blue]
> Which is good though! (It would be better
> of course if it could be entered in DD/MM/YYYY and be recorder in SQL
> as such)[/color]

In a database field with the type date(!!), as you are using here,
the date is stored as a number.
[color=blue]
> 2. field with the current date & time
> The ASP page displays it correctly as 02/09/2004 (September 2nd). But
> SQL Server saves it as 09/02/2004 (February 9th).[/color]

You should "give" it to the SQL string as yyyy-mm-dd or yyyy/mm/dd
[color=blue]
> And I bet there'll
> be an error or September 13th ... Or is there a way to define in SQL
> Server something similar to the Default Value: Now() in Access? Then I
> could drop this field on the ASP page.[/color]

Perhaps, I do not use SQL server. now() will probably include the actual
serverlocal time. I suspect that is not what you want.

But if you 'give' it to the db as a ASP string, convert your input to a
yyyy-mm-dd string

If you retrieve it from the databas, and it comes as mm/dd/yyyy, convert
it by ASP(vbs/jscript) to you preferred string.
[color=blue]
> So somehow there's a complete confusion!?
>
> How can I streamline that or where on the Internet could I find more
> info about it?[/color]

Concluding:

Make two functions,
one converting your preferred date string to a yyyy-mm-dd string
and
one converting the database output string to your preferred date string.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress,
but let us keep the discussions in the newsgroup)

[Bob Barrows [MVP]]

Re: ASP > SQL date problem!?

Jerome wrote:[color=blue]
> Hi,
>
> I know this is an old problem and I've already tried to look the
> solution up on the web but I didn't find what I need.
>
> So, there's the following situation:
>
> I've got an ASP page with a form, containing two fields that poses
> problems
> 1. birthday field
> SQL server will only accept it if entered in MM/DD/YYYY.[/color]

Wron. SQL Server will also accept the ISO standard date:

YYYYMMDD


[color=blue]
> Which is bad
> since we're in Europe, people are accustomed to DD/MM/YYYY. I tried to
> use LCID but that didn't work either. Funny thing is when the birthday
> is entered in MM/DD/YYYY on the page, it later gets displayed as
> DD/MM/YYYY in SQL Server!?[/color]

No it doesn't. Datetimes are stored without format as paired integers: the
first integer containing the number of days since the seed date, and the
second containing the number of milliseconds since midnight (to indicate
time of day). Any formatting is done by the client tool used to retrieve the
date from the database, usually using the Regional Settings for the user as
the default format (although this varies depending on which client tool you
are using to retrieve the dates).
[color=blue]
> Which is good though! (It would be better
> of course if it could be entered in DD/MM/YYYY and be recorder in SQL
> as such)[/color]

You should always pass dates to SQL Server in a non-ambiguous format (the
ISO standard). If you allow users to enter dates in any way they wish, you
need to reformat them to YYYYMMDD before passing them to SQL. You need to
take control of the display process so the dates will be displayed in your
preferred format. See the links below.
[color=blue]
>
> 2. field with the current date & time
> The ASP page displays it correctly as 02/09/2004 (September 2nd). But
> SQL Server saves it as 09/02/2004 (February 9th). And I bet there'll
> be an error or September 13th ... Or is there a way to define in SQL
> Server something similar to the Default Value: Now() in Access? Then
> I could drop this field on the ASP page.
>[/color]

Again, this is entirely dependant on the Regional Settings for the IUSR
account on your web server machine. See these articles:

http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Thomas Dodds]

Re: ASP > SQL date problem!?

>> And I bet there'll[color=blue][color=green]
>> be an error or September 13th ... Or is there a way to define in SQL
>> Server something similar to the Default Value: Now() in Access? Then I
>> could drop this field on the ASP page.[/color]
>
> Perhaps, I do not use SQL server. now() will probably include the actual
> serverlocal time. I suspect that is not what you want.[/color]

set default value for column to GETDATE() (SQL Server) [or SYSDATE (Oracle)]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_4z51.asp

"Evertjan." <exjxw.hannivoo rt@interxnl.net > wrote in message
news:Xns955878A 882A23eejj99@19 4.109.133.29...[color=blue]
> Jerome wrote on 02 sep 2004 in microsoft.publi c.inetserver.as p.general:[color=green]
>> 1. birthday field
>> SQL server will only accept it if entered in MM/DD/YYYY.[/color]
>
> No, the general accepted format is yyyy-mm-dd
>[color=green]
>> Which is bad
>> since we're in Europe, people are accustomed to DD/MM/YYYY. I tried to
>> use LCID but that didn't work either. Funny thing is when the birthday
>> is entered in MM/DD/YYYY on the page, it later gets displayed as
>> DD/MM/YYYY in SQL Server!?[/color]
>
> SQL server does not "display"
>[color=green]
>> Which is good though! (It would be better
>> of course if it could be entered in DD/MM/YYYY and be recorder in SQL
>> as such)[/color]
>
> In a database field with the type date(!!), as you are using here,
> the date is stored as a number.
>[color=green]
>> 2. field with the current date & time
>> The ASP page displays it correctly as 02/09/2004 (September 2nd). But
>> SQL Server saves it as 09/02/2004 (February 9th).[/color]
>
> You should "give" it to the SQL string as yyyy-mm-dd or yyyy/mm/dd
>[color=green]
>> And I bet there'll
>> be an error or September 13th ... Or is there a way to define in SQL
>> Server something similar to the Default Value: Now() in Access? Then I
>> could drop this field on the ASP page.[/color]
>
> Perhaps, I do not use SQL server. now() will probably include the actual
> serverlocal time. I suspect that is not what you want.
>
> But if you 'give' it to the db as a ASP string, convert your input to a
> yyyy-mm-dd string
>
> If you retrieve it from the databas, and it comes as mm/dd/yyyy, convert
> it by ASP(vbs/jscript) to you preferred string.
>[color=green]
>> So somehow there's a complete confusion!?
>>
>> How can I streamline that or where on the Internet could I find more
>> info about it?[/color]
>
> Concluding:
>
> Make two functions,
> one converting your preferred date string to a yyyy-mm-dd string
> and
> one converting the database output string to your preferred date string.
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress,
> but let us keep the discussions in the newsgroup)
>[/color]

[Evertjan.]

Re: ASP &gt; SQL date problem!?

Thomas Dodds wrote on 02 sep 2004 in
microsoft.publi c.inetserver.as p.general:
[color=blue][color=green][color=darkred]
>>> And I bet there'll
>>> be an error or September 13th ... Or is there a way to define in SQL
>>> Server something similar to the Default Value: Now() in Access? Then
>>> I could drop this field on the ASP page.[/color]
>>
>> Perhaps, I do not use SQL server. now() will probably include the
>> actual serverlocal time. I suspect that is not what you want.[/color]
>
> set default value for column to GETDATE() (SQL Server) [or SYSDATE
> (Oracle)]
>
> http://msdn.microsoft.com/library/de...y/en-us/tsqlre
> f/ts_ga-gz_4z51.asp
>
>
> "Evertjan." <exjxw.hannivoo rt@interxnl.net > wrote in message
> news:Xns955878A 882A23eejj99@19 4.109.133.29...[color=green]
>> Jerome wrote on 02 sep 2004 in
>> microsoft.publi c.inetserver.as p.general:[color=darkred]
>>> 1. birthday field
>>> SQL server will only accept it if entered in MM/DD/YYYY.[/color]
>>
>> No, the general accepted format is yyyy-mm-dd
>>[color=darkred]
>>> Which is bad
>>> since we're in Europe, people are accustomed to DD/MM/YYYY. I tried
>>> to use LCID but that didn't work either. Funny thing is when the
>>> birthday is entered in MM/DD/YYYY on the page, it later gets
>>> displayed as DD/MM/YYYY in SQL Server!?[/color]
>>
>> SQL server does not "display"
>>[color=darkred]
>>> Which is good though! (It would be better
>>> of course if it could be entered in DD/MM/YYYY and be recorder in
>>> SQL as such)[/color]
>>
>> In a database field with the type date(!!), as you are using here,
>> the date is stored as a number.
>>[color=darkred]
>>> 2. field with the current date & time
>>> The ASP page displays it correctly as 02/09/2004 (September 2nd).
>>> But SQL Server saves it as 09/02/2004 (February 9th).[/color]
>>
>> You should "give" it to the SQL string as yyyy-mm-dd or yyyy/mm/dd
>>[color=darkred]
>>> And I bet there'll
>>> be an error or September 13th ... Or is there a way to define in SQL
>>> Server something similar to the Default Value: Now() in Access? Then
>>> I could drop this field on the ASP page.[/color]
>>
>> Perhaps, I do not use SQL server. now() will probably include the
>> actual serverlocal time. I suspect that is not what you want.
>>
>> But if you 'give' it to the db as a ASP string, convert your input to
>> a yyyy-mm-dd string
>>
>> If you retrieve it from the databas, and it comes as mm/dd/yyyy,
>> convert it by ASP(vbs/jscript) to you preferred string.
>>[color=darkred]
>>> So somehow there's a complete confusion!?
>>>
>>> How can I streamline that or where on the Internet could I find more
>>> info about it?[/color]
>>
>> Concluding:
>>
>> Make two functions,
>> one converting your preferred date string to a yyyy-mm-dd string
>> and
>> one converting the database output string to your preferred date
>> string.[/color][/color]

Certainly not topposting, nor interposting, nor topquoting.
Thomas, wat is this?

Now() I am sure you ment well,
just forgot to delete the bottom part.

;-}

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress,
but let us keep the discussions in the newsgroup)

[CJM]

Re: ASP &gt; SQL date problem!?

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:OWpWAWOkEH A.548@TK2MSFTNG P11.phx.gbl...[color=blue]
>
> Wron. SQL Server will also accept the ISO standard date:
>
> YYYYMMDD
>
>[/color]

I agree with your advice, Bob, but I'm uneasy about the use of the word
'Standard'.

ISO 8601 states that the standard date format is YYYY-MM-DD, but then goes
on the explain that other delimiters are acceptable as is their omission.

So YYYYMMDD, YYYY-MM-DD and YY-MM-DD are all 'standard' date formats that
meet the ISO 8601 requirements. If you add a time element, as is often
important in DB work then you have further variations still, eg.
YYYY-MM-DDTHH:MM:SS.

Unfortunately, different DB's only accept a subset of the valid formats! So
there's often no way to code some SQL that is truly portable.

Further reading:
RFC3339
ISO8601
etc...

Not quite as straightforward as it should be, perhaps?

Chris

[Thomas Dodds]

Re: ASP &gt; SQL date problem!?

right - pick on me for having the tech skills to answer the question ....
[color=blue]
> Certainly not topposting, nor interposting, nor topquoting.
> Thomas, wat is this?
>
> Now() I am sure you ment well,
> just forgot to delete the bottom part.
>
> ;-}
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress,
> but let us keep the discussions in the newsgroup)
>[/color]