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)

DateTimestamp

Hi,
I currently have a column in a table that is a default of getdate()). My
question : Is there a way to capture just the date without the time appended
?
For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexes
on this datetimestamp field? Would a clustered index on this field help?
Almost all searching is done by the date field, whether it is searched by da
y
or a date range...Any better way than what is currently being done to help
with speed ? Currently we have a clustered index on this column. The column
currently contains approximately 12 million rows...
Thanks for your input,
WarrenChange the defualt to Cast (getdate() as Integer)
-- Datetimes as stored internally as a decimal value 4 bytes for the
integer portion, and another 4 bytes for the fractional portion. The 4 byte
s
for integer portion are the date, and the other 4 bytes are the time... So i
f
you cast the value to an integer, you truncate the time...
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||Store same time for all rows.
Example:
use tempdb
go
create table dbo.t (
colA int not null identity unique,
colB datetime default (convert(char(8), getdate(), 112))
)
insert into t default values
WAITFOR DELAY '00:00:00.999'
insert into t default values
WAITFOR DELAY '00:00:00.999'
insert into t default values
select * from t
drop table t
go
If you are planning to do a lot of selects filtering this column based on a
range of dates, then having a clustered index by this column will be helpful
.
AMB
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||There is no way to capture a date without the time portion; best thing to do
is probably write a function that sets each portion of the time to zero - us
e
DATEDIFF and DATEADD functions to subtract hours, minutes, seconds, and
milliseconds from the date.
With SQL 2000 32-bit datetimes are stored as 2 4-byte integers, so you can
convert the datetime to a binary(8), take the first 4 bytes and concat
0x00000000 to that and convert back to datetime to get zero hours, mins,
secs, & ms; but this method would not necessarily work on anything but SQL 2
k
32-bit.
As for a clustered index, only testing will tell...
KH
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||If you're really interested in speed, don;t use DateTime column use an
integer or smallint.. and store the integer you get from Cast (getDate()
as Integer) directly. This reduces the size of the data in the column frm
8bytes to 4 bytes (oor 2 bytes if you use smallint) By reducing the width o
f
the index, you will dramatically increase the number of entries you will be
able to store on each IO Page of the index, and increase the performance
using the index.
NOTE: If you use smallints, then you need to subtract 32768 from the valu
you get from Cast(getdate() as Integer) before stuffing it into the smallint
field, because smallint is SIGNED 2-byte integer, but smalldatetime uses
UNSIGNED 2-byte integer..
SmallDatetime goes from Integer value 0 represesnting 1 jan 1900, to 65536
representing 6 June 2079... whereas smallint goes from -32768 to +32767...
Using Regular datetime is no problem, because it uses SIGNED 4-byte Integer,
with values from
January 1, 1753 through December 31, (this way value 0 still represents 1
Jan 1900... )
If your queries against this table often use date as a range of dates...
then a clustered index on this column will increase performance
draamatically.
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||Thanks for all the replys...
I found out that the client application inserts the date stamp in the row of
the database. The colun is a dattime field, so it's not the getdate()) that
I thought it was..
Any more thoughts?
Thanks,
Warren
"CBretana" wrote:
> If you're really interested in speed, don;t use DateTime column use an
> integer or smallint.. and store the integer you get from Cast (getDate(
)
> as Integer) directly. This reduces the size of the data in the column fr
m
> 8bytes to 4 bytes (oor 2 bytes if you use smallint) By reducing the width
of
> the index, you will dramatically increase the number of entries you will b
e
> able to store on each IO Page of the index, and increase the performance
> using the index.
> NOTE: If you use smallints, then you need to subtract 32768 from the valu
> you get from Cast(getdate() as Integer) before stuffing it into the smalli
nt
> field, because smallint is SIGNED 2-byte integer, but smalldatetime uses
> UNSIGNED 2-byte integer..
> SmallDatetime goes from Integer value 0 represesnting 1 jan 1900, to 6553
6
> representing 6 June 2079... whereas smallint goes from -32768 to +32767...
> Using Regular datetime is no problem, because it uses SIGNED 4-byte Intege
r,
> with values from
> January 1, 1753 through December 31, (this way value 0 still represents 1
> Jan 1900... )
> If your queries against this table often use date as a range of dates...
> then a clustered index on this column will increase performance
> draamatically.
> "Warren" wrote:
>|||Then you need to
a) change the way the client write the data to strip off the time portion,
How is client writing to Database? using CLient constructed SQL,
Through a Stored Proc, etc.?
b) write an Insert/ update trigger on the database table that strips off the
time portion whenver client tries t owrite time into the table
In either case, To speed things up, same comments I made earlier about
datatypes still apply...
"Warren" wrote:
> Thanks for all the replys...
> I found out that the client application inserts the date stamp in the row
of
> the database. The colun is a dattime field, so it's not the getdate()) th
at
> I thought it was..
> Any more thoughts?
> Thanks,
> Warren
> "CBretana" wrote:
>sql

DateTime types and getdate() comparison

SQL 2000. Let's say column MyDate is a datetime type. Is this
comparison syntax OK as is?
... where MyDate <= getdate()
Or is some formatting of the column value and/or of the function's
return value required for the comparison to work?
Thanks
LiamComparison operators (<,>,=, <>, >=, <= ) are allowed between two values wit
h
a datatype of datetime. Your expression is fine.
However, if you want to do things like add or subtract datetime values, you
will need to use the date and time functions in SQL Server.
"Liam" wrote:

> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> .... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam
>|||depends on what you need
but don't convert the column - you'll lose any sargability if it's indexed.
i tend not to try to rely on date data having being inserted with a time
of midnight, so i convert the variable and perform range queries
if you need mydate <= just the date: then do
MyDate < tomorrow at midnight
e.g.
where MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need MyDate for just today
where MyDate >= dateadd(day, datediff(day, 0, getdate()), 0)
and MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need mydate <= current date and time, then simply using
getdate() is appropriate.
Liam wrote:
> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> ... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam

Datetime to time conversion with default date

Hi,

I am importing a csv file to SQL 2005 table. The source column is coming as datetime. The destination filed is a datetime type. I would like to update the destination with the time part from the source. I used the data conversion to convert it to time using "database time[DT_DBTIME]". For a source value "2/08/2007 21:51:07" this inserts a value "2007-08-03 21:51:07.000". I need the column to have a value as "1900-01-01 21:57:07.000".

Can someone please tell me how do I do this conversion?

Thanks,

Try this in a Derived Column transform (replace DateValue with the name of your column):

Code Snippet

(DT_DBTIMESTAMP)("1900-01-01 " + (DT_WSTR,10)(DT_DBTIME)DateValue)

|||

Thanks, jwelch.

DATETIME To Format mm/dd/yyyy hh:mm am/pm

I have a column in a database set as a DATETIME datatype, when I select it, I want to return it as:

mm/dd/yyyy hh:mm am or pm.

How in the world can I do this? I looked at the function CONVERT() and it doesnt seem to have this format as a valid type. This is causing me to lose my hair, in MySQL it is just so much easier. .

At any rate, currently when I select the value without any convert() it returns as:

June 1 2007 12:23AM

Which is close, but I want it as:

06/01/2007 12:23AM

Thanks!

Hi,

Normally you would be formatting your date on the UI or Report side, which means you must let your program display it correctly or let your reporting engine format your date as you want to.

But if you really want to change the format of your date, you can do this by changing the type to string by using the CONVERT function. The closest that I can come up with is this format:

mm/dd/yyyy

checki it here (code 101):

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

the syntax would be like this in SQL

SELECT CONVERT('your date', nvarchar(MAX), 101)

Maybe you can mix it up with code 108 so that you can concatenate the time with it.

cheers,

Paul June A. Domag

|||

The reason I am formatting it in SQL is this is inside a trigger written in pure SQL which emails from the DB.

Yeah CONVERT() using type 101 was the closest I could get as well, but it only displays date, no time. I need both date and time in the format I specified above.

This is absolutley stupid that they did it this way, they should take a lession from MySQL which allows you to format a DATETIME in any fashion via strings such as %m/%d/%Y etc, etc.

Anybody have other ideas?

|||

Hi,

Why not just combine the codes 101 and 108 and maybe manually parse it using substring?

You can create a scalar function to make it reusable.

Code Snippet

DECLARE @.dt VARCHAR(MAX)

SELECT @.dt = CONVERT(nvarchar(MAX), GETDATE(), 101) + ' ' + CONVERT(nvarchar(MAX), GETDATE(), 108)

-- After this just use Substring to satisfy your formatting

note: varchar(max) is only available in SQL2005. specify the lenght if your using SQL2000

cheers,

Paul June A. Domag

|||

As Paul indicated, formating is normally left to the client application. I suspect the developer you are working with either does not know how to properly format for display in his/her application, or is too lazy and is passing the responsibility off to the database.

This expression should provide the date in the form you desire. Replace the [ @.MyDate ] with your column or date value. You can easily create your own function that will do this for you so that you can re-use this expression.

DECLARE @.MyDate datetime

SET @.MyDate = '2007/07/21 11:35:45.255PM'

SELECT MyDate =
convert( varchar(10), @.MyDate, 101) +
stuff( right( convert( varchar(26), @.MyDate, 109 ), 15 ), 7, 7, ' ' )

MyDate
-
07/21/2007 11:35 PM

|||

Arnie Rowland ,

You are the man, that worked like a charm. I guess my problem with the convert function is that there is no predefined format of:

mm/dd/yyyy hh:mm am/pm

I would assume this is very very popular, so I am confused as to why it is not implemented. As far as this benig done on the front end, this has to be done at the DB level, since we send emails out via the database.

|||

I have to say that I wouldn't want to send an email from a trigger that required any kind of special formatting. Perhaps an alert to a sysadmin, but if I was going to send correspondence like that, I would put my information in a queue of some sort and have a tool to send the email.

the CONVERT thing is a mess because it doesn't give you enough formats, unlike a proper data presentation layer would. I have (in the past) used datePart to build up a date formatter of my own, or you could probably do it with the CLR quite nicely. But SQL Server should be used to manage and manipulate data, not format it (as a broad rule of course. We all do it from time to time to appease a user/manager/programmer etc, so don't think I am saying it is horrible, it just isn't as ideal as using a programming tool made to do such things.)

DateTime string insert into sql datetime column fails

Iam trying to write to a DateTime field in MSSQL from wonderware
intouch. The problem is that I keep getting the error that the string
I'm using is not a valid datetime string....has anybody experienced
this and what was the workaround?
Thanks
GaryThis should arm you with enough information to understand why the operation
fails:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<GaryCharlotte@.Charter.net> wrote in message
news:1145071019.588279.87720@.i39g2000cwa.googlegroups.com...
> Iam trying to write to a DateTime field in MSSQL from wonderware
> intouch. The problem is that I keep getting the error that the string
> I'm using is not a valid datetime string....has anybody experienced
> this and what was the workaround?
> Thanks
> Gary
>|||Thank you Tibor.
I have tried various combinations including the recommended on that
site ie '02/23/1998 14:23:05'
Still no joy. I wonder if this is a wonderware sqlinsert problem...|||> I have tried various combinations including the recommended on that
> site ie '02/23/1998 14:23:05'
That's not recommended, it will fail if, for example, your dateformat is
dmy.
What does "no joy" mean? Does it fail? With what error? Did you try a
safe standard format like
'19980223 14:23:05'
?|||OK I found out what the problem is. If you use a SQLInsertprepare and
SQLInsertexecute it fails no matter what format you use.
Used SQLConnect, SQLInsert and SQLDisconnect and it works great!
Thanks for the help Tiborsql

Datetime query issue with C# stored procedure

Ok, i am using the convert function to get the date format from my datetime column. My problem is when C# try's to pull the column name the column name is not sent in the query. When I run the query in the query analyzer the column name is blank in the result window. How can I get the date from the record with out losing the name of my column in the query. My data binding needs the name of the column to bind the data to the drop down list control. Here is the statement:

SQL statement
SELECT DISTINCT convert(datetime, eventDT, 110) FROM tblRecognition

C# code
ddlDateTo.DataSource = _uiCode.Fill.Date();
ddlDateTo.DataTextField = "eventDT";
ddlDateTo.DataBind();

Thank you,assign a column alias in your SELECT

convert(datetime, eventDT, 110) as displaydate

ddlDateTo.DataTextField = "displaydate";|||well that was simple lol. thank you, i don't know why that didn't accur to me.

Datetime Query

i am trying to query a datetime column in a db.

e.g. 3/7/2005 4:24:01 AM
My query is below :-
--
select a.date, b.useruri as 'FROM', c.useruri as 'TO',
a.body as 'MESSAGE' from messages as a
inner join
users as b
on a.fromid = b.userid
inner join
users as c
on a.toid = c.userid
where a.date like '%2005-03-01%'
order by a.dateSpecify the times using BETWEEN. Otherwise, you won't use any indexes and this will be extremely slow.|||Do i specify the date as a i wrote in the query. since the datetime is like
3/7/2005 4:24:01 AM ?

or do i have to declare the datime if it was today and use the variable in the query.|||Well, it depends what you're trying to achieve. :) If the data was stored like that, then just go from 00:00:00 to 23:59:59. If it was stored with more accuracy, it can get a little tricky. Note the following code results followed by an excert from Books Online:

CODE:

DECLARE @.dates TABLE(date1 DATETIME)

INSERT @.dates(date1)
SELECT '01/01/05 13:58:01.000' UNION ALL
SELECT '01/02/05 00:00:00.000' UNION ALL
SELECT '01/02/05 00:00:00.001' UNION ALL
SELECT '01/02/05 23:59:59.999' UNION ALL
SELECT '01/03/05 00:00:00.000' UNION ALL
SELECT '01/03/05 00:00:00.001' UNION ALL
SELECT '01/04/05 10:00:00.001'

SELECT date1 FROM @.dates

SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.999'

SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.997'

BOL Quote:

Date and time data types for representing date and time of day.

datetime

Date and time data from January 1, 1753 through December 31, 9999, 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, as shown in the table.

Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995,
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998 1998-01-01 23:59:59.997
01/01/98 23:59:59.992,
01/01/98 23:59:59.993,
01/01/98 23:59:59.994 1998-01-01 23:59:59.993
01/01/98 23:59:59.990 or
01/01/98 23:59:59.991 1998-01-01 23:59:59.990

Microsoft SQL Server rejects all values it cannot recognize as dates between 1753 and 9999.sql

DATETIME Issue

Hi all

I am having a problem with how the DATE field of a column is being displayed. What I am trying to do is run a DTS that takes the info from a table and places it into a flat file. Of course the format of the flat file does not need the trailing 23:59:59.993. When running a view it gives us the proper format of mm/dd/yyyy but the rest is not being shown in the view. A Cast function in the view into a VARCHAR changes it to something like Oct, 10, 2006. And of course the LEFT function comes up with the same Oct, 10, 2006 format.

How would I tell SQL server to just grab just 10/06/2006 and nothing else or without it trying to change it to a different format?

ThanksLook up the Convert function in BOL. There are a number of formats to display a date in.|||Wonderful, thanks!

DateTime in UTC

I have a DateTime column in a database table.

How can I get the equivalent UTC value for this column?

eg something like the DateTime.ToUniversalTime() in C#.

or select DATE_COL1, getutcdate(DATE_COL1) FROM TABLE

nb: The GETUTCDATE() function returns the current utc DATEIt depends what you want to do exactly - is the offset from UTC based
on where the server is physically, where the clients are physically, or
something else? I don't believe there's any easy way to do this in
MSSQL, because you need to know the server's location and current UTC
offset, so you would probably need an external program which gets this
information from the operating system. If you want to base the offset
on the clients' location, then things would be more complicated,
especially if you have clients in different time zones.

If the offset is constant, you could put it in a lookup table and
create your own scalar function to modify the date, but then you would
need to handle daylight savings and so on yourself as well. So if the
C# function you mentioned already does what you want, it might be
easiest just to use it in an external program (in SQL 2005 you could
write a C# stored procedure or function to do this).

Simon|||PromisedOyster (PromisedOyster@.hotmail.com) writes:
> I have a DateTime column in a database table.
> How can I get the equivalent UTC value for this column?
> eg something like the DateTime.ToUniversalTime() in C#.
> or select DATE_COL1, getutcdate(DATE_COL1) FROM TABLE
>
> nb: The GETUTCDATE() function returns the current utc DATE

Use the dateadd() function. You will have to handle the logic for
the offset to UTC yourself, as SQL Server does not have any time zone
information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||hi

hope this trick would work:
select DATE_COL1, dateadd("mi", datediff("mi",GETUTCDATE() ,getdate())
,DATE_COL1)

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||We do this all the time:

--test method
DECLARE @.Date smalldatetime
SET @.Date = GETDATE()

SELECT @.date, DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()),
@.Date), GETUTCDATE()

You could deal with minutes, but since UTC time is a change in hours,
simply your life :)

Stu|||Hi Stu
I think UTC time deals with 1/2 Hrs also. and because of this minute
should be correct.

For eg, India is +5.30 Hrs GMT

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||Thanks Stu

I worked this out myself and the proc I developed is about the same as
yours. I had to use minutes though to handle Central Australian Time.

Stu wrote:
> We do this all the time:
> --test method
> DECLARE @.Date smalldatetime
> SET @.Date = GETDATE()
> SELECT @.date, DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()),
> @.Date), GETUTCDATE()
> You could deal with minutes, but since UTC time is a change in hours,
> simply your life :)
> Stu|||Really? I never knew that. I always thought that the timezones were
shifts in hours; didnot realize they shifted in half hors as well.
That's gotta be a pain for mking long distance calls.

:)|||Stu wrote:
> Really? I never knew that. I always thought that the timezones were
> shifts in hours; didnot realize they shifted in half hors as well.
> That's gotta be a pain for mking long distance calls.
> :)
Yes, its true.

And to further complicate things, Nepal is GMT+5:45. They just had to
be different from India.

datetime in SQL Server 2000 SP2

Hello!
I have datetime column <CreatedDate> in table <sale>.
Here' s some sample data for CreatedDate:
2004-04-30 15:56:12.390
2004-04-30 15:59:42.000
2004-04-30 15:58:42.100
2004-04-30 16:01:06.190
2004-04-30 16:01:59.820
When running query below I'm not getting any rows back:
select name, CreatedDate
from sale
where CreatedDate between '04/01/2004' and '04/30/2004'
But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for CreaedDate '04/30/2004'.
From BOL:
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
Why sql server can't recognize '04/30/2004'?
Thanks,
Lena
Because '04/30/2004' really means '04/30/2004 00:00:00.000' and '2004-04-30
15:56:12.390' is greater than that.
Bojidar Alexandrov
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for
CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or
equal to the value of begin_expression and less than or equal to the value
of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>
|||I suggest you check out my article about datetime at:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of
begin_expression and less than or equal to the value of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>
|||Is there a way to use some date function in where clause so sql server will return all rows with CreatedDate for 04/30/2004 and will ignore time stamp?
Thanks
|||Lena,
This issue(plus many others) are discussed in the link that Tibor posted.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:BC32384D-6877-4DCF-9861-712082375A2D@.microsoft.com...
> Is there a way to use some date function in where clause so sql server
will return all rows with CreatedDate for 04/30/2004 and will ignore time
stamp?
> Thanks

datetime in SQL Server 2000 SP2

Hello!
I have datetime column <CreatedDate> in table <sale>.
Here' s some sample data for CreatedDate:
2004-04-30 15:56:12.390
2004-04-30 15:59:42.000
2004-04-30 15:58:42.100
2004-04-30 16:01:06.190
2004-04-30 16:01:59.820
When running query below I'm not getting any rows back:
select name, CreatedDate
from sale
where CreatedDate between '04/01/2004' and '04/30/2004'
But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for Cr
eaedDate '04/30/2004'.
From BOL:
BETWEEN returns TRUE if the value of test_expression is greater than or equa
l to the value of begin_expression and less than or equal to the value of en
d_expression.
Why sql server can't recognize '04/30/2004'?
Thanks,
LenaBecause '04/30/2004' really means '04/30/2004 00:00:00.000' and '2004-04-30
15:56:12.390' is greater than that.
Bojidar Alexandrov
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for
CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or
equal to the value of begin_expression and less than or equal to the value
of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>|||I suggest you check out my article about datetime at:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:8AAFC89E-8E21-4A93-987E-F3D142BEC4D1@.microsoft.com...
> Hello!
> I have datetime column <CreatedDate> in table <sale>.
> Here' s some sample data for CreatedDate:
> 2004-04-30 15:56:12.390
> 2004-04-30 15:59:42.000
> 2004-04-30 15:58:42.100
> 2004-04-30 16:01:06.190
> 2004-04-30 16:01:59.820
> When running query below I'm not getting any rows back:
> select name, CreatedDate
> from sale
> where CreatedDate between '04/01/2004' and '04/30/2004'
> But when changing '04/30/2004' to '05/01/2004' I' m getting rows back for
CreaedDate '04/30/2004'.
> From BOL:
> BETWEEN returns TRUE if the value of test_expression is greater than or equal to t
he value of
begin_expression and less than or equal to the value of end_expression.
>
> Why sql server can't recognize '04/30/2004'?
> Thanks,
> Lena
>
>|||Is there a way to use some date function in where clause so sql server will
return all rows with CreatedDate for 04/30/2004 and will ignore time stamp?
Thanks|||Lena,
This issue(plus many others) are discussed in the link that Tibor posted.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:BC32384D-6877-4DCF-9861-712082375A2D@.microsoft.com...
> Is there a way to use some date function in where clause so sql server
will return all rows with CreatedDate for 04/30/2004 and will ignore time
stamp?
> Thanks

datetime HOUR function format

I am using reporting services to make a matrix. The row value is the date portion of DateIn. The value is a count of transactions. The column type is the problem. It is the hour part of the timein value.

I got it from the database like this:

{fn HOUR(dbo.[Transaction].[TimeIn])} AS Hour

This works, but gives 24 hour time (and only the hour part, so it looks like 10, 11, 12, 13, 14, etc.)

I want it to look like 10:00 AM, 11:00 AM, 12:00 PM, 1:00, PM, etc.

I have read several books, checked online books, tried format functions... and I'm going nuts. This should be so simple- how do I format this so a human can read it? Thanks

If you need the database to do the conversion, then you can set the format code of the textbox to "t" and use the following expression.

=CDate(Fields!Hour.Value & ":00")

If you can use the raw date value from the database, then you can just set the format code of the textbox to "t". If you are grouping on only the hour, then you can still just get the raw date value from the database and use =Fields!TimeIn.Value.Hour as the group expression.

Datetime format for dimension Please help me

Dear All,

How can I format a dimesion datetime column . for example while browsing a cube, the value of the dimension column 'DateOrder' shows like that 2002-11-01 00:00:00. I wan to get 01/11/2002 dd/mm/yyyy.

What I have to do. When I changed their cell value property as dd/mm/yyyy it doesnpt working ..Please to crrect my problem

with regards

Polachah

You could create a named calculation in the DSV which formats the Date column however you like and the use this as the name of the attribute.|||

Thank for replying my requirement

I did the same way but the format is not changed.. Also I tried to use that cube in a pivot grid table and tried to change the format there. Still the format is shown as yyyy/mm/dd like that..

|||

You can either break the date into pieces and join it back together however you want

Code Snippet

datename(dd,DateOrder) + '/' + convert(varchar,month(DateOrder)) + '/' + datename(yyyy,DateOrder)

But you would have to do a bit more work on the above code to get it producing a leading "0" on the day and month.

Or you can use the third parameter of the convert function that is used when converting from a datetime to a string (which I prefer to use if I can)

Code Snippet

convert(varchar,DateOrder,103)

Format 103 is dd/mm/yyyy - Books Online has a list of all the format numbers in the help for the CONVERT() function|||

Dear sir

Thank you very mcuh ... for your help .. I got from your advice what I need thank u verymuch again

DATETIME field

I have a CreatedDate column of type datetime. I am trying to query for
CreatedDate = somedate, but not results are returned. I understand this is
because the time is also included in the data will never equal a specific
date. BOL recommends doing a string comparison using LIKE; however, it
doesn't return results either. Any suggestions?
WBWB,
What is the datatype somedate? Try CONVERTing CreatedDate to a format that
doesn't include the time:
SELECT CreatedDate FROM YourTable
WHERE CONVERT(char(8), CreatedDate, 112) = somedate
-Andy
"WB" <none> wrote in message news:OGPSuu4PFHA.1236@.TK2MSFTNGP14.phx.gbl...
>I have a CreatedDate column of type datetime. I am trying to query for
> CreatedDate = somedate, but not results are returned. I understand this
> is
> because the time is also included in the data will never equal a specific
> date. BOL recommends doing a string comparison using LIKE; however, it
> doesn't return results either. Any suggestions?
> WB
>|||somedate is usually an entry like '04/12/2005'
"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:%23dR1D84PFHA.2604@.TK2MSFTNGP10.phx.gbl...
> WB,
> What is the datatype somedate? Try CONVERTing CreatedDate to a format
that
> doesn't include the time:
> SELECT CreatedDate FROM YourTable
> WHERE CONVERT(char(8), CreatedDate, 112) = somedate
> -Andy
> "WB" <none> wrote in message news:OGPSuu4PFHA.1236@.TK2MSFTNGP14.phx.gbl...
specific
>|||> somedate is usually an entry like '04/12/2005'
But is that April 12th or December 4th? You can Google this group and find
tons of dicussions on this topic.
Good luck.
-Andy|||Try type 101 instead of 112, although it should work either way.
"WB" <none> wrote in message news:eUren%234PFHA.2788@.TK2MSFTNGP09.phx.gbl...
> somedate is usually an entry like '04/12/2005'
> "Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
> news:%23dR1D84PFHA.2604@.TK2MSFTNGP10.phx.gbl...
> that
> specific
>|||> BOL recommends doing a string comparison using LIKE;
BOL is WRONG, in my opinion.
If you want to find values for today, use a range query:
DECLARE @.dt SMALLDATETIME
SET @.dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT columns FROM table
WHERE dateColumn >= @.dt
AND dateColumn < (@.dt + 1)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
however, it
> doesn't return results either. Any suggestions?
> WB
>|||I suggest you check out http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:OGPSuu4PFHA.1236@.TK2MSFTNGP14.phx.gbl...
>I have a CreatedDate column of type datetime. I am trying to query for
> CreatedDate = somedate, but not results are returned. I understand this i
s
> because the time is also included in the data will never equal a specific
> date. BOL recommends doing a string comparison using LIKE; however, it
> doesn't return results either. Any suggestions?
> WB
>

datetime default value

Hi I am designing a table with vs.net 2003 and have a column that has a
datetime datatype. Just wondering if anyone knows if there is a way to set
its' default value to the current system time, like Now().
thanks.
--
Paul G
Software engineer.getdate()
Walter
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
> Hi I am designing a table with vs.net 2003 and have a column that has a
> datetime datatype. Just wondering if anyone knows if there is a way to
> set
> its' default value to the current system time, like Now().
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks. I ended up setting a constraint with SQL in the query analyzer
but your method appears much easier.
--
Paul G
Software engineer.
"Walter Mallon" wrote:

> getdate()
> Walter
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
>
>

datetime default value

Hi I am designing a table with vs.net 2003 and have a column that has a
datetime datatype. Just wondering if anyone knows if there is a way to set
its' default value to the current system time, like Now().
thanks.
--
Paul G
Software engineer.getdate()
Walter
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
> Hi I am designing a table with vs.net 2003 and have a column that has a
> datetime datatype. Just wondering if anyone knows if there is a way to
> set
> its' default value to the current system time, like Now().
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks. I ended up setting a constraint with SQL in the query analyzer
but your method appears much easier.
--
Paul G
Software engineer.
"Walter Mallon" wrote:
> getdate()
> Walter
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
> > Hi I am designing a table with vs.net 2003 and have a column that has a
> > datetime datatype. Just wondering if anyone knows if there is a way to
> > set
> > its' default value to the current system time, like Now().
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>

DateTime Datatype, How to display just Date, not time

I have a column with DateTime Datatype. But I want to display just Date , not time.

Like 4/26/2006 not 4/26/2006 9:25:55AM

pls help

check out the CONVERT and CAST functions. Try the following. If you check out books on line for the CONVERT functions they have a list of values and the formats the function will produce with the value. Here's an example:

SELECT CONVERT(varchar, getdate(), 101)

|||

Format it before output.

Cdate("10/1/2006 11:00:00").Tostring("d")

or if you are using it, and databinding it to a grid, textbox, etc, specify a format of "d".

datetime datatype conversion

Hello

I have 1 column in table with char datatype that stores datetime values (e.g.: 20061207091510 which translates to 2006-12-07 09:15:10).

Is there a way to convert this string into datetime datatype to preserve the time part (hours:minutes:seconds?

Thanks,

Lena

SELECT CONVERT(DATETIME, LEFT('20061207091510', 8), 112)+CONVERT(DATETIME, SUBSTRING('20061207091510', 9, 2) + ':' + SUBSTRING('20061207091510', 11, 2) + ':' + SUBSTRING('20061207091510', 13, 2), 114)

|||thank you!

Datetime data type resulted in an out-of-range datetime value. Please help

Hi,

I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date

'31/08/2006 23:28:59'

I get the error "...datetime data type resulted in an out-of-range datetime value"

I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?

cheers,

Ernest

I guess itis caused by the date format in SQL Server. Please try following statements:

set DATEFORMAT dmy
declare @.t smalldatetime
set @.t='31/08/2006 23:28:59'
select @.t

|||

Thanks Lori,

It appears that when I use parameters in my SqlCommand object this works like a treat. God bless the parameters!!