Date Handling in SQL Server

I have a SQL Database with an ASP front end.
There are actually two webservers which connect to the SQL database in
London.
i have two questions
1. How can I insert null values when inserting dates within my records. If
gives me a type mismatch error.
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?
Thanks in advance for your help.
RegardsHi,
1. Hope you are using the insert this way: insert into dbo.tablename
(test) values (null)
2. I think you have common SQL server for storing data from US and UK.
Actually, I have one question here - where are you trying to
filter/segregate the data on presentation layer or at the database end?
In case of ASP, since you have 2 webservers, client connecting from
respective location should be able to see their dates according to
their regional settings.
Coming back to SQL Server, I don't think wherein SQL Server can handle
both US and UK date formats in the same column, maybe u have to keep a
constraint say check constraint which will check for a column say
country "US" then use US date format or "UK" then use UK date format..
Hope I got you right on your question..
Regards,
Dilip|||The specifying a date in yyyy/mm/dd format should be interpreted the same
for both US and British. For example: 2005/03/01
"JP SIngh" <none@.none.com> wrote in message
news:Oq7pfo0lFHA.3568@.tk2msftngp13.phx.gbl...
>I have a SQL Database with an ASP front end.
> There are actually two webservers which connect to the SQL database in
> London.
> i have two questions
> 1. How can I insert null values when inserting dates within my records. If
> gives me a type mismatch error.
> 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?
> Thanks in advance for your help.
> Regards
>|||"JT" <someone@.microsoft.com> wrote in message
news:OGCNx41lFHA.1416@.TK2MSFTNGP09.phx.gbl...
> The specifying a date in yyyy/mm/dd format should be interpreted the same
> for both US and British. For example: 2005/03/01
No - use a format that is understood regardless of any settings.
http://www.karaszi.com/sqlserver/info_datetime.asp

Date handling in SQL Server

I need to make a decision regarding date handling to continue as is or reope
n
and modify completed development, if it is felt that current approach was no
t
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All program
s
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren’t we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.In my opinion, when a data is unavailable/unknown, you should set it to
NULL, instead of hardcoding your applications to look for certain very old
or very futuristic dates.
You could use new columns to indicate the status of rows, instead of using
hardcoded date values.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
I need to make a decision regarding date handling to continue as is or
reopen
and modify completed development, if it is felt that current approach was
not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All
programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.|||Thanks for your suggestion. How would you determine terminated records?
James
"Narayana Vyas Kondreddi" wrote:

> In my opinion, when a data is unavailable/unknown, you should set it to
> NULL, instead of hardcoding your applications to look for certain very old
> or very futuristic dates.
> You could use new columns to indicate the status of rows, instead of using
> hardcoded date values.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
> I need to make a decision regarding date handling to continue as is or
> reopen
> and modify completed development, if it is felt that current approach was
> not
> he best.
> All development to date has been done with the use of two default dates.
> There is one default date 01/01/1800 used for all date fields except
> term_date. For term_date a forever date of 01/01/2900 is used. All
> programs
> identify terminated records as term_date less than 01/01/2900.
> My questions are:
> Why aren't we using Null values
> Why two default dates instead of just one.
> What standard does your company use? What is the most common approach bei
ng
> used/ Any input you can provide will help us decide our forward direction
.
> Thanks.
>
>|||I agree with Vyas... However if some rows have a valid termination date and
others do not,,, only place a date value when it is know, and do not default
to some max value...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:B52B23B0-6BCA-4333-A15F-FFA7DC9347D0@.microsoft.com...[vbcol=seagreen]
> Thanks for your suggestion. How would you determine terminated records?
> James
> "Narayana Vyas Kondreddi" wrote:
>

Date handling in SQL Server

I need to make a decision regarding date handling to continue as is or reopen
and modify completed development, if it is felt that current approach was not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren’t we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.
In my opinion, when a data is unavailable/unknown, you should set it to
NULL, instead of hardcoding your applications to look for certain very old
or very futuristic dates.
You could use new columns to indicate the status of rows, instead of using
hardcoded date values.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
I need to make a decision regarding date handling to continue as is or
reopen
and modify completed development, if it is felt that current approach was
not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All
programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.
|||Thanks for your suggestion. How would you determine terminated records?
James
"Narayana Vyas Kondreddi" wrote:

> In my opinion, when a data is unavailable/unknown, you should set it to
> NULL, instead of hardcoding your applications to look for certain very old
> or very futuristic dates.
> You could use new columns to indicate the status of rows, instead of using
> hardcoded date values.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
> I need to make a decision regarding date handling to continue as is or
> reopen
> and modify completed development, if it is felt that current approach was
> not
> he best.
> All development to date has been done with the use of two default dates.
> There is one default date 01/01/1800 used for all date fields except
> term_date. For term_date a forever date of 01/01/2900 is used. All
> programs
> identify terminated records as term_date less than 01/01/2900.
> My questions are:
> Why aren't we using Null values
> Why two default dates instead of just one.
> What standard does your company use? What is the most common approach being
> used/ Any input you can provide will help us decide our forward direction.
> Thanks.
>
>
|||I agree with Vyas... However if some rows have a valid termination date and
others do not,,, only place a date value when it is know, and do not default
to some max value...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:B52B23B0-6BCA-4333-A15F-FFA7DC9347D0@.microsoft.com...[vbcol=seagreen]
> Thanks for your suggestion. How would you determine terminated records?
> James
> "Narayana Vyas Kondreddi" wrote:

Date handling in SQL Server

I need to make a decision regarding date handling to continue as is or reopen
and modify completed development, if it is felt that current approach was not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why arenâ't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.In my opinion, when a data is unavailable/unknown, you should set it to
NULL, instead of hardcoding your applications to look for certain very old
or very futuristic dates.
You could use new columns to indicate the status of rows, instead of using
hardcoded date values.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
I need to make a decision regarding date handling to continue as is or
reopen
and modify completed development, if it is felt that current approach was
not
he best.
All development to date has been done with the use of two default dates.
There is one default date 01/01/1800 used for all date fields except
term_date. For term_date a forever date of 01/01/2900 is used. All
programs
identify terminated records as term_date less than 01/01/2900.
My questions are:
Why aren't we using Null values
Why two default dates instead of just one.
What standard does your company use? What is the most common approach being
used/ Any input you can provide will help us decide our forward direction.
Thanks.|||Thanks for your suggestion. How would you determine terminated records?
James
"Narayana Vyas Kondreddi" wrote:
> In my opinion, when a data is unavailable/unknown, you should set it to
> NULL, instead of hardcoding your applications to look for certain very old
> or very futuristic dates.
> You could use new columns to indicate the status of rows, instead of using
> hardcoded date values.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
> I need to make a decision regarding date handling to continue as is or
> reopen
> and modify completed development, if it is felt that current approach was
> not
> he best.
> All development to date has been done with the use of two default dates.
> There is one default date 01/01/1800 used for all date fields except
> term_date. For term_date a forever date of 01/01/2900 is used. All
> programs
> identify terminated records as term_date less than 01/01/2900.
> My questions are:
> Why aren't we using Null values
> Why two default dates instead of just one.
> What standard does your company use? What is the most common approach being
> used/ Any input you can provide will help us decide our forward direction.
> Thanks.
>
>|||I agree with Vyas... However if some rows have a valid termination date and
others do not,,, only place a date value when it is know, and do not default
to some max value...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:B52B23B0-6BCA-4333-A15F-FFA7DC9347D0@.microsoft.com...
> Thanks for your suggestion. How would you determine terminated records?
> James
> "Narayana Vyas Kondreddi" wrote:
>> In my opinion, when a data is unavailable/unknown, you should set it to
>> NULL, instead of hardcoding your applications to look for certain very
>> old
>> or very futuristic dates.
>> You could use new columns to indicate the status of rows, instead of
>> using
>> hardcoded date values.
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>>
>> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
>> news:7F85F760-6C09-4579-98FC-D3EC1FD9FE73@.microsoft.com...
>> I need to make a decision regarding date handling to continue as is or
>> reopen
>> and modify completed development, if it is felt that current approach was
>> not
>> he best.
>> All development to date has been done with the use of two default dates.
>> There is one default date 01/01/1800 used for all date fields except
>> term_date. For term_date a forever date of 01/01/2900 is used. All
>> programs
>> identify terminated records as term_date less than 01/01/2900.
>> My questions are:
>> Why aren't we using Null values
>> Why two default dates instead of just one.
>> What standard does your company use? What is the most common approach
>> being
>> used/ Any input you can provide will help us decide our forward
>> direction.
>> Thanks.
>>