Dates in SQL Server

[JP SIngh]

I have a SQL Database with an ASP front end.

There are actually two webservers which connect to the SQL database in UK.

i have two questions

1. How can I insert null values when inserting dates within my records. If
gives me a type mismatch error is the form field was empty.

2. How will the sql server differentiate that the date coming from UK
webserver example "03/01/2005" is 3rd January 2005 and the same date coming
from US webserver "03/01/2005" is 1st March 2005. Do I need to deal with
this in the ASP code? or maybe I need to convert them to a more generic
format. Does anyone know of a fool proof function to convert the dates into
universal formats.

Thanks in advance for your help.

Regards

 

[Bob Barrows [MVP]]

I have a SQL Database with an ASP front end.

There are actually two webservers which connect to the SQL database
in UK.

i have two questions

1. How can I insert null values when inserting dates within my
records. If gives me a type mismatch error is the form field was
empty.

Pass Null if the form field is empty

2. How will the sql server differentiate that the date coming from UK
webserver example "03/01/2005" is 3rd January 2005 and the same date
coming from US webserver "03/01/2005" is 1st March 2005. Do I need to
deal with this in the ASP code? or maybe I need to convert them to a
more generic format. Does anyone know of a fool proof function to
convert the dates into universal formats.

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


HTH,
Bob Barrows

 

[Aaron Bertrand [SQL Server MVP]]

2. How will the sql server differentiate that the date coming from UK

webserver example "03/01/2005" is 3rd January 2005 and the same date
coming
from US webserver "03/01/2005" is 1st March 2005.

Don't use silly and ambiguous date formats. Pass YYYYMMDD. The database
won't get confused, nor will any of your users.

http://www.aspfaq.com/2023

 

[SP]

SQL Server will store dates in its format, as long as it's a valid date
format, SQL Server should recognize it when doing updates or inserts.
For example
"Update myTable set myDatetime = " & cdate(strDateEntered) & "where id = " &
Idnumber

The presentation is, just that... formatting data on display.
when you pull your date from the database you should just format it in the
way you want to view it.
for example
"Select convert(varchar(10),myDatetime,1) as MyDate FROM MyTable where Id =
" & IdNumber

in this case you will see the MyDate displayed as "MM/DD/YY" of course there
are other format options so have fun playing with them.

Cheers,

SP