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.
>>

Date Format Question

Hi all - I have a question regarding formating getdate(). Below is my query.

SELECT

cast(datepart(yyyy, getdate()) as char(4))

+'-'+ cast(datepart(mm,getdate()) as char(2))

+'-'+ cast(datepart(dd, getdate()) as char(2))

Result

2007-5 -30

The result I would like is:

2007-05-30

Can anyone help me with this?

Thanks in Advance.

You could use:

Code Snippet

SELECT convert( varchar(10), getdate(), 120 )

-
2007-05-30

Refer to Books Online, Topic: 'Cast and Convert' for the usage of the 'style' codes (the 120 above).

Date format change in the server

Hi,
Can anyone help me to solve this query regarding Date format in SQL Server
Is it possible to change the Date format in the SQL Database server. Is
this dependent upon the system date settings in the Control Panel.
Regards,
VB Babunath
Hi
SQL Server stores the data an a number and does not care about the setting
at OS level. Date format display is determined by the client and has
nothing to do with the server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"VBB" <VBB@.discussions.microsoft.com> wrote in message
news:A2D2D692-6699-4EF2-B0E7-C75B9E1609D5@.microsoft.com...
> Hi,
> Can anyone help me to solve this query regarding Date format in SQL Server
> Is it possible to change the Date format in the SQL Database server. Is
> this dependent upon the system date settings in the Control Panel.
> Regards,
> VB Babunath
|||See "set dateformat" in BOL, but better use ISO format (see convert function,
styles 112 and 126), because sql server will always interpret the values as
datetime, independently of the language and "set dateformat" setting.
Example:
set dateformat mdy
go
select cast('20050922' as datetime)
go
-- error
select cast('22/09/2005' as datetime)
go
set dateformat dmy
go
select cast('20050922' as datetime)
go
-- error
select cast('09/22/2005' as datetime)
go
AMB
"VBB" wrote:

> Hi,
> Can anyone help me to solve this query regarding Date format in SQL Server
> Is it possible to change the Date format in the SQL Database server. Is
> this dependent upon the system date settings in the Control Panel.
> Regards,
> VB Babunath
|||The question is very broad. I suggest you start by reading
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"VBB" <VBB@.discussions.microsoft.com> wrote in message
news:A2D2D692-6699-4EF2-B0E7-C75B9E1609D5@.microsoft.com...
> Hi,
> Can anyone help me to solve this query regarding Date format in SQL Server
> Is it possible to change the Date format in the SQL Database server. Is
> this dependent upon the system date settings in the Control Panel.
> Regards,
> VB Babunath

Date format change in the server

Hi,
Can anyone help me to solve this query regarding Date format in SQL Server
Is it possible to change the Date format in the SQL Database server. Is
this dependent upon the system date settings in the Control Panel.
Regards,
VB BabunathHi
SQL Server stores the data an a number and does not care about the setting
at OS level. Date format display is determined by the client and has
nothing to do with the server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"VBB" <VBB@.discussions.microsoft.com> wrote in message
news:A2D2D692-6699-4EF2-B0E7-C75B9E1609D5@.microsoft.com...
> Hi,
> Can anyone help me to solve this query regarding Date format in SQL Server
> Is it possible to change the Date format in the SQL Database server. Is
> this dependent upon the system date settings in the Control Panel.
> Regards,
> VB Babunath|||See "set dateformat" in BOL, but better use ISO format (see convert function,
styles 112 and 126), because sql server will always interpret the values as
datetime, independently of the language and "set dateformat" setting.
Example:
set dateformat mdy
go
select cast('20050922' as datetime)
go
-- error
select cast('22/09/2005' as datetime)
go
set dateformat dmy
go
select cast('20050922' as datetime)
go
-- error
select cast('09/22/2005' as datetime)
go
AMB
"VBB" wrote:
> Hi,
> Can anyone help me to solve this query regarding Date format in SQL Server
> Is it possible to change the Date format in the SQL Database server. Is
> this dependent upon the system date settings in the Control Panel.
> Regards,
> VB Babunath|||The question is very broad. I suggest you start by reading
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"VBB" <VBB@.discussions.microsoft.com> wrote in message
news:A2D2D692-6699-4EF2-B0E7-C75B9E1609D5@.microsoft.com...
> Hi,
> Can anyone help me to solve this query regarding Date format in SQL Server
> Is it possible to change the Date format in the SQL Database server. Is
> this dependent upon the system date settings in the Control Panel.
> Regards,
> VB Babunath

Date format change in the server

Hi,
Can anyone help me to solve this query regarding Date format in SQL Server
Is it possible to change the Date format in the SQL Database server. Is
this dependent upon the system date settings in the Control Panel.
Regards,
VB BabunathHi
SQL Server stores the data an a number and does not care about the setting
at OS level. Date format display is determined by the client and has
nothing to do with the server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"VBB" <VBB@.discussions.microsoft.com> wrote in message
news:A2D2D692-6699-4EF2-B0E7-C75B9E1609D5@.microsoft.com...
> Hi,
> Can anyone help me to solve this query regarding Date format in SQL Server
> Is it possible to change the Date format in the SQL Database server. Is
> this dependent upon the system date settings in the Control Panel.
> Regards,
> VB Babunath|||See "set dateformat" in BOL, but better use ISO format (see convert function
,
styles 112 and 126), because sql server will always interpret the values as
datetime, independently of the language and "set dateformat" setting.
Example:
set dateformat mdy
go
select cast('20050922' as datetime)
go
-- error
select cast('22/09/2005' as datetime)
go
set dateformat dmy
go
select cast('20050922' as datetime)
go
-- error
select cast('09/22/2005' as datetime)
go
AMB
"VBB" wrote:

> Hi,
> Can anyone help me to solve this query regarding Date format in SQL Server
> Is it possible to change the Date format in the SQL Database server. Is
> this dependent upon the system date settings in the Control Panel.
> Regards,
> VB Babunath|||The question is very broad. I suggest you start by reading
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"VBB" <VBB@.discussions.microsoft.com> wrote in message
news:A2D2D692-6699-4EF2-B0E7-C75B9E1609D5@.microsoft.com...
> Hi,
> Can anyone help me to solve this query regarding Date format in SQL Server
> Is it possible to change the Date format in the SQL Database server. Is
> this dependent upon the system date settings in the Control Panel.
> Regards,
> VB Babunath

Date based filter in merge replication

Hopefully, someone can provide some advice regarding filtering data by dates.
We have data that we want to expire from the sqlce database because it has reached a certain age. Filters have been applied to only replicate data wihtin the date boundaries we require.
What we have found through testing is that when we increment server and device dates to a date where data should expire, it is not removed from the database on the device. During this testing we made sure our snapshot was updated before applying more chan
ges through our application, allowing us to see data travelling to and from the device. But, alas, the outdated data survived.
So, I suppose it makes sense that if a record is not updated, it will not be affected by replication - or is there some way around this?
Other than archiving data on the server side which has its own set of implications (ms_MergeTombstone growing excessively, effects on historical reports), is there some other technique we can use to limit the volume of data on the device?
Thanks
Steve
Steve,
As you mentioned already, records that are not updated are not affected by
replication. Creating a new snapshot won't help on this side either. So the
technique I term as 'Induced Updates' could be applied in your case. This
simply means making an update in the table for all rows you want replication
to re-assess without changing the data. This sample script will demo:
update YourTable
set column_name = column_name
where date_column = some_date_criteria
You can also create a job to do this periodically, where some_date_criteria
can be dependent on the getdate() function.
Hope the above helps.
Raj Moloye.
|||Steve,
As you mentioned already, records that are not updated are not affected by
replication. Creating a new snapshot won't help on this side either. So the
technique I term as 'Induced Updates' could be applied in your case. This
simply means making an update in the table for all rows you want replication
to re-assess without changing the data. This sample script will demo:
update YourTable
set column_name = column_name
where date_column = some_date_criteria
You can also create a job to do this periodically, where some_date_criteria
can be dependent on the getdate() function.
Hope the above helps.
Raj Moloye.
|||Thanks Raj,
I have to say I was starting to think along those lines as I wrote the original post.
When I update these rows that are out of date range, does replication cause these updated rows to be removed from the device?
I think some testing might be in order here just to confirm what you are saying.
Thanks again.
SteveM
"Raj Moloye" wrote:

> Steve,
> As you mentioned already, records that are not updated are not affected by
> replication. Creating a new snapshot won't help on this side either. So the
> technique I term as 'Induced Updates' could be applied in your case. This
> simply means making an update in the table for all rows you want replication
> to re-assess without changing the data. This sample script will demo:
> update YourTable
> set column_name = column_name
> where date_column = some_date_criteria
> You can also create a job to do this periodically, where some_date_criteria
> can be dependent on the getdate() function.
> Hope the above helps.
> Raj Moloye.
>
>
|||Thanks Raj,
I have to say I was starting to think along those lines as I wrote the original post.
When I update these rows that are out of date range, does replication cause these updated rows to be removed from the device?
I think some testing might be in order here just to confirm what you are saying.
Thanks again.
SteveM
"Raj Moloye" wrote:

> Steve,
> As you mentioned already, records that are not updated are not affected by
> replication. Creating a new snapshot won't help on this side either. So the
> technique I term as 'Induced Updates' could be applied in your case. This
> simply means making an update in the table for all rows you want replication
> to re-assess without changing the data. This sample script will demo:
> update YourTable
> set column_name = column_name
> where date_column = some_date_criteria
> You can also create a job to do this periodically, where some_date_criteria
> can be dependent on the getdate() function.
> Hope the above helps.
> Raj Moloye.
>
>