I would like to have a field in a table that is a Date only data type instea
d
of the datetime. Could someone offer any advice on this in SQL Server 2005.
ThanksDoesn't exists
You could store YYYYMMDD in a char(8) field instead
http://sqlservercode.blogspot.com/|||No such thing.
http://www.aspfaq.com/2206
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||>> I would like to have a field [sic] in a table that is a DATE only data type in
stead of the DATETIME. Could someone offer any advice on this in SQL Serve
r 2005. <<
You might want to learn the basics of RDBMS, so that you do not confuse
fields and columns.
Now, to answer your question: do it the right way! Time is not a point
(Chronons), but a duration expressed as half-open intervals. Thus a
day is really "[yyyy-mm-dd 00:00:00, yyyy-mm-dd 23:59:59.999..)" in the
ISO temporal model.
Read Rick Snodgrass at Univeristy of AZ for more details.|||If you want to treat the data as a date, without a time value, just ignore
the time value when your application updates or selects from the database.
The only time it makes a difference is if you are comparing two date values.
If you always insert/update your date values without specifying the time,
then the time is set to midnight of that day. As long as you are
consistent, this has the same effect as if you didn't store the time at all,
and you wont run into issues when comparing dates.
You could build in a trigger that insures the time value is set to 00:00:00
on inserts and updates as well, which would guarantee consistency even if
the application programming mistakenly sets the time to another value.
The only other real difference is storage, and I doubt it is significant
enough to even give consideration to with today's storage costs.
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
> I would like to have a field in a table that is a Date only data type
instead
> of the datetime. Could someone offer any advice on this in SQL Server
2005.
> Thanks
>|||> The only other real difference is storage, and I doubt it is significant
> enough to even give consideration to with today's storage costs.
Well, I would suggest that if you only care about the date, then using
SMALLDATETIME will minimize storage requirements and make indexes more
efficient, and I don't know of any downside...
A|||I would agree with others that a Date only type has only marginal value as
you could treat all datetimes as 00:00:00.000 time to get the same result.
However, a Date only type can be mentally easier to deal with as you know
time can not effect what your doing. I actually added a TDate and TTime
UDTs to a sql project I did. Can use it as is or update it as needed if you
don't like the api choices. Get the project at link below.
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I would agree with others that a Date only type has only marginal value as
I don't think so. People have been screaming for this for many years,
and I would very disappointed if it is not in the next version of SQL
Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||With 2005, they don't have to wait, unless they want to. I agree it has
some value, but maybe I missed a whole bunch of use cases. What are some of
the more profound use cases for Date only you have come across? TIA Erland.
William Stacey [MVP]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974953149317Yazorman@.127.0.0.1...
> William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I don't think so. People have been screaming for this for many years,
> and I would very disappointed if it is not in the next version of SQL
> Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||> With 2005, they don't have to wait, unless they want to.
Why? The Date and Time datatypes were dropped from the product very early
on. I think most of it was due to the volume with which we complained about
its implementation (and by we I do mean myself, Erland and others). I'm
sure that's not what you mean, so while, yes, you could create your own UDT,
have fun with that, and let us know when you have something marginally
useful! The most complex UDT I've seen to date that was actually useable
was POINT. There is so much involved with date validation and
interoperability that it is unlikely you would be able to develop something
that would seamlessly integrate with the rest of the product (most notably
implicit conversion to/from datetime and acceptance as inputs to functions
like DATEDIFF/DATEADD/YEAR/MONTH/DAY).
> What are some of the more profound use cases for Date only you have come
> across?
A calendar table. Hire/fire date. Birth date. I'm sure if I spent more
than two minutes and reviewed all of the projects I've been involved with in
the past 10 years, I could come up with dozens of others. None of these
need time in most cases, and life would be much simpler if we didn't have to
truncate/validate/correct data going in or coming out, or when comparing, or
when displaying, or when exporting to XML, or ...
Showing posts with label advice. Show all posts
Showing posts with label advice. Show all posts
Wednesday, March 21, 2012
Tuesday, February 14, 2012
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.
>
>
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.
>
>
Subscribe to:
Posts (Atom)