Help on ASP SQL date format problem

[mewanalwis]

Dear Friends,

I have a rather strange problem which invloves SQL server and ASP. The
problem is this.


I have an ASP application which use a SQL server. it saves date values
with MM/dd/yyyy format. the SQL server and every other computer is
configured to MM/dd/yyyy format in reginol settings. The DSN which use
to connect to SQL server is configured to use Reginol settings date
format.


But I for ex. when I save date 03/01/2006 (MM/dd/yyyy) using the
computer which runs sql server it saves right. But when I use other
computer it saves as 01/03/2006.


I'm really stuck in this mess. Please help me on this. Thank you.


Mewan
Sri Lanka

 

[Evertjan.]

wrote on 22 mrt 2006 in microsoft.public.inetserver.asp.general:

I have a rather strange problem which invloves SQL server and ASP. The
problem is this.


I have an ASP application which use a SQL server. it saves date values
with MM/dd/yyyy format. the SQL server and every other computer is
configured to MM/dd/yyyy format in reginol settings. The DSN which use
to connect to SQL server is configured to use Reginol settings date
format.


But I for ex. when I save date 03/01/2006 (MM/dd/yyyy) using the
computer which runs sql server it saves right. But when I use other
computer it saves as 01/03/2006.

No,No,no.

Dates saved in most databases in a date/time field are saved in an internal
numeric structure.

The only dependable date-litteral input to an sql string is #yyyy/mm/dd#
and the databse output should be put in a date variable where the
serverside script can extract d, m and y from.

 

[mewanalwis]

Dear Evertjan,
Thanks for your quick reply.

Thw thing is I don't specify any date format when i'm saving or editing
data. And the other thing is I simply enter the date in a asp text box
in MM/dd/yyyy format. do i have to type it in yyyy/mm/dd format. Thank
you.

Mewan

 

[mewanalwis]

Dear Evertjan,
Thanks for your quick reply.

Thw thing is I don't specify any date format when i'm saving or editing
data. And the other thing is I simply enter the date in a asp text box
in MM/dd/yyyy format. do i have to type it in yyyy/mm/dd format. Thank
you.

Mewan

 

[Bob Barrows [MVP]]

Dear Evertjan,
Thanks for your quick reply.

Thw thing is I don't specify any date format when i'm saving or
editing data. And the other thing is I simply enter the date in a asp
text box in MM/dd/yyyy format. do i have to type it in yyyy/mm/dd
format. Thank you.

http://www.aspfaq.com/show.asp?id=2313
http://www.aspfaq.com/show.asp?id=2040
http://www.aspfaq.com/show.asp?id=2260

 

[Evertjan.]

wrote on 22 mrt 2006 in microsoft.public.inetserver.asp.general:

Thw thing is I don't specify any date format when i'm saving or editing
data.

That is what I said, databases do NOT store data in a readable format.

And the other thing is I simply enter the date in a asp text box

Asp text boxes do not exist, as ASP is serverside only.

Maybe in asp.net, but if that is what you mean
you are posting in the wrong NG.
This is a classic ASP only NG.

in MM/dd/yyyy format. do i have to type it in yyyy/mm/dd format.

No, you are the programmer, and between the form-posting and the entry in
the database, you put your own conversion code.

 

[mewanalwis]

Hi,

Thank you very much for your responces. What I mean by text box is the
basic asp form text box. anyway i followed ur advice and now i'm
entering the date as yyyy/mm/dd format. I don't do any formating in
between. so now the problem is solved. Thank you very much.

Mewan

 

[Evertjan.]

wrote on 24 mrt 2006 in microsoft.public.inetserver.asp.general:

Thank you very much for your responces. What I mean by text box is the
basic asp form text box. anyway i followed ur advice and now i'm
entering the date as yyyy/mm/dd format. I don't do any formating in
between. so now the problem is solved. Thank you very much.

[please do not toppost on usenet]

Mewan, basic ASP, classic ASP has no "asp form text box"es

So you are talking ASP.NET ????

I will answer for the classic ASP, that is the topic of this NG.

My idea is, that some form of checking between a submitted
form-input-text box content and the insertion of theat into a serverside
database is required.

On an open internet, the risk of injection has to be delt with, and if
the form is only available to yourself, you still have to make it as
easy as possible to avoid common mistakes.

an European (d)d-(m)m-yyyy input can be converted by (ASP-vbs):

insertdate(request.form("d"))

function insertdate(theFormValue)
'' injection defence here
d = split(theFormValue,"-")
if ubound(d)<>3 then return
d2 = "#"&d(2)&"/"&d(1)&"/"&d(0)&"#"
''' etc '''
end function

American (m)m-(d)d-yyyy will need:
d2 = "#"&d(2)&"/"&d(0)&"/"&d(1)&"#"