DB - DDL - how much time each operation takes - a doc needed

Is there a doc describing how much time each DDL can take,
e.g. (n is the number of records in the table):
add nullable column = 0(1)
add column with default falue = o(n)
etc'...


Tal Olier
otal@.mercury.co.ilI haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?|||Originally posted by Paul Young
I haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?

No it hasn't, I am looking for something like:

# Operation DB Type Example Time
1 Rename a table Oracle Alter table x rename .. o(1)
MS-SQL Sp_rename o(1)
2 Rename a index Oracle Alter index x rename.. o(1)
MS-SQL Sp_rename t.x.. o(1)
3 Add column Oracle Alter table x add y NULL o(1)
Oracle Alter table x add y default (10)/default(null) o(n)
MS-SQL Alter table x add o(1)

DayAdd

Dear all,
I want to convert the day range from two variable into records. So, I use a
While-loop to do so
while @.CountDate <= @.TWorkDate
begin
.
.
.
-- CountDate = CountDate add one day
select @.CountDate = DATEADD(day, 1, @.CountDate)
END
When I try to run, it shows the following error:
Server: Msg 141, Level 15, State 1, Procedure sp7006_2, Line 64
A SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.
Help... The error should related to the DATEADD function but I dunno how to
handle.
IvanPlease post the entire query definition, since the error does not originate
from any of the lines that you have posted.
Look for a SELECT statement in which both a variable assignment and a result
set declaration are present. This is forbidden in a single SELECT statement.
ML
http://milambda.blogspot.com/|||This statement:
select @.CountDate = DATEADD(day, 1, @.CountDate)
does NOT create an error.
Please post the complete DDL and code so that we may have all the
information necessary to help you.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ivan" <ivan@.microsoft.com> wrote in message
news:eoU6REcmGHA.856@.TK2MSFTNGP03.phx.gbl...
> Dear all,
> I want to convert the day range from two variable into records. So, I use
> a While-loop to do so
> while @.CountDate <= @.TWorkDate
> begin
> .
> .
> .
> -- CountDate = CountDate add one day
> select @.CountDate = DATEADD(day, 1, @.CountDate)
> END
> When I try to run, it shows the following error:
> Server: Msg 141, Level 15, State 1, Procedure sp7006_2, Line 64
> A SELECT statement that assigns a value to a variable must not be combined
> with data-retrieval operations.
> Help... The error should related to the DATEADD function but I dunno how
> to handle.
> Ivan
>sql

Datetime Query

How to display records between datetime field??WHERE dateCol BETWEEN @.date1 AND @.date2

???|||how do i display records between two fields Callstartdt and endcalldt bot the fields are datetime fields...

What would be syntax for the query??

Please help..|||You really have to give us more information

Like ddl, sample data and expected results

Read the hint sticky at the top of the forum to see how to post a question here|||Do i need to post the design of the table??

How do i copy a design of the table...

I want to display all the records between two dates.|||go to enterprise mangler and right click on the table

Choose all tasks then generate sql server script

Did you read the hint link at the top of the page?

it's all explained up there, but in any case

When you say betyween 2 dates?

Which 2 dates?

Or is it a number of days betwen 2 dates

The question is not very clear, and that's why code examples would help us immensly|||How to display records between datetime field??



***********
datetime * records * field
***********|||Lmao! :D

DateTime Format problem

In SQL query I have to find records which occour between two dates. I created Select query with two parameters @.date1 and @.date2 in clasue WHERE. But problem is with date format of my parameters. This format is to long. I dont wont to use time part of these parameters only date part is needed. When I put two identical dates my query doesn't find any data because both dates are eg. 2007-05-22 00:00:00. But I need data for all this day. How to correct this problem? Regards Pawel.

Use the Convert Function to convert it to a small date it will trim the time part

Where Convert(Varchar(10),@.Date1) = Convert(Varchar(10),@.Date2) ... Also you can use the third parameter in the Convert Function to get a specific format of dates i.e dd/mm/yyyy or yyyy/mm/dd etc. For a complete list

http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx

Check the link

|||

If the goal is to retrieve data for a single day, the method I prefer is lower inclusion, upper exclusion. Let me explain:

declare @.dtdatetime, @.startDatedatetime, @.endDatedatetime-- assume this is the dateset @.dt ='2007-01-02 12:34:56'select-- if only a date portion is passed into the sproc -- you won't need to remove the time portion @.startDate =convert(char(10), @.dt, 120) , @.endDate =dateadd(day, 1, @.startDate)select a.*-- use column list here!from tbl awhere-- inclusive of the lower limit a.DateColumn >= @.startDate-- exclusive of the upper limitand a.DateColumn < @.endDate

datetime format

Hi.
I have two parameters called StartDate and EndDate.These parameters are from datetime type.
I want to view the records between these dates.I have some questions:

1)In the database, these parameters' values are like 15.11.1984 23:59:14. It has time value near the date value.But I don't want to view the time value.I only want the date part.

2)In the preview tab, I choose a date clicking the calendar image near the parameter textbox.
For example I choose 02.05.2001 and when I click the view report button, it changes to 05.02.2001.So there is a format difference.I want it to show like dd.mm.yyyy

3)By default, if the user doesn't enter a date, I want to view all the records.Any idea about this?

Thanks!

Try doing a convert on your database datetime field similar to this in your query

convert(datetime, "datefield", 104)

This will format the date as dd.mm.yyyy. You can also do this on the parameter value so they are both in the same format. Your query would look something like this:

select * from table where convert(datetime, "datefield", 104) >= convert(datetime, @.StartDate, 104) and convert(datetime, "datefield", 104) <= convert(datetime, @.EndDate,104)

To display all records you can set the default values to the maximum and minimum dates in your database. The issue with this is that everytime the report is opened, it will automatically run for all dates. Not sure how to make it work only if the user doesn't select dates.

|||kmcclung thanks for the reply.
But it didn't work.
I wrote convert(datetime, myDateField, 104) and then tried the third parameter for 103, 4, ...
But it didn't change.
Then I realized that it is not dependent on that number.
It uses only the default datetime format.
The records in my database are like dd.mm.yyyy hh:mm:ss
And after I used the CONVERT function NOTHING changed.
I only want the date part to be visible.(only want this)
And the second problem is that as I said before when I click the calendar button near the date texbox area and select a date like 15.12.2001 then it is written to textbox like 12.15.2001.
And because of not existing a month number like 15 an error occurs.
I mean that I want to change that calendar's format.

How can I correct this?|||What type of database you are using?|||

0) It sounds like your database is NOT storing dates with a DATETIME format. Why not?

1) To take '15.11.1984 23:59:14' and store it as a DATETIME with time stripped off (set to midnite):

CONVERT(DATETIME, CAST(CONVERT(DATETIME, '15.11.1984 23:59:14', 104) AS INT))

Datetime Filter

For one of the tables on my report i need to allow only the records where a
certain datetime field has Null value. I have not been able to figure out the
expression in the filter that would do this.
Any ideas?
I cannot do this in the select statement as other data regions require
records with values in the same field.You can do a filter at the table level (the Table report object, that is) or
at the list level by high-lighting the Table or List (for instance) and
selecting the filters from the Properties menu and placing a filter on the
data in the object. Hope this helps.
"Nice_Out" wrote:
> For one of the tables on my report i need to allow only the records where a
> certain datetime field has Null value. I have not been able to figure out the
> expression in the filter that would do this.
> Any ideas?
> I cannot do this in the select statement as other data regions require
> records with values in the same field.|||Thanks. I have figured out that I can do a filter.
What I am having trouble with is the expression to use in the filter. I get
the feeling that it does not like to handle Nulls.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> You can do a filter at the table level (the Table report object, that is)
or
> at the list level by high-lighting the Table or List (for instance) and
> selecting the filters from the Properties menu and placing a filter on the
> data in the object. Hope this helps.
> "Nice_Out" wrote:
> > For one of the tables on my report i need to allow only the records
where a
> > certain datetime field has Null value. I have not been able to figure
out the
> > expression in the filter that would do this.
> >
> > Any ideas?
> >
> > I cannot do this in the select statement as other data regions require
> > records with values in the same field.|||Sorry about that. If I had read more closely I would have seen that. Have
you tried the following in your filter?
set Expression to your date/time field
set Operator to "="
set Value to "= Nothing"
This is assuming you do not also need to include a date spread for when the
field does have a value. If so, let me know. I have that figured out as
well.
"Nice_Out" wrote:
> Thanks. I have figured out that I can do a filter.
> What I am having trouble with is the expression to use in the filter. I get
> the feeling that it does not like to handle Nulls.
> "Rand" <Rand@.discussions.microsoft.com> wrote in message
> news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > You can do a filter at the table level (the Table report object, that is)
> or
> > at the list level by high-lighting the Table or List (for instance) and
> > selecting the filters from the Properties menu and placing a filter on the
> > data in the object. Hope this helps.
> >
> > "Nice_Out" wrote:
> >
> > > For one of the tables on my report i need to allow only the records
> where a
> > > certain datetime field has Null value. I have not been able to figure
> out the
> > > expression in the filter that would do this.
> > >
> > > Any ideas?
> > >
> > > I cannot do this in the select statement as other data regions require
> > > records with values in the same field.
>
>|||Hey thanks!
That worked groovy.
I swear I tried everything so close to that. Don't know how I missed it.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DCE9E96F-CD79-4D43-B03A-88204095C6C0@.microsoft.com...
> Sorry about that. If I had read more closely I would have seen that.
Have
> you tried the following in your filter?
> set Expression to your date/time field
> set Operator to "="
> set Value to "= Nothing"
> This is assuming you do not also need to include a date spread for when
the
> field does have a value. If so, let me know. I have that figured out as
> well.
>
> "Nice_Out" wrote:
> > Thanks. I have figured out that I can do a filter.
> > What I am having trouble with is the expression to use in the filter. I
get
> > the feeling that it does not like to handle Nulls.
> >
> > "Rand" <Rand@.discussions.microsoft.com> wrote in message
> > news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > > You can do a filter at the table level (the Table report object, that
is)
> > or
> > > at the list level by high-lighting the Table or List (for instance)
and
> > > selecting the filters from the Properties menu and placing a filter on
the
> > > data in the object. Hope this helps.
> > >
> > > "Nice_Out" wrote:
> > >
> > > > For one of the tables on my report i need to allow only the records
> > where a
> > > > certain datetime field has Null value. I have not been able to
figure
> > out the
> > > > expression in the filter that would do this.
> > > >
> > > > Any ideas?
> > > >
> > > > I cannot do this in the select statement as other data regions
require
> > > > records with values in the same field.
> >
> >
> >

Datetime conversion under diferent versions of SQL

Hello!
I'm using the same script to insert/update records on diferent versions of
SQL but i'm getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de
dados char em um tipo de dados datetime resultou em um valor datetime fora
do intervalo.
(translation: error converting one string into datetime value out of range)
The SQL versions that I am probing is 8.00.194 (RTM) that is installed with
Microsoft SQL Personal Engine CD and ther other version is 8.00.2039 (SP4)
that i've downloaded and installed.
Can anywone help me?
Regards,
kTodosYou are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>|||... and for some extra reading: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) and this is okay on one
> server (which may have British language settings) but not on another (which may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
>> Hello!
>> I'm using the same script to insert/update records on diferent versions of SQL but i'm getting
>> this error:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]A conversão de um tipo de dados char em um tipo de
>> dados datetime resultou em um valor datetime fora do intervalo.
>> (translation: error converting one string into datetime value out of range)
>> The SQL versions that I am probing is 8.00.194 (RTM) that is installed with Microsoft SQL
>> Personal Engine CD and ther other version is 8.00.2039 (SP4) that i've downloaded and installed.
>> Can anywone help me?
>> Regards,
>> kTodos
>

Datetime conversion under diferent versions of SQL

Hello!
I'm using the same script to insert/update records on diferent versions of
SQL but i'm getting this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]A converso de um
tipo de
dados char em um tipo de dados datetime resultou em um valor datetime fora
do intervalo.
(translation: error converting one string into datetime value out of range)
The SQL versions that I am probing is 8.00.194 (RTM) that is installed with
Microsoft SQL Personal Engine CD and ther other version is 8.00.2039 (SP4)
that i've downloaded and installed.
Can anywone help me?
Regards,
kTodosYou are probably passing dates in some regional format (e.g. dd/mm/yyyy) and
this is okay on one server (which may have British language settings) but
not on another (which may have US English language, or mdy dateformat). To
avoid these problems, always pass dates as 'YYYYMMDD'...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"kTodos" <kanduru.x@.iol.pt> wrote in message
news:%23YZA%23Q6rHHA.2240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I'm using the same script to insert/update records on diferent versions of
> SQL but i'm getting this error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]A converso de
um tipo de
> dados char em um tipo de dados datetime resultou em um valor datetime fora
> do intervalo.
> (translation: error converting one string into datetime value out of
> range)
> The SQL versions that I am probing is 8.00.194 (RTM) that is installed
> with Microsoft SQL Personal Engine CD and ther other version is 8.00.2039
> (SP4) that i've downloaded and installed.
> Can anywone help me?
> Regards,
> kTodos
>|||... and for some extra reading: http://www.karaszi.com/SQLServer/in...ime.as
p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OtJcSa8rHHA.1200@.TK2MSFTNGP04.phx.gbl...
> You are probably passing dates in some regional format (e.g. dd/mm/yyyy) a
nd this is okay on one
> server (which may have British language settings) but not on another (whic
h may have US English
> language, or mdy dateformat). To avoid these problems, always pass dates
as 'YYYYMMDD'...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "kTodos" <kanduru.x@.iol.pt> wrote in message news:%23YZA%23Q6rHHA.2240@.TK2
MSFTNGP03.phx.gbl...
>

Datetime comparison problem

Hi,
I am having trouble finding any records with a datetime of 2006-06-16
09:04:39:347. This time clearly falls between the beginning and end times.
I'm even converting to a char to see if that works. It seems to work if I
use .346 but sometimes it requires a difference of .003. Can anyone see what
I might be doing wrong? Thanks Ellie
declare @.dPrevDate datetime
declare @.dNextDate datetime
select @.dPrevDate = '2006-06-16 09:04:39.346'
select @.dNextDate = '2006-06-16 09:05:56.110'
Select *
where (convert(char, tblChanges.dateaction, 21) > convert(char,
@.dPrevDate, 21) and
convert(char, tblChanges.dateaction, 21) < convert(char, @.dNextDate,
21))Sorry I made typos, I should have said that it DIDN'T work for 346 but did
work for 345 (when 346 is clearly less than 347) and the code should be:
>
>
"Ellie" <nospam@.nospam.net> wrote in message
news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what I might be doing wrong? Thanks Ellie
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @.dNextDate, 21))
>
>|||Due to a design flaw with Intel based chips, compute clock time is accurate
to the nearest three thousandth of a second.
This makes it difficult to coordinate precise times from a different time so
urce to the times created by a Intel based computer. For example, an automat
ed process control system have be recording times that are .003 seconds 'of
f' what the computer thinks.
You have to build in the .003 'fudge' factor.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ellie" <nospam@.nospam.net> wrote in message news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...[
color=darkred]
> Hi,
>
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see wh
at
> I might be doing wrong? Thanks Ellie
>
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
>
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char, @.dNextDate
,
> 21))
>
>
>
>[/color]|||I was thinking that there must have been a problem there but why can't I
convert it to a string and have it more accurate? That isn't working either.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
Due to a design flaw with Intel based chips, compute clock time is accurate
to the nearest three thousandth of a second.
This makes it difficult to coordinate precise times from a different time
source to the times created by a Intel based computer. For example, an
automated process control system have be recording times that are .003
seconds 'off' what the computer thinks.
You have to build in the .003 'fudge' factor.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Ellie" <nospam@.nospam.net> wrote in message
news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am having trouble finding any records with a datetime of 2006-06-16
> 09:04:39:347. This time clearly falls between the beginning and end times.
> I'm even converting to a char to see if that works. It seems to work if I
> use .346 but sometimes it requires a difference of .003. Can anyone see
> what
> I might be doing wrong? Thanks Ellie
> declare @.dPrevDate datetime
> declare @.dNextDate datetime
> select @.dPrevDate = '2006-06-16 09:04:39.346'
> select @.dNextDate = '2006-06-16 09:05:56.110'
> Select *
> where (convert(char, tblChanges.dateaction, 21) > convert(char,
> @.dPrevDate, 21) and
> convert(char, tblChanges.dateaction, 21) < convert(char,
> @.dNextDate,
> 21))
>
>|||I don't have any clue what "isn't working" means. However, I do have a
suggestion for storing accuracies under 3 ms.
When you enter the data to SQL Server, you can store the date +
minutes/seconds in datetime, then store *your* milliseconds in a separate
INT column. Or to save space you could use smalldatetime for date + minutes
and store seconds*1000+milliseconds in a separate INT column.
Now, when you query, you'll have something more complex to work with, and
that will be the harder part, but your data will be there. You can just
query for the times within your minute, and then where the milliseconds are
in the range you're after.
"Ellie" <nospam@.nospam.net> wrote in message
news:uTH03CvlGHA.884@.TK2MSFTNGP05.phx.gbl...
>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working
>either.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
> Due to a design flaw with Intel based chips, compute clock time is
> accurate to the nearest three thousandth of a second.
> This makes it difficult to coordinate precise times from a different time
> source to the times created by a Intel based computer. For example, an
> automated process control system have be recording times that are .003
> seconds 'off' what the computer thinks.
> You have to build in the .003 'fudge' factor.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Ellie" <nospam@.nospam.net> wrote in message
> news:O5OF6rulGHA.3816@.TK2MSFTNGP02.phx.gbl...
>|||Arnie Rowland (arnie@.1568.com) writes:
> Due to a design flaw with Intel based chips, compute clock time is > accurate to t
he nearest three thousandth of a second.

> This makes it difficult to coordinate precise times from a different
> time source to the times created by a Intel based computer. For example,
> an automated process control system have be recording times that are
> .003 seconds 'off' what the computer thinks.
Eh? It's perfectly possible in Windows to handle time down to
dissolution of 100 ns, I believe. At least, you can get far below
3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
durations in s.
In fact, if you look at
http://manuals.sybase.com:80/online...=2862;lang=sv#X
you can see that Sybase has the same accuracy. And, as may know
Microsoft SQL Server was originally derived from Sybase. And Sybase
has its origin on Unix. So I doubt that whereever the design flaw
was, that it was with Intel chips.
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|||You are seeing a limit of the DATETIME datatype in SQL Server. From
the Books on Line: "...to an accuracy of one three-hundredth of a
second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
are rounded to increments of .000, .003, or .007 seconds..."
So, to store the time more accurately you must, as Aaron explained,
have to "roll your own".
Roy Harvey
Beacon Falls, CT
On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@.nospam.net> wrote:

>I was thinking that there must have been a problem there but why can't I
>convert it to a string and have it more accurate? That isn't working either
.
>
>"Arnie Rowland" <arnie@.1568.com> wrote in message
>news:umCpU6ulGHA.4144@.TK2MSFTNGP05.phx.gbl...
>Due to a design flaw with Intel based chips, compute clock time is accurate
>to the nearest three thousandth of a second.
>This makes it difficult to coordinate precise times from a different time
>source to the times created by a Intel based computer. For example, an
>automated process control system have be recording times that are .003
>seconds 'off' what the computer thinks.
>You have to build in the .003 'fudge' factor.|||My brain checked out -it is a code artifact that predates Intel chip
design -is more in keeping of what I wanted to say. The latest IntelAMD
chips are capable of almost s precision.
Erlund, Thanks for catching this.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97EC62C58ABBYazorman@.127.0.0.1...
> Arnie Rowland (arnie@.1568.com) writes:
>
> Eh? It's perfectly possible in Windows to handle time down to
> dissolution of 100 ns, I believe. At least, you can get far below
> 3.33 ms. For instance, in the SQL 2005 Profiler, you can opt to get
> durations in s.
> In fact, if you look at
> http://manuals.sybase.com:80/online...=2862;lang=sv#X
> you can see that Sybase has the same accuracy. And, as may know
> Microsoft SQL Server was originally derived from Sybase. And Sybase
> has its origin on Unix. So I doubt that whereever the design flaw
> was, that it was with Intel chips.
>
> --
> 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|||So does this mean that internally they are not rounded (0.00333) but what
I'm seeing in query analyzer when I try to query it, is the rounded .000,
.003 or .007, etc.? I'm not concerned about the accurate storing of the tim
e
but the query afterwards. If they are stored inaccurately, I should still be
able to use that query, unless it is stored internally pre-rounding. I still
don't get why I can't convert it to a string and do a string comparison
though. Hope I am making sense.
Thanks for all of your help. I'll read some of the links and work around
this.
Ellie
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:u21p9299dg97vdtin85imfs525fkefinc7@.
4ax.com...
> You are seeing a limit of the DATETIME datatype in SQL Server. From
> the Books on Line: "...to an accuracy of one three-hundredth of a
> second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values
> are rounded to increments of .000, .003, or .007 seconds..."
> So, to store the time more accurately you must, as Aaron explained,
> have to "roll your own".
> Roy Harvey
> Beacon Falls, CT
> On Fri, 23 Jun 2006 14:13:22 -0400, "Ellie" <nospam@.nospam.net> wrote:
>|||Effectively they are rounded (as demonstrated below) and stored that
way. Comparison of a string against a datetime is performed by
comparing datetime types after converting the string. The string
conversion follows the rules demonstrated below.
Lets look at them as strings, and as strings converted to datetime:
select '2006-06-16 09:04:39.338',
convert(datetime,'2006-06-16 09:04:39.338') UNION
select '2006-06-16 09:04:39.339',
convert(datetime,'2006-06-16 09:04:39.339') UNION
select '2006-06-16 09:04:39.340',
convert(datetime,'2006-06-16 09:04:39.340') UNION
select '2006-06-16 09:04:39.341',
convert(datetime,'2006-06-16 09:04:39.341') UNION
select '2006-06-16 09:04:39.342',
convert(datetime,'2006-06-16 09:04:39.342') UNION
select '2006-06-16 09:04:39.343',
convert(datetime,'2006-06-16 09:04:39.343') UNION
select '2006-06-16 09:04:39.344',
convert(datetime,'2006-06-16 09:04:39.344') UNION
select '2006-06-16 09:04:39.345',
convert(datetime,'2006-06-16 09:04:39.345') UNION
select '2006-06-16 09:04:39.346',
convert(datetime,'2006-06-16 09:04:39.346') UNION
select '2006-06-16 09:04:39.347',
convert(datetime,'2006-06-16 09:04:39.347') UNION
select '2006-06-16 09:04:39.348',
convert(datetime,'2006-06-16 09:04:39.348') UNION
select '2006-06-16 09:04:39.349',
convert(datetime,'2006-06-16 09:04:39.349') UNION
select '2006-06-16 09:04:39.350',
convert(datetime,'2006-06-16 09:04:39.350') UNION
select '2006-06-16 09:04:39.351',
convert(datetime,'2006-06-16 09:04:39.351') UNION
select '2006-06-16 09:04:39.352',
convert(datetime,'2006-06-16 09:04:39.352')
order by 1
2006-06-16 09:04:39.338 2006-06-16 09:04:39.337
2006-06-16 09:04:39.339 2006-06-16 09:04:39.340
2006-06-16 09:04:39.340 2006-06-16 09:04:39.340
2006-06-16 09:04:39.341 2006-06-16 09:04:39.340
2006-06-16 09:04:39.342 2006-06-16 09:04:39.343
2006-06-16 09:04:39.343 2006-06-16 09:04:39.343
2006-06-16 09:04:39.344 2006-06-16 09:04:39.343
2006-06-16 09:04:39.345 2006-06-16 09:04:39.347
2006-06-16 09:04:39.346 2006-06-16 09:04:39.347
2006-06-16 09:04:39.347 2006-06-16 09:04:39.347
2006-06-16 09:04:39.348 2006-06-16 09:04:39.347
2006-06-16 09:04:39.349 2006-06-16 09:04:39.350
2006-06-16 09:04:39.350 2006-06-16 09:04:39.350
2006-06-16 09:04:39.351 2006-06-16 09:04:39.350
2006-06-16 09:04:39.352 2006-06-16 09:04:39.353
I hope that helps.
Roy Harvey
Beacon Falls, CT
On Sat, 24 Jun 2006 07:48:43 -0400, "Ellie" <nospam@.nospam.net> wrote:

>So does this mean that internally they are not rounded (0.00333) but what
>I'm seeing in query analyzer when I try to query it, is the rounded .000,
>.003 or .007, etc.? I'm not concerned about the accurate storing of the tim
e
>but the query afterwards. If they are stored inaccurately, I should still b
e
>able to use that query, unless it is stored internally pre-rounding. I stil
l
>don't get why I can't convert it to a string and do a string comparison
>though. Hope I am making sense.
>Thanks for all of your help. I'll read some of the links and work around
>this.
>Ellie
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:u21p9299dg97vdtin85imfs525fkefinc7@.
4ax.com...
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
JamesTry:
invoiceddate >= DATEADD(D,- 30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112
))
David Portas
SQL Server MVP
--|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
Message posted via http://www.droptable.com|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it c
ame back in .0000 seconds with the results for top 100.
Jon
Message posted via http://www.droptable.com|||Thank you both.|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
whereas[vbcol=seagreen]
of[vbcol=seagreen]
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
James
Try:
invoiceddate >= DATEADD(D,-30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
David Portas
SQL Server MVP
|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
Message posted via http://www.sqlmonster.com
|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it came back in .0000 seconds with the results for top 100.
Jon
Message posted via http://www.sqlmonster.com
|||Thank you both.
|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>
|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>
|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...[vbcol=seagreen]
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
whereas[vbcol=seagreen]
of
>

Dates without Times

I have a view that I'm building in which I need to get all records that have
an InvoicedDate in in the past 30 days. So basically I set up part of the
where clause as:
(InvoicedDate >= DATEADD(d, - 30, GETDATE()))
Which doesn't work because the function returns a date and a time, whereas
I'm only storing dates in the InvoicedDate field. So, long story short, it
doesn't get the dates from exactly 30 days ago. I could roll some kind of
hack where I add one I guess, but that seems really hokey. What's a better
way to accomplish this?
Thanks!
JamesTry:
invoiceddate >= DATEADD(D,-30,CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))
--
David Portas
SQL Server MVP
--|||Try,
datepart(mm,createdate) >= DATEADD(d, - 30, datepart(mm,GETDATE()))
--
Message posted via http://www.sqlmonster.com|||I meant:
where datepart(mm,InvoiceDate) >=datepart(mm,GETDATE() - 30)
Might work better. I just tried it on our call records table (30m) and it came back in .0000 seconds with the results for top 100.
Jon
--
Message posted via http://www.sqlmonster.com|||Thank you both.|||DECLARE @.threshold SMALLDATETIME
SET @.threshold = CONVERT(CHAR(8), DATEADD(DAY, -30, GETDATE()), 112)
SELECT ... WHERE InvoicedDate >= @.threshold
--
http://www.aspfaq.com/
(Reverse address to reply.)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I have a view that I'm building in which I need to get all records that
have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
better
> way to accomplish this?
> Thanks!
> James
>|||Someone passed this along to me when i was having problems with dates and
times. Pretty useful script to keep around.
nivek
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
"James" <cppjames@.aol.com> wrote in message
news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
>I have a view that I'm building in which I need to get all records that
>have
> an InvoicedDate in in the past 30 days. So basically I set up part of the
> where clause as:
> (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> Which doesn't work because the function returns a date and a time, whereas
> I'm only storing dates in the InvoicedDate field. So, long story short,
> it
> doesn't get the dates from exactly 30 days ago. I could roll some kind of
> hack where I add one I guess, but that seems really hokey. What's a
> better
> way to accomplish this?
> Thanks!
> James
>|||A little exhaustive, but also useful:
http://www.aspfaq.com/2464
--
http://www.aspfaq.com/
(Reverse address to reply.)
"nivek" <eckart_612@.hotmail.com> wrote in message
news:MKidnVX_g-TwhU7cRVn-qA@.centurytel.net...
> Someone passed this along to me when i was having problems with dates and
> times. Pretty useful script to keep around.
> nivek
>
> Select
> Convert(Varchar(12),GetDate(),101),
> Convert(VarChar(12),GetDate(),102),
> Convert(VarChar(12),GetDate(),103),
> Convert(VarChar(12),GetDate(),104),
> Convert(VarChar(12),GetDate(),105),
> Convert(VarChar(12),GetDate(),106),
> Convert(VarChar(12),GetDate(),107),
> Convert(Varchar(11),GetDate(),108),
> Convert(VarChar(12),GetDate(),109),
> Convert(VarChar(12),GetDate(),110),
> Convert(VarChar(12),GetDate(),111),
> Convert(VarChar(12),GetDate(),112),
> Convert(VarChar(12),GetDate(),113),
> Convert(VarChar(12),GetDate(),114)
>
> "James" <cppjames@.aol.com> wrote in message
> news:OMajU3c7EHA.4072@.TK2MSFTNGP10.phx.gbl...
> >I have a view that I'm building in which I need to get all records that
> >have
> > an InvoicedDate in in the past 30 days. So basically I set up part of
the
> > where clause as:
> >
> > (InvoicedDate >= DATEADD(d, - 30, GETDATE()))
> >
> > Which doesn't work because the function returns a date and a time,
whereas
> > I'm only storing dates in the InvoicedDate field. So, long story short,
> > it
> > doesn't get the dates from exactly 30 days ago. I could roll some kind
of
> > hack where I add one I guess, but that seems really hokey. What's a
> > better
> > way to accomplish this?
> >
> > Thanks!
> > James
> >
> >
>

Dates

I want to make a simple question.
I want to query the count of the records at a table for a month (mm/yyyy), how i have to "format" the date for this in access?
Thanks
dimisI had this answer from pootle flump in forum of access

Hi

You can either use a range in your where clause (WHERE MyDate BETWEEN 1st day of month and Last day of month) or you can use some functions (probably a little slower - WHERE Year(MyDate) = TheYearIWantToCheck AND Month(MyDate) = TheMonthIWantToCheck)

HTH
__________________
Environment: A02\03 SQL Server 2K DAO 3.6 ADO 2.7 VB6 SP5
Reply With Quote|||Note that the range solution (WHERE MyDate BETWEEN '2005-12-01' AND '2005-12-31') is typically much more performant (and more portable too ;)) than the use of scalar functions (WHERE year(MyDate)=2005 AND month(MyDate)=12)|||dont use short date format for date comparison, coz every single second affects the result. '2005-01-01' means '2005-01-01 00:00:00.000', no more no less, even just 1 second. so i used to use function like year, month, day, datediff.|||My problem was that I had to find the records from a table for the current month (in Ms access with asp) so I had to find dynamicaly the current month and then find the correct sql statement.
dimis|||to get current date and time use now(), month(now()) and year(now()) for current month and year

Dates

Hi,

I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ReservationsConnectionString %>"

SelectCommand="SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = @.TIM_Time)">

<SelectParameters>

<asp:ControlParameter ControlID="Calendar1" Name="TIM_Time" PropertyName="SelectedDate"

Type="DateTime" />

</SelectParameters>

I get no records when I know they are there - do I have to put in additional checks to cater for the time component of the column?

Thanks in advance.

Assuming you can guarantee no time value on the @.TIM_Time parameter

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From]
FROM [Times]
WHERE ([TIM_Time] >= @.TIM_Time and [TIM_Time] < dateadd(day,1,@.TIM_Time))">

This gets all times from midnight on the day, to anything before midnight on the next day.

<light advice>I would seriously reconsider your naming convention of including a table prefix for every column, especially one that is an abbreviation. That will get seriously old over time having to type SOMETHING_ in front of every column, and then having to remove it with an alias everytime you want to display it to a user. </light advice>

|||

If TIM_Time contain only date (without time) then you can use this code

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = convert(varchar, @.TIM_Time, 112)

|||

Thaks for this but how does one hold only a date?

Is there a simple way of selecting the date part and the time part?

|||

you also can use Functions for this. You can do it in T-SQL (see following code), or write an equivalent with code behind.

declare @.dt as datetime, @.tm as datetime

select @.dt = getdate(), @.tm = getdate()

select @.tm, @.dt

select @.dt = dbo.FN_DATETIME_AS_HMS(@.dt)
select @.tm = dbo.FN_DATETIME_AS_DATE(@.tm)

select @.dt, @.tm

Here the functions code :

CREATE FUNCTION FN_DATETIME_AS_HMS (@.DT DATETIME)
RETURNS CHAR(8) AS
BEGIN
IF @.DT IS NULL RETURN NULL
DECLARE @.H INT
DECLARE @.M INT
DECLARE @.S INT
SET @.H = DATEPART(HOUR, @.DT)
SET @.M = DATEPART(MINUTE, @.DT)
SET @.S = DATEPART(SECOND, @.DT)
DECLARE @.RETVAL VARCHAR(8)
IF @.H < 10
SET @.RETVAL = '0' + CAST(@.H AS CHAR(1))+':'
ELSE
SET @.RETVAL = CAST(@.H AS CHAR(2))+':'
IF @.M < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.M AS CHAR(1))+':'
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.M AS CHAR(2))+':'
IF @.S < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.S AS CHAR(1))
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.S AS CHAR(2))
RETURN CAST(@.RETVAL AS CHAR(8))
END

--

CREATE FUNCTION FN_DATETIME_AS_DATE (@.DT DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CAST(FLOOR(CAST(@.DT AS FLOAT)) AS DATETIME)
END

ps : sources http://sqlpro.developpez.com/cours/sqlserver/udf

|||

Stephane,

Many thanks for this and sorry to be so ignorant but where exactly do I place the first part of your code? I created the 2 functions but I tried to place the first part it in a method but received an error message saying that the declare statement is not valid in a method.

|||

this is T-SQL only. it was just a sample to hava a look at the result. to run this code, execute it in Query Analyzer.

the important part is :

select dbo.FN_DATETIME_AS_HMS([Put a dateTime here])

and you'll have you time, with a 'null' date, fixed to 01/01/1900 and a valid hour. You also could choos another default value for null date. Some use 01/01/1753 with SQL Server.

select dbo.FN_DATETIME_AS_DATE([Put a DateTime here])

here, you'll have a date time with your correct date, and time set to 00:00:00.

|||

Stephane,

Thanks a lot, that was very helpful - I'm getting there - slowly!

Dates

Hi,

I have date columns in my tables defined as smalldatetime. How do I perform a Select that will retrieve records with dates equal to the date selected in a calendar control as follows:

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ReservationsConnectionString %>"

SelectCommand="SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = @.TIM_Time)">

<SelectParameters>

<asp:ControlParameter ControlID="Calendar1" Name="TIM_Time" PropertyName="SelectedDate"

Type="DateTime" />

</SelectParameters>

I get no records when I know they are there - do I have to put in additional checks to cater for the time component of the column?

Thanks in advance.

Assuming you can guarantee no time value on the @.TIM_Time parameter

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From]
FROM [Times]
WHERE ([TIM_Time] >= @.TIM_Time and [TIM_Time] < dateadd(day,1,@.TIM_Time))">

This gets all times from midnight on the day, to anything before midnight on the next day.

<light advice>I would seriously reconsider your naming convention of including a table prefix for every column, especially one that is an abbreviation. That will get seriously old over time having to type SOMETHING_ in front of every column, and then having to remove it with an alias everytime you want to display it to a user. </light advice>

|||

If TIM_Time contain only date (without time) then you can use this code

SELECT [TIM_Time], [TIM_ID], [TIM_Valid_From] FROM [Times] WHERE ([TIM_Time] = convert(varchar, @.TIM_Time, 112)

|||

Thaks for this but how does one hold only a date?

Is there a simple way of selecting the date part and the time part?

|||

you also can use Functions for this. You can do it in T-SQL (see following code), or write an equivalent with code behind.

declare @.dt as datetime, @.tm as datetime

select @.dt = getdate(), @.tm = getdate()

select @.tm, @.dt

select @.dt = dbo.FN_DATETIME_AS_HMS(@.dt)
select @.tm = dbo.FN_DATETIME_AS_DATE(@.tm)

select @.dt, @.tm

Here the functions code :

CREATE FUNCTION FN_DATETIME_AS_HMS (@.DT DATETIME)
RETURNS CHAR(8) AS
BEGIN
IF @.DT IS NULL RETURN NULL
DECLARE @.H INT
DECLARE @.M INT
DECLARE @.S INT
SET @.H = DATEPART(HOUR, @.DT)
SET @.M = DATEPART(MINUTE, @.DT)
SET @.S = DATEPART(SECOND, @.DT)
DECLARE @.RETVAL VARCHAR(8)
IF @.H < 10
SET @.RETVAL = '0' + CAST(@.H AS CHAR(1))+':'
ELSE
SET @.RETVAL = CAST(@.H AS CHAR(2))+':'
IF @.M < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.M AS CHAR(1))+':'
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.M AS CHAR(2))+':'
IF @.S < 10
SET @.RETVAL = @.RETVAL + '0' + CAST(@.S AS CHAR(1))
ELSE
SET @.RETVAL = @.RETVAL + CAST(@.S AS CHAR(2))
RETURN CAST(@.RETVAL AS CHAR(8))
END

--

CREATE FUNCTION FN_DATETIME_AS_DATE (@.DT DATETIME)
RETURNS DATETIME AS
BEGIN
RETURN CAST(FLOOR(CAST(@.DT AS FLOAT)) AS DATETIME)
END

ps : sources http://sqlpro.developpez.com/cours/sqlserver/udf

|||

Stephane,

Many thanks for this and sorry to be so ignorant but where exactly do I place the first part of your code? I created the 2 functions but I tried to place the first part it in a method but received an error message saying that the declare statement is not valid in a method.

|||

this is T-SQL only. it was just a sample to hava a look at the result. to run this code, execute it in Query Analyzer.

the important part is :

select dbo.FN_DATETIME_AS_HMS([Put a dateTime here])

and you'll have you time, with a 'null' date, fixed to 01/01/1900 and a valid hour. You also could choos another default value for null date. Some use 01/01/1753 with SQL Server.

select dbo.FN_DATETIME_AS_DATE([Put a DateTime here])

here, you'll have a date time with your correct date, and time set to 00:00:00.

|||

Stephane,

Thanks a lot, that was very helpful - I'm getting there - slowly!

datediff question

Hello All!

I want to select records that are 24 hours old or older.

Here is my code

SELECT *, DATEDIFF(Hour, Service_Date_Time, getdate())AS Hours

FROM Active_Orders

WHERE (Status_ID = 4)

Not sure how I write the 24 hours part?

Any thoughtS

Thanks!

Rudy

Use a filter expression in the "where" clause.

SELECT *, DATEDIFF(Hour, Service_Date_Time, getdate()) AS Hours

FROM Active_Orders

WHERE (Status_ID = 4) and Service_Date_Time <= DATEADD(Hour, 24, getdate())

AMB

|||

This may work for you:

Code Snippet

SELECT

{ColumnList},

Service_Date_Time

FROM Active_Orders

WHERE ( Status_ID = 4

AND Service_Date_Time >= dateadd( day, -1, getdate() )

)

You want to avoid using a function on the Service_Date_Time field since doing so would eliminate any chance of using indexing on the column.

*NOTE: (Using [ SELECT * ] is not a good idea, and is not in the accepted 'best practices'.)

|||

Hi Arnie,

Good point about not manipulating the column in the filter expression (Mea culpa - I changed my post after reading yours). The OP wants 24 hours old or older.

...

WHERE ( Status_ID = 4

AND Service_Date_Time <= dateadd( day, -1, getdate() )

)

go

AMB

|||

Thanks Alejandro,

I'm glad you caught the mistake -I was thinking 'FOR' the last 24 hours. Your change [ <= ] will catch those MORE than 24 hours.

|||

WOW!

Thanks guys! Great suggestions!

Rudy

datediff question

Hello All!

I want to select records that are 24 hours old or older.

Here is my code

SELECT *, DATEDIFF(Hour, Service_Date_Time, getdate())AS Hours

FROM Active_Orders

WHERE (Status_ID = 4)

Not sure how I write the 24 hours part?

Any thoughtS

Thanks!

Rudy

Use a filter expression in the "where" clause.

SELECT *, DATEDIFF(Hour, Service_Date_Time, getdate()) AS Hours

FROM Active_Orders

WHERE (Status_ID = 4) and Service_Date_Time <= DATEADD(Hour, 24, getdate())

AMB

|||

This may work for you:

Code Snippet

SELECT

{ColumnList},

Service_Date_Time

FROM Active_Orders

WHERE ( Status_ID = 4

AND Service_Date_Time >= dateadd( day, -1, getdate() )

)

You want to avoid using a function on the Service_Date_Time field since doing so would eliminate any chance of using indexing on the column.

*NOTE: (Using [ SELECT * ] is not a good idea, and is not in the accepted 'best practices'.)

|||

Hi Arnie,

Good point about not manipulating the column in the filter expression (Mea culpa - I changed my post after reading yours). The OP wants 24 hours old or older.

...

WHERE ( Status_ID = 4

AND Service_Date_Time <= dateadd( day, -1, getdate() )

)

go

AMB

|||

Thanks Alejandro,

I'm glad you caught the mistake -I was thinking 'FOR' the last 24 hours. Your change [ <= ] will catch those MORE than 24 hours.

|||

WOW!

Thanks guys! Great suggestions!

Rudy

DateDiff problem

I have a db with a table that records details about order transactions. The
main columns I am concerned about are the "Transacton_Type" column and the
"Date_Time" column. Each order has more than one transaction/rows in the
table (ie. Pending, Shipped, etc...), with each transaction having its' own
time stamp (as opposed to a table with a Pending column and a Shipped
column). How can I use the DateDiff function to give me the difference
between the Pending date and Shipped date for each Order (uniquely referenced
in a "P.O." column) if the timestamp for each transaction exists in the same
column. Is this even possible?
There are a couple of ways to do this, and they should be efficient if
there are supporting indexes:
select
T1.[P.O.],
T1.Date_Time as PendingDate,
T2.Date_Time as ShippedDate,
datediff(day, T1.Date_Time, T2.Date_Time) as LagTime
from yourTable T1
join yourTable T2
on T1.[P.O.] = T2.[P.O.]
where T1.Transaction_Type = ''Pending'
and T2.Transaction_Type = 'Shipped'
-- add where clause to restrict to a certain customer, for example.
The second way shows the pending, shipped, etc., dates as separate
columns. You can create the inner query here as a view, or adapt this
in other ways for individual queries.
select [P.O.], datediff(day,PendingDate, ShippedDate) as LagTime
from (
select
[P.O.],
max(case when Transaction_Type = 'Pending' then Date_Time end) as
PendingDate,
max(case when Transaction_Type = 'Shipped' then Date_Time end) as
ShippedDate,
.. other dates you need
from yourTable
-- restrict to a P.O. either here or ..
group by [P.O.]
) T
-- .. here (other place to restrict for limited results)
Steve Kass
Drew University
Strugglin' wrote:

>I have a db with a table that records details about order transactions. The
>main columns I am concerned about are the "Transacton_Type" column and the
>"Date_Time" column. Each order has more than one transaction/rows in the
>table (ie. Pending, Shipped, etc...), with each transaction having its' own
>time stamp (as opposed to a table with a Pending column and a Shipped
>column). How can I use the DateDiff function to give me the difference
>between the Pending date and Shipped date for each Order (uniquely referenced
>in a "P.O." column) if the timestamp for each transaction exists in the same
>column. Is this even possible?
>

DateDiff

I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.

I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.The concept of what a month is, is funny...you'd be better of if you could pick a fixed number of days

It's like month is nondetermenistic, where days are determenistic..

Never read that anywhere I don't think (damn I hate when that happens)

USE Northwind
GO

CREATE TABLE myTable99(myMonth99 int, myDay99 int, myYear99 int)
GO

INSERT INTO myTable99(myMonth99, myDay99, myYear99)
SELECT 1,1,2003 UNION ALL
SELECT 2,1,2003 UNION ALL
SELECT 3,1,2003 UNION ALL
SELECT 4,1,2003 UNION ALL
SELECT 4,15,2003 UNION ALL
SELECT 4,30,2003 UNION ALL
SELECT 5,1,2003 UNION ALL
SELECT 6,1,2003 UNION ALL
SELECT 7,1,2003 UNION ALL
SELECT 8,1,2003 UNION ALL
SELECT 9,1,2003 UNION ALL
SELECT 10,1,2003 UNION ALL
SELECT 11,1,2003 UNION ALL
SELECT 12,1,2003
GO

SELECT * FROM myTable99
WHERE DATEDIFF(mm,
CONVERT(datetime,
CONVERT(varchar(4),myYear99)
+'/'+CONVERT(varchar(4),myMonth99)
+'/'+CONVERT(varchar(4),myDay99))
,GetDate()) >= 12
GO

DROP TABLE myTable99
GO|||Yeah, I see what you mean with the moth thing being funny. I figured out another way where I can do it. If I leave the date fixed where it displayes the year, month and day, I can accomplish what i have set out to do. This is what worked for me.

select * from podinrh
where date_rcvd between getdate() - 365 and getdate()
order by date_rcvd

I will also try out the script that you have provided me with Brett. Thanks for all the help you all provide.|||Yeah thats days...

Works much better

SELECT * FROM myTable99
WHERE DATEDIFF(dd,
CONVERT(datetime,
CONVERT(varchar(4),myYear99)
+'/'+CONVERT(varchar(4),myMonth99)
+'/'+CONVERT(varchar(4),myDay99))
,GetDate()) >= 365
GO|||Originally posted by estefex
I will also try out the script that you have provided me with Brett.

You know to just cut and paste it in to QA right?|||Brett, It works fine too. I will have to see what format they are going to want to stick to. I am thinking they are going to want it with the date broken bown into columns like you did. I appreciate you help.|||The Devil is in the details. To handle leap years, you might want to use this instead:

select * from podinrh
where date_rcvd between dateadd(year, -1, getdate()) and getdate()
order by date_rcvd

Also remember that the BETWEEN operator is inclusive. If your data is stored as whole dates (without the time of day), then you may end up excluding data from the first day or including an extra 24 hours at the end.

For date comparisons, I use this instead of BETWEEN:

select * from podinrh
where date_rcvd > dateadd(year, -1, getdate()) and date_rcvd <= getdate()
order by date_rcvd|||Thanks for the tipp because i was useing the time with the date as well. The number of records returned is a but greater than when I used the between statement.

DATEADD returning odd count of records

Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qhDATEADD(m, -18, getdate()) is giving you 26th Jan 2004. That means
everything older than or equal to 26th Jan 2004. But your second query is
looking for data between 1st and 31st of Jan, which obviously is going to
have 5 days worth of additional data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1122371409.746888.44830@.g49g2000cwa.googlegroups.com...
Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qh|||Hello, Scott
You should notice the fact that "DATEADD(m, -18, getdate())"
returns January 26, 2004 and in your second query you are
comparing with January 31, 2004.
If you want to obtain the last day of the current month,
you can use the following:
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
So your query might be:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
Razvan|||Hi Razvan,
Cheers for the reply, your solution sorted it. I didn't expect the
count to be over 200k for the days 26-31st Jan. I think it's myself
who doesn't know his own data!
;o)
Thanks
Scott
Razvan Socol wrote:
> Hello, Scott
> You should notice the fact that "DATEADD(m, -18, getdate())"
> returns January 26, 2004 and in your second query you are
> comparing with January 31, 2004.
> If you want to obtain the last day of the current month,
> you can use the following:
> SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
> So your query might be:
> select count(*) from MyTable
> where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
> Razvan