ASP & SQL date problem 1

[ufobaby]

hi experts,

am passing a date in "dd/mm/yyyy" fromat (reading from a file) to a table in SQL database. but when i see the table the date is converted.
ex - i enter 08/04/2004 (8th April 2004)
the table shows 04/08/2004 (4th August 2004)
the data type of the field is date

am using SQL 2000 and ASP 3.0 on windows 2000. the regional setting of the machine are "dd/mm/yyyy"

what could be the problem ... / solution ...
pls help.

__________

Niraj...
"The unexpected success is not just an opportunity for innovation. It demands innovation."

 

[SkipVought]

ufobaby,

I'd NORMALIZE when it comes to dates and formats.

On Input

MyYear = 2004
MyMonth = 4
MyDay = 8
MyDate = DateSerial(MyYear, MyMonth, MyDay)

then load the table.
Then on the backend

MyYear = Year([TDate])
MyMonth = Month([TDate])
MyDay = Day([TDate])
DsplyDate = MyDay & "/" & MyMonth & "/" & MyYear

[/code]


Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[ufobaby]

skip, thanks for the quick response, but the problem is the file am uploading is hughe and runs in to thousands for records and if for every record i do this , it will be very time consuming

any thing else, better than this ... thanks

__________

Niraj...
"The unexpected success is not just an opportunity for innovation. It demands innovation."

 

[Jeremy21b]

Date format can be difficult with SQL. It insists on using its format and there is little flexibility. Perhaps SmallDateTime works better? You could also just use nvarchar as the datatype and it would enter the exact same date. If that doesn't work, you might be stuck using Skip's code. The database might be storing the correct info, but just reformatting it. Try an SQL query ordered by date to check. If it is, then you could just use
FormatDateTime(todaysDate,0)

Another option is uploading it as is, and then creating a script that goes through the database switching the date/month order.

 

[SkipVought]

You don't do this for EVERY RECORD!

You do this for Every Date Column/Field.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}