Comma Delimited Fields: smalldatetime

Showing posts with label smalldatetime. Show all posts

Thursday, March 29, 2012

Comparing time values

I have a table called WorkItem. It models a chunk of work done during
a working day.

It has two columns that I'm interested in:

Start (smalldatetime) - the TIME the work block is begun
Duration (int) - the duration in minutes of the work block.

In another table called OvertimeRates I have information about rate
multipliers and a column that tells me the TIME that the rate
multiplier kicks in.

e.g.

OTRateBegins (smalldatetime)

In terms of calculating whether a particular work block starts after
the OTRateBegins, I could (I presume) do something like:

If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,
OTRateBegins, 108)

However, would I be better off using DATEPART functions to get the hour
and minute parts of both the Start and OTRateBegins, and using them
instead? For some reason, (probably paranoia!), I am suspicious of the
CONVERT function.

Apologies for not posting DDL, but I felt that the situation didn't
really warrant it.

Thanks

EdwardI'm confused; if both columns are smalldatetime, then why are you
converting them to smalldatetime?

Stu|||On 25 Aug 2005 09:24:33 -0700, teddysnips@.hotmail.com wrote:

(snip)
>Start (smalldatetime) - the TIME the work block is begun
(...)
>OTRateBegins (smalldatetime)
(...)

>If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,
>OTRateBegins, 108)

Hi Edward,

If both columns store just a time (or rather: the datepart is left at
the default value), you can use a simple comparison:

IF Start > OTRateBegins

If either or both sport a date value as well, you'll need another
solution. The convert might work (no reason for your paranoia), but
there might be better solutions as well.

If you post CREATE TABLE statements, some INSERT statements with sample
data, and expected output, it'll be easier to help you.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On 25 Aug 2005 09:24:33 -0700, teddysnips@.hotmail.com wrote:
> (snip)
> >Start (smalldatetime) - the TIME the work block is begun
> (...)
> >OTRateBegins (smalldatetime)
> (...)
> >If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,
> >OTRateBegins, 108)
> Hi Edward,
> If both columns store just a time (or rather: the datepart is left at
> the default value), you can use a simple comparison:
> IF Start > OTRateBegins
> If either or both sport a date value as well, you'll need another
> solution. The convert might work (no reason for your paranoia), but
> there might be better solutions as well.
> If you post CREATE TABLE statements, some INSERT statements with sample
> data, and expected output, it'll be easier to help you.

Thanks Hugo.

I don't particularly want to post DDL and INSERT statements, as there's
no real data to play with at the moment, and the table relations are a
good deal more complicated than I've let on.

However, suffice it to say that the datepart of the OTRateBegins is
entirely disposable - I'm only interested in the timepart element. So
I just want to ignore the datepart.

In terms of what I personally wish to do (I'm actually developing a UDF
to return accumulated minutes multiplied by the correct OT rate
multiplier) I'm not interested in the datepart of the Start column, but
in fact the datepart of this column is crucial, as it tells one when
the block of work was done (there are different multipliers for
different time periods and different days).

Hence the reason for the CONVERT function (as Stu asked), which I was
using merely to expose the timepart of the two fields. Is there a
better way? As I suggested in my original post, I *could* strip out
the HOUR and MINUTE values using the DATEPART function, and do some
rather more complex comparisons using them, but that seems rather
inefficient.

I dunno. As usual, I'm probably ignoring the obvious and elegant in
favour of the simple and quick. It was like that when I was a C
programmer - I never could get the hang of doing stuff in-line.

Anyway, many thanks for your help.

Edward|||On 25 Aug 2005 12:36:24 -0700, teddysnips@.hotmail.com wrote:

(snip)
>I don't particularly want to post DDL and INSERT statements, as there's
>no real data to play with at the moment, and the table relations are a
>good deal more complicated than I've let on.
>However, suffice it to say that the datepart of the OTRateBegins is
>entirely disposable - I'm only interested in the timepart element. So
>I just want to ignore the datepart.

Hi Edward,

The reason I asked for DDL and INSERT statements is to make sure that
there can be no misunderstanding. It may be because of me not being a
native English speaker, but I'm still not sure if your OTRateBegins
column will contain a time combined with an (irrelevant) date, or if
they will contain only the time (*).

(*) SQL Server will of course always store a date - "only the time"
means that you don't provide any date; in that case, SQL Server will use
the default date (January 1st 1900).

Anyway, here's a more generic answer for comparisons where you want to
compare only the time portion of the datetime:

- If both Column1 and Column2 contain only a time, you can compare them
with a straight comparison (Column1 > Column2); the advantage is that
the optimizer can choose to use any index on either or both of these
columns.
- If one of the columns contains a date + time and the other contains
only the time, you'll have to strip the datepart of the column with date
(Column1 > CONVERT(char(12), Column2, 114) or CONVERT(char(12), Column1,
114) > Column2); in this case, the optimizer can still use an index on
the column that has only the time - the other column is used in a
function, which precludes the use of an index.
- If both columns containt date and time, you'll have to strip both
(CONVERT(char(12), Column1, 114) > CONVERT(char(12), Column2, 114)); the
disadvantage is that the optimizer can't use the indexes on any of these
columns.

If you don't need millisecond precision, you can also use
CONVERT(char(8), Column1, 108).

>In terms of what I personally wish to do (I'm actually developing a UDF
>to return accumulated minutes multiplied by the correct OT rate
>multiplier)
(snip)

I was afraid that it'd be something like that. Yet another reason to
post the CREATE TABLE statements plus some sample data (can be made up)
and expected output, plus the code you currently have.

The use of IF in your original post suggests that you process your input
table row by row. In 99% of all cases, a set-based solution is faster,
shorter, easier to understand and hence also easier to maintain. If you
provide some more information, I (and maybe others as well) can take a
look at your logic and try our hand at converting it to a set-based
version.

(snip)
> As I suggested in my original post, I *could* strip out
>the HOUR and MINUTE values using the DATEPART function, and do some
>rather more complex comparisons using them, but that seems rather
>inefficient.

Ugh! Please don't go there - why would you even want to write messy and
complex code when a simple comparison (with CONVERT, if you have to use
it) will do?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||OK, now I'm even more confused. Too much time behind my keyboard, I
suppose. Hugo, why can't he do a simple compare if both columns
contain a date and time? Here's my example:

DECLARE @.Start smalldatetime
DECLARE @.OTRateBegins smalldatetime

SET @.Start = '6/1/2005 4:00:00 AM'
SET @.OTRateBegins = '5/30/2005 4:00:00 AM'

IF @.Start > @.OtRateBegins
PRINT 'Start > OTRate'
ELSE
PRINT 'SPLAT'

I guess the question is: what does OTRateBegins represent? Is it the
time of the day that overtime kicks in? Or is it a calendar date and
time that represents the overtime phase of a project (e.g., you have a
contract for 100 hours, and you want to be paid the overtime rate if
you go past the 100th hour)?

Still confused.
Stu|||Stu wrote:
> OK, now I'm even more confused. Too much time behind my keyboard, I
> suppose. Hugo, why can't he do a simple compare if both columns
> contain a date and time? Here's my example:
> DECLARE @.Start smalldatetime
> DECLARE @.OTRateBegins smalldatetime
> SET @.Start = '6/1/2005 4:00:00 AM'
> SET @.OTRateBegins = '5/30/2005 4:00:00 AM'
> IF @.Start > @.OtRateBegins
> PRINT 'Start > OTRate'
> ELSE
> PRINT 'SPLAT'
> I guess the question is: what does OTRateBegins represent? Is it the
> time of the day that overtime kicks in?

Yes, which is why I can't do a simple comparison as above
> Or is it a calendar date and
> time that represents the overtime phase of a project (e.g., you have a
> contract for 100 hours, and you want to be paid the overtime rate if
> you go past the 100th hour)?

No!

Thanks Stu and Hugo for your work on this. As it happens, Hugo nailed
it in the previous post. For the comparison I'm trying to do, it is
only the time component that matters, so the use of 108 (I don't need
milliseconds!) for the CONVERT style will be fine.

As Hugo guessed, I'm NOT using a SET based solution, because the
requirements are too complicated for my tiny brain! I'm going to
persevere with my design, and see how it performs. If it's a
three-legged dog, I'll come back here with my begging bowl, and some
DDL and INSERT statements and tax your generosity some more.

But in the meantime, THANK YOU to Stu and Hugo!

Edward

Tuesday, March 27, 2012

Comparing Smalldatetime column

quote:

> I have two Smalldatetime columns. One of the columns have
> NULL values in some of the rows. When I compare the two
> columns, I don't get the rows that have NULL values (I am
> comparing them to be NOT the same e.g Select * from
> mytable where column1 <> column2). I get all the rows that
> are not the same but have values but not the NULL columns.
> How can I get the NULL columns too '
>
> Thanks for help.

|||> b) you could say SET ANSI_NULLS OFF
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.

quote:

>--Original Message--
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -

it's still UNKNOWN.

quote:

>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.

quote:

>--Original Message--
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -

it's still UNKNOWN.

quote:

>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

|||SORRY FOR THE DOUBLE POST...

quote:

>--Original Message--
>I guess I have something wrong in my query. I also was
>trying to exclude the values in one of the columns which
>was the dates that had 1900 in it (E.g NOT Like '%

1900%').

quote:

>I was trying to add that statement to
>SELECT *
> FROM mytable
> WHERE column1 <> column2
> OR column1 IS NULL
> OR column2 IS NULL
>--OR/AND column1 Not like '%1900%'
>but somehow it is eliminating the NULL columns.
>Thanks.
>
>it's still UNKNOWN.
>.
>

|||It helps if you can post some code to reproduce your problem and show your
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.

quote:

>--Original Message--
>It helps if you can post some code to reproduce your

problem and show your

quote:

>required results. It's not clear to me exactly what

result you're trying to

quote:

>get in this case.
>When combining AND and OR you have to watch out for the

order of precedence.

quote:

>Use parentheses to make the order of evaluation clear

otherwise x OR y AND z

quote:

>is evaluated as x OR (y AND z).
>Don't use LIKE to compare dates. LIKE performs a string

comparison and if

quote:

>you use it on dates then every date has to be cast as a

string. Best way to

quote:

>eliminate the year 1900 is to test for a date >= 1901-01-

01.

quote:

>Here's some code to reproduce a result that *may* be what

you want:

quote:

>CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1

SMALLDATETIME,

quote:

>column2 SMALLDATETIME)
>INSERT INTO MyTable VALUES (1,'20030101','20030101')
>INSERT INTO MyTable VALUES (2,'20030101','20030102')
>INSERT INTO MyTable VALUES (3,'19000101','19000101')
>INSERT INTO MyTable VALUES (4,'19000101','20030101')
>INSERT INTO MyTable VALUES (5,'20030101','19000101')
>INSERT INTO MyTable VALUES (6,'19000101',NULL)
>INSERT INTO MyTable VALUES (7,NULL,'19000101')
>INSERT INTO MyTable VALUES (8,'20030101',NULL)
>INSERT INTO MyTable VALUES (9,NULL,'20030101')
>INSERT INTO MyTable VALUES (10,NULL,NULL)
>SELECT *
> FROM MyTable
> WHERE
> COALESCE(column1,'19000101') <> COALESCE

(column2,'19000101')

quote:
d">
> AND
> (column1 >= '19010101' OR column2 >= '19010101')
>Which returns the rows where Keycol is 2,4,5,8 and 9.
>If that's not it and you need more help then you'll have

to specify which

quote:

>rows you want to be included in your result. I should

have covered all the

quote:

>relevant combinations.
>Hope this helps.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

Comparing Smalldatetime column

I have two Smalldatetime columns. One of the columns have
NULL values in some of the rows. When I compare the two
columns, I don't get the rows that have NULL values (I am
comparing them to be NOT the same e.g Select * from
mytable where column1 <> column2). I get all the rows that
are not the same but have values but not the NULL columns.
How can I get the NULL columns too '
Thanks for help.IS NULL and IS NOT NULL are the predicates used to test for the presence or
absence of NULL:
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
You can read about three-value logic and NULL values in Books Online:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_02_8pwy.asp
In general a comparison of anything to NULL is always UNKNOWN which means
NULL values are excluded by statements like
...
WHERE x <> y
--
David Portas
--
Please reply only to the newsgroup
--|||ANSI defines NULL = NULL as false.
NULL is the abscence of a value and, NULL does not equal NULL.
You can:
a) use IS NULL to perform the test or
b) you could say SET ANSI_NULLS OFF
Books Online will have lot's more info on each option... I hope this helps,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:000e01c3dc43$7c50b750$a101280a@.phx.gbl...
> I have two Smalldatetime columns. One of the columns have
> NULL values in some of the rows. When I compare the two
> columns, I don't get the rows that have NULL values (I am
> comparing them to be NOT the same e.g Select * from
> mytable where column1 <> column2). I get all the rows that
> are not the same but have values but not the NULL columns.
> How can I get the NULL columns too '
>
> Thanks for help.|||> b) you could say SET ANSI_NULLS OFF
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
--
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||SORRY FOR THE DOUBLE POST...
>--Original Message--
>I guess I have something wrong in my query. I also was
>trying to exclude the values in one of the columns which
>was the dates that had 1900 in it (E.g NOT Like '%
1900%').
>I was trying to add that statement to
>SELECT *
> FROM mytable
> WHERE column1 <> column2
> OR column1 IS NULL
> OR column2 IS NULL
>--OR/AND column1 Not like '%1900%'
>but somehow it is eliminating the NULL columns.
>Thanks.
>>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
>it's still UNKNOWN.
>>--
>>David Portas
>>--
>>Please reply only to the newsgroup
>>--
>>
>>.
>.
>|||It helps if you can post some code to reproduce your problem and show your
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.
>--Original Message--
>It helps if you can post some code to reproduce your
problem and show your
>required results. It's not clear to me exactly what
result you're trying to
>get in this case.
>When combining AND and OR you have to watch out for the
order of precedence.
>Use parentheses to make the order of evaluation clear
otherwise x OR y AND z
>is evaluated as x OR (y AND z).
>Don't use LIKE to compare dates. LIKE performs a string
comparison and if
>you use it on dates then every date has to be cast as a
string. Best way to
>eliminate the year 1900 is to test for a date >= 1901-01-
01.
>Here's some code to reproduce a result that *may* be what
you want:
>CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1
SMALLDATETIME,
>column2 SMALLDATETIME)
>INSERT INTO MyTable VALUES (1,'20030101','20030101')
>INSERT INTO MyTable VALUES (2,'20030101','20030102')
>INSERT INTO MyTable VALUES (3,'19000101','19000101')
>INSERT INTO MyTable VALUES (4,'19000101','20030101')
>INSERT INTO MyTable VALUES (5,'20030101','19000101')
>INSERT INTO MyTable VALUES (6,'19000101',NULL)
>INSERT INTO MyTable VALUES (7,NULL,'19000101')
>INSERT INTO MyTable VALUES (8,'20030101',NULL)
>INSERT INTO MyTable VALUES (9,NULL,'20030101')
>INSERT INTO MyTable VALUES (10,NULL,NULL)
>SELECT *
> FROM MyTable
> WHERE
> COALESCE(column1,'19000101') <> COALESCE
(column2,'19000101')
> AND
> (column1 >= '19010101' OR column2 >= '19010101')
>Which returns the rows where Keycol is 2,4,5,8 and 9.
>If that's not it and you need more help then you'll have
to specify which
>rows you want to be included in your result. I should
have covered all the
>relevant combinations.
>Hope this helps.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

Thursday, March 29, 2012

Comparing time values

Tuesday, March 27, 2012

Comparing Smalldatetime column

Comparing Smalldatetime column

Comma Delimited Fields

Blog Archive

About Me