Query Question

I have a query that I am not sure how to write.
I need to get the value of a column where that column's value happens more
than once AND a date column has the same date.
for instance:
ColA ColB
--
ValA 1/1/2003
ValA 1/1/2003
ValX 1/1/2003
I need to find all values from ColA where there is another row with the same
value in ColA and ColB (in this case the query should return ValA).
?SELECT DISTINCT colA
FROM Sometable
GROUP BY colA, colB
HAVING COUNT(*)>1
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks David,
Makes sense and did the trick
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:-PWdnfOhm6CPriCi4p2dnA@.giganews.com...
> SELECT DISTINCT colA
> FROM Sometable
> GROUP BY colA, colB
> HAVING COUNT(*)>1
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||select cola from table_name
group by cola,colb
having count(*) >1
_______________________
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:OvXOcu6rDHA.1764@.TK2MSFTNGP10.phx.gbl...
> I have a query that I am not sure how to write.
> I need to get the value of a column where that column's value happens more
> than once AND a date column has the same date.
> for instance:
> ColA ColB
> --
> ValA 1/1/2003
> ValA 1/1/2003
> ValX 1/1/2003
> I need to find all values from ColA where there is another row with the
same
> value in ColA and ColB (in this case the query should return ValA).
> ?
>
>

Query problems - Group By and Latest date

Hi all,

hopefully someone can suggest the best way of implementing the problem i am trying to resolve. We have a table which contains rows relating to tests run on our product. This table is populated from an SSIS job which parses CSV files.

There are multiple rows per serial number relating to multiple tests. The only tests i am interested in are the ones with an ID of T120. Here is the query i have so far which should make it a little easier to explain:

SELECT [SerialNumber]
,Param1
,[TimeStamp]
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1, [TimeStamp]
ORDER BY SerialNumber

What i have above is fine to a point. The problem i am encountering is that in test T120 it specifies a part which can be be one of about 6 in field Param1. If during testing there is a problem with the part then it is replaced and the test run a second time up until the whole product passes the test. The query above returns all instances of replacements so i may have the out put as follows:

SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03

What i really need is to only list the last part that is installed, hence the one with the latest timestamp:

SerialNumber Param1 TimeStamp

0 Part1 15/03/07

0 Part2 16/03/07

0 Part3 15/03/03

Can someone please help me to alter the above query so that it will show only those Param1 fields that have the latest date for each part.

Many thanks in advance,

Grant

This should do the trick:

SELECT [SerialNumber]
,Param1
,MAX([TimeStamp])
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
ORDER BY SerialNumber

You only need to take the max of your timestamp field (and remove it from the group by). The group by all fields is a bit extreme in the previous query (you could use the distinct keyword instead if you had apparent duplicate rows (i.e. replaced the part 3 times in a day).

|||Thats sorted it.
I wasn't as far of the mark in the first place as i'd thought. Thank's very much for the assistance, its much appreciated.

Cheers,

Grant|||Hi, apologies but i need one more piece of advice on this subject.

If i want to include a column with a serial number of the part that has been replaced, how would i do that. As soon as i add it, it needs to be part of an aggregate function or the group by clause. When it becomes part of the group by clause it then duplicates the part again.

Any ideas?

Thanks,

Grant|||

Is the serial number of the part in the same table - if so presumably it is different for each time that part is replaced. You can use a nested query to get that - however it will run into a problem if there are multiple records with the same date. As it stands at the moment you could not distinuish between them.

If you had records:

SerialNumber Param1 TimeStamp Part_SN
0 Part1 15/03/07 1234
0 Part2 15/03/07 1235
0 Part2 16/03/07 1236
0 Part2 16/03/07 1237
0 Part3 15/03/03 1238

How would you know which of the two Part2 items fitted on 16 Mar to give the serial number of? If there are additional fields to determine this then we need to use them

If this does not arise then the query below should serve (substitute correct fieldname for Part_SN):

SELECT B.[SerialNumber]
,B.Param1
,B.[TimeStamp]
,B.Part_SN
FROM (
SELECT [SerialNumber]
,Param1
,MAX([TimeStamp]) AS TimeStamp
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1
) A
INNER JOIN [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL] B
ON (A.[SerialNumber] = B.[SerialNumber]) AND
(A.Param1 = B.Param1) AND
(A.[TimeStamp] = B.[TimeStamp]) AND
(B.Test = 'T120')
ORDER BY B.[SerialNumber]

If this is a problem then you will get multiple records in that case - one for each serial number. If the TimeStamp is a datetime which includes the time of the replacement then this will not be an issue (as long as the required serial number is the last record.

|||Thanks,

That is exactly what i wanted to do. Works like a charm.

Grantsql

Query Perormance (Problem bookmark)

I have a bookmark caused by the a15.Tr_type_id where condition below. I thin
k
it is because the query tries to satisfy the date condition before going to
get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
bookmark disappears and performance boosts. I tried a clustered index on
a15.Tr_type_id and it improved it a bit more. Any recommendations much
appreciated?
select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
a14.Ra_gender_id Ra_gender_id,
a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
a14.RA_yr_band_New_id RA_yr_band_New_id,
a12.Pr_cover_id Pr_cover_id,
count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_id
end)) WJXBFS1
into #ZZT5J0302LPMD004
from Z_fat_bse_po_risk_detail a11
join Z_prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join Z_POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join Z_RAt_lu_Rated a14
on (a11.Rated_driver_id = a14.Rated_driver_id)
join Z_TRt_lu_Trans_Subtype a15
on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
where (a12.Pr_cover_id in ('C', 'F')
and a14.Ra_gender_id in ('F', 'M')
and a14.Ra_licence_id in ('F', 'P')
and a12.Pr_Group_id in (2, 3)
and a14.RA_yr_band_New_id not in (1)
and a11.Po_tr_bus_type_id in (0)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
and CONVERT(datetime, '2005-03-05 00:00:00', 120)
and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a13.Po_corp_unit_id in ('GEI')
and a11.Entered_by_id not in (7561, 7570)
and a12.Pr_cover_id in ('C', 'F', 'T')
and a14.Ra_gender_id in ('F', 'M')
and a13.Po_market_src_id not in (14))
group by a14.Ra_licence_Group_desc,
a14.Ra_gender_id,
a14.RA_yr_band_HFI_id,
a14.RA_yr_band_New_id,
a12.Pr_cover_idgolden rule, check statistics job has run before you believe an end user!
"marcmc" wrote:

> I have a bookmark caused by the a15.Tr_type_id where condition below. I th
ink
> it is because the query tries to satisfy the date condition before going t
o
> get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
> bookmark disappears and performance boosts. I tried a clustered index on
> a15.Tr_type_id and it improved it a bit more. Any recommendations much
> appreciated?
> select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
> a14.Ra_gender_id Ra_gender_id,
> a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id RA_yr_band_New_id,
> a12.Pr_cover_id Pr_cover_id,
> count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_
id
> end)) WJXBFS1
> into #ZZT5J0302LPMD004
> from Z_fat_bse_po_risk_detail a11
> join Z_prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join Z_POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join Z_RAt_lu_Rated a14
> on (a11.Rated_driver_id = a14.Rated_driver_id)
> join Z_TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> where (a12.Pr_cover_id in ('C', 'F')
> and a14.Ra_gender_id in ('F', 'M')
> and a14.Ra_licence_id in ('F', 'P')
> and a12.Pr_Group_id in (2, 3)
> and a14.RA_yr_band_New_id not in (1)
> and a11.Po_tr_bus_type_id in (0)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
> and CONVERT(datetime, '2005-03-05 00:00:00', 120)
> and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a13.Po_corp_unit_id in ('GEI')
> and a11.Entered_by_id not in (7561, 7570)
> and a12.Pr_cover_id in ('C', 'F', 'T')
> and a14.Ra_gender_id in ('F', 'M')
> and a13.Po_market_src_id not in (14))
> group by a14.Ra_licence_Group_desc,
> a14.Ra_gender_id,
> a14.RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id,
> a12.Pr_cover_id
>|||ps: do MVP's ever visit this forum
"marcmc" wrote:

> I have a bookmark caused by the a15.Tr_type_id where condition below. I th
ink
> it is because the query tries to satisfy the date condition before going t
o
> get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
> bookmark disappears and performance boosts. I tried a clustered index on
> a15.Tr_type_id and it improved it a bit more. Any recommendations much
> appreciated?
> select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
> a14.Ra_gender_id Ra_gender_id,
> a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id RA_yr_band_New_id,
> a12.Pr_cover_id Pr_cover_id,
> count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_
id
> end)) WJXBFS1
> into #ZZT5J0302LPMD004
> from Z_fat_bse_po_risk_detail a11
> join Z_prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join Z_POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join Z_RAt_lu_Rated a14
> on (a11.Rated_driver_id = a14.Rated_driver_id)
> join Z_TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> where (a12.Pr_cover_id in ('C', 'F')
> and a14.Ra_gender_id in ('F', 'M')
> and a14.Ra_licence_id in ('F', 'P')
> and a12.Pr_Group_id in (2, 3)
> and a14.RA_yr_band_New_id not in (1)
> and a11.Po_tr_bus_type_id in (0)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
> and CONVERT(datetime, '2005-03-05 00:00:00', 120)
> and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a13.Po_corp_unit_id in ('GEI')
> and a11.Entered_by_id not in (7561, 7570)
> and a12.Pr_cover_id in ('C', 'F', 'T')
> and a14.Ra_gender_id in ('F', 'M')
> and a13.Po_market_src_id not in (14))
> group by a14.Ra_licence_Group_desc,
> a14.Ra_gender_id,
> a14.RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id,
> a12.Pr_cover_id
>|||"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E13FCDE5-D0FD-4D1F-922B-CAEC5C9A584A@.microsoft.com...
> ps: do MVP's ever visit this forum
Yes. I see 12 messages posted here since 2:09PM PST yesterday, the 9th, and
exactly half of those posts came from MVPs Mike Epprecht, Jasper Smith, and
Sue Hoegemeier.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.|||Ahh I see now. It's just that when I used to post on compact framework site
there was a little bubble with mvp which was easily noticeable. Thx.
"Stephen Dybing [MSFT]" wrote:

> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:E13FCDE5-D0FD-4D1F-922B-CAEC5C9A584A@.microsoft.com...
> Yes. I see 12 messages posted here since 2:09PM PST yesterday, the 9th, an
d
> exactly half of those posts came from MVPs Mike Epprecht, Jasper Smith, an
d
> Sue Hoegemeier.
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Please reply to the newsgroups only, thanks.
>
>|||> Ahh I see now. It's just that when I used to post on compact framework
> site
> there was a little bubble with mvp which was easily noticeable.
I guess the SQL Server MVPs are a bit more low-key :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:0A8A3596-B262-4A78-93B3-861293435199@.microsoft.com...[vbcol=seagreen]
> Ahh I see now. It's just that when I used to post on compact framework
> site
> there was a little bubble with mvp which was easily noticeable. Thx.
> "Stephen Dybing [MSFT]" wrote:
>|||I just noticed...if we post through the web based news reader on Microsoft's
communities site, it puts the little MVP bubble thing by our names. My posts
earlier using Agent newsreader (over 99% of my posts) don't have the bubble
thing.
So if you want a bubble by your name, that's how you can have one!
-Sue
"Dan Guzman" wrote:

> I guess the SQL Server MVPs are a bit more low-key :-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:0A8A3596-B262-4A78-93B3-861293435199@.microsoft.com...
>
>

Query parameter drop-down not available once report is cached

Hi, I have a date parameter that is populated from a query to give me a
month drop-down on my report. When a user selects the month and runs
the report, it passes the parameter to a query to return a dataset.
This is all straight forward. Since the report runs quite slowly, I
wanted to cache the report witha dafault month, but allow the users to
then re-run the report with other months. I found that once I cached
the report on a shared schedule, the month drop-down is no longer
available for me to select a new query parameter to rerun the report.
Another filter parameter is available, so I am assuming it it because
it is a query parameter.
Is there a way to cache the reoprt, but still allow users to reselect
the query parameter and rerun the report?You can make the month parameter a filter parameter, bring back a all the
months data and then apply the month filter to the data regions in the
report..
Or if you are using Enterprise Edition you could preload the cache using a
data-driven subscription, see "Preloading the Cache"
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_processing_v1_2p89.asp
--
Douglas McDowell
douglas@.nospam.solidqualitylearning.com
"MDXQuery" <imgroup1@.hotmail.com> wrote in message
news:1109694129.517130.311640@.g14g2000cwa.googlegroups.com...
> Hi, I have a date parameter that is populated from a query to give me a
> month drop-down on my report. When a user selects the month and runs
> the report, it passes the parameter to a query to return a dataset.
> This is all straight forward. Since the report runs quite slowly, I
> wanted to cache the report witha dafault month, but allow the users to
> then re-run the report with other months. I found that once I cached
> the report on a shared schedule, the month drop-down is no longer
> available for me to select a new query parameter to rerun the report.
> Another filter parameter is available, so I am assuming it it because
> it is a query parameter.
> Is there a way to cache the reoprt, but still allow users to reselect
> the query parameter and rerun the report?
>

Query over two columns

Hi,
I have a table that includes two Datetime columns. The first column holds
the date. e.g 2006/5/10 and the second column holds time e,g 08:15:25.
I am having a problems specifing a query that will list the rows in the
table between a date time range.
i.e return all events between 2006/5/8 09:00:00 and 2006/5/10 21:30:00
Can anyone give me any suggesstions on how to do this?
Thanks
Maccawhy do you store it in two datetime columns?
or is it 2 varchar columns?
If so then try this.
select * from [a table]
where cast(datecol + ' ' + timecol as datetime) between
'2006/5/8 09:00:00' and '2006/5/10 21:30:00'|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?

Query on large record

First thing I am new to write a query. I have two tables that I need
information from. The first table has the user, date and time. The second
table has the user, date, time and a record that contains 132 characters.
What I need to do is match the user, Date and time along with 7 characters
that are placed 7 positions in the record.
This is the begining of the record looks like and I only need the 0685043,
is this possible?
OVRIDE 0685043
Thanks in advance for any help.
I don't know what you're matching the 0685043 with, but
you will probably need the SUBSTRING function, which you
can learn about from Books Online, to extract that from the
rest of the 132 character string.
Generally, if substrings of a column have meaning of their own,
it is better to keep that information in a separate column of the
table.
Steve Kass
Drew University
Daniell wrote:

>First thing I am new to write a query. I have two tables that I need
>information from. The first table has the user, date and time. The second
>table has the user, date, time and a record that contains 132 characters.
>What I need to do is match the user, Date and time along with 7 characters
>that are placed 7 positions in the record.
>This is the begining of the record looks like and I only need the 0685043,
>is this possible?
>OVRIDE 0685043
>Thanks in advance for any help.
>
>
|||Thanks Steve I guess I should have explained a little better. I will give
the SUBSTRING a try.
"Steve Kass" wrote:

> I don't know what you're matching the 0685043 with, but
> you will probably need the SUBSTRING function, which you
> can learn about from Books Online, to extract that from the
> rest of the 132 character string.
> Generally, if substrings of a column have meaning of their own,
> it is better to keep that information in a separate column of the
> table.
> Steve Kass
> Drew University
> Daniell wrote:
>

Query on date/time data type

I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
I've been trying CONVERT and CAST but to no avail. There's no examples on
how to utilize the TIME part of this smalldatetime data type.
Thx,
Don
SQL2000
WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
AND MyDateTimeColumn <= '2006-10-24 10:55:00'
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
>I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
> Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
> I've been trying CONVERT and CAST but to no avail. There's no examples on
> how to utilize the TIME part of this smalldatetime data type.
> Thx,
> Don
> SQL2000
>
|||DOH! )
I was trying to make it too compliated!
Thanks!
Don
"Arnie Rowland" wrote:
[vbcol=seagreen]
> WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
> AND MyDateTimeColumn <= '2006-10-24 10:55:00'
> )
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
|||I recommend that you use either of below two formats for the datetime strings:
'20061024 01:00:00'
'2006-10-24T01:00:00'
If you use any other format, the app will be language dependent
(http://www.karaszi.com/SQLServer/inf...asp#DtFormats). as shown below:
SET LANGUAGE german
SELECT CAST('2006-10-24 01:00:00' AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:98D78D54-EFF9-4097-BBD5-80D338D2663F@.microsoft.com...[vbcol=seagreen]
> DOH! )
> I was trying to make it too compliated!
> Thanks!
> Don
>
> "Arnie Rowland" wrote:

Query on date/time data type

I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
I've been trying CONVERT and CAST but to no avail. There's no examples on
how to utilize the TIME part of this smalldatetime data type.
Thx,
Don
SQL2000WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
AND MyDateTimeColumn <= '2006-10-24 10:55:00'
)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D
40-8B10-5D6AEB90CDCE@.microsoft.com...
>I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
>
> Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
>
> I've been trying CONVERT and CAST but to no avail. There's no examples on
> how to utilize the TIME part of this smalldatetime data type.
>
> Thx,
> Don
> SQL2000
>
>|||DOH! )
I was trying to make it too compliated!
Thanks!
Don
"Arnie Rowland" wrote:
[vbcol=seagreen]
> WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
> AND MyDateTimeColumn <= '2006-10-24 10:55:00'
> )
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e top yourself.
> - H. Norman Schwarzkopf
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message new
s:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...|||I recommend that you use either of below two formats for the datetime string
s:
'20061024 01:00:00'
'2006-10-24T01:00:00'
If you use any other format, the app will be language dependent
(http://www.karaszi.com/SQLServer/in...e.asp#DtFormats). as shown bel
ow:
SET LANGUAGE german
SELECT CAST('2006-10-24 01:00:00' AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:98D78D54-EFF9-4097-BBD5-80D338D2663F@.microsoft.com...[vbcol=seagreen]
> DOH! )
> I was trying to make it too compliated!
> Thanks!
> Don
>
> "Arnie Rowland" wrote:
>

Query on date/time data type

I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
I've been trying CONVERT and CAST but to no avail. There's no examples on
how to utilize the TIME part of this smalldatetime data type.
Thx,
Don
SQL2000This is a multi-part message in MIME format.
--=_NextPart_000_06CE_01C6F760.251370F0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
WHERE ( MyDateTimeColumn >=3D '2006-10-24 01:00:00' AND MyDateTimeColumn <=3D '2006-10-24 10:55:00'
)
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message =news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
>I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
> > Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
> > I've been trying CONVERT and CAST but to no avail. There's no examples =on > how to utilize the TIME part of this smalldatetime data type.
> > Thx,
> Don
> SQL2000
> >
--=_NextPart_000_06CE_01C6F760.251370F0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

WHERE ( =MyDateTimeColumn >=3D '2006-10-24 01:00:00'
AND MyDateTimeColumn <=3D '2006-10-24 10:55:00'
)
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"donsql22222" wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...>I =need everthing from 1AM to 10:55AM. Can't figure out the syntax.> > Need =from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"> > I've =been trying CONVERT and CAST but to no avail. There's no examples on > how to =utilize the TIME part of this smalldatetime data type.> > =Thx,> Don> SQL2000> >

---

--=_NextPart_000_06CE_01C6F760.251370F0--|||DOH! :))
I was trying to make it too compliated!
Thanks!
Don
"Arnie Rowland" wrote:
> WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
> AND MyDateTimeColumn <= '2006-10-24 10:55:00'
> )
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
> >I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
> >
> > Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
> >
> > I've been trying CONVERT and CAST but to no avail. There's no examples on
> > how to utilize the TIME part of this smalldatetime data type.
> >
> > Thx,
> > Don
> > SQL2000
> >
> >|||I recommend that you use either of below two formats for the datetime strings:
'20061024 01:00:00'
'2006-10-24T01:00:00'
If you use any other format, the app will be language dependent
(http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormats). as shown below:
SET LANGUAGE german
SELECT CAST('2006-10-24 01:00:00' AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:98D78D54-EFF9-4097-BBD5-80D338D2663F@.microsoft.com...
> DOH! :))
> I was trying to make it too compliated!
> Thanks!
> Don
>
> "Arnie Rowland" wrote:
>> WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
>> AND MyDateTimeColumn <= '2006-10-24 10:55:00'
>> )
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to the top yourself.
>> - H. Norman Schwarzkopf
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
>> >I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
>> >
>> > Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
>> >
>> > I've been trying CONVERT and CAST but to no avail. There's no examples on
>> > how to utilize the TIME part of this smalldatetime data type.
>> >
>> > Thx,
>> > Don
>> > SQL2000
>> >
>> >

query on date field with AM/PM designation

I need to query a table that has the date field formatted as follows:

12/1/2000 12:33:16 PM

The query basically is looking for a range between two dates.

select * from area_formulae where create_dt > to_date('12/1/2000 12:33:15','mm/dd/yyyy hh:mi:ss')
and create_dt < to_date('12/1/2000 12:33:18','mm/dd/yyyy hh:mi:ss')

This query that will work but it ignores the AM or PM designation. How would I query for a range of dates that would cover both the AM and PM ranges?Can you do this?

'mm/dd/yyyy hh:mi:ss AM/PM'

Query not returning proper data (date related) in 2005 after upgrade from 2000...

I am sending out an SOS.

Here is the situation:

We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can’t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn’t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea’s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.

Thank you.

ScottIm very curious about this situation. Can you post the query that you are running?|||

It is very long but here it is...

select data.shipment,
po_line,
data.style,
p.prodcat1 as omnia_sbu,
Left(p.prodcat1, 3) as sbu_mstr,
isnull(CONVERT(CHAR(10), OrigCRD, 101),'') OrigCRD,
isnull(CONVERT(CHAR(10), CRD, 101),'') CRD,
isnull(OrigNDC, '') OrigNDC,
isnull(CONVERT(CHAR(10), NDC, 101),'') NDC,
isnull(OrigLogP, '') OrigLogP,
isnull(LogP,'') LogP,
isnull(po_mode, '') po_mode,
isnull(data.agent, '') agent,
isnull(rtrim(agent),'') + isnull(ship_mode,'') agent_key,
isnull(left(agent, 2),'') agent_mstr,
isnull(left(agent, 2),'') + isnull(ship_mode,'') agent_mstr_key,
isnull(data.factory_id, '') factory_id,
isnull(data.factory, '') factory,
isnull(rtrim(data.factory), '') + isnull(ship_mode,'') as fact_key,
isnull(data.broker, '') broker,
isnull(left(broker, 3), '') broker_mstr,
isnull(template, '') ship_cal,
isnull( ship_mode, '') ship_mode,
isnull(CONVERT(CHAR(10), brd_actual_date, 101),'') brd_actual_date,
isnull(CONVERT(CHAR(10), crd_actual_date, 101),'') crd_actual_date,
isnull(CONVERT(CHAR(10), cls_actual_date, 101),'') cls_actual_date,
isnull(CONVERT(CHAR(10), dep_actual_date, 101),'') dep_actual_date,
isnull(CONVERT(CHAR(10), arv_actual_date, 101),'') arv_actual_date,
isnull(CONVERT(CHAR(10), dlv_actual_date, 101),'') dlv_actual_date,
isnull(CONVERT(CHAR(10), rcv_actual_date, 101),'') rcv_actual_date,
isnull(CONVERT(CHAR(10), idc_actual_date, 101),'') idc_actual_date,
isnull(CONVERT(CHAR(10), bkd_actual_date, 101),'') bkd_actual_date,
isnull(CONVERT(CHAR(10), aci_actual_date, 101),'') aci_actual_date,
isnull(CONVERT(CHAR(10), dlt_actual_date, 101),'') dlt_actual_date,
isnull(CONVERT(CHAR(10), lot_actual_date, 101),'') lot_actual_date,
isnull(CONVERT(CHAR(10), dcr_actual_date, 101),'') dcr_actual_date,
isnull(CONVERT(CHAR(10), ccd_actual_date, 101),'') ccd_actual_date,
units ship_units
from
(select shipment.shipment, lot po_line, prbunhea.style, CASE ltrim(prbunhea.misc12)
WHEN '' THEN ''
ELSE ltrim(prbunhea.misc12) END OrigCRD,
CASE ltrim(prbunhea.misc4)
WHEN '' THEN ''
ELSE ltrim(prbunhea.misc4) END CRD,
ltrim(prbunhea.misc13) OrigNDC,
CASE ltrim(revdd)
WHEN '' THEN ''
ELSE revdd END NDC,
CASE ltrim(orig_shipcal)
WHEN '' THEN ''
ELSE ltrim(orig_shipcal) END OrigLogP,
CASE ltrim(prbunhea.defshipcal)
WHEN '' THEN ''
ELSE ltrim(prbunhea.defshipcal) END LogP,
CASE ltrim(prbunhea.misc14)
WHEN '' THEN ''
ELSE ltrim(prbunhea.misc14) END po_mode,
CASE ltrim(shipment.misc5)
WHEN '' THEN ''
ELSE ltrim(shipment.misc5) END ship_mode,
prbunhea.rdacode agent,
shipment.broker,
prbunhea.rfactory factory_id,
ship_to_1 factory,
sum(shipped.unitship) units
from prbunhea, shipped, shipment, shshipto where
prbunhea.lot = shipped.cut
and shipment.shipment = shipped.shipment
and shshipto.factory = prbunhea.rfactory
group by shipment.shipment, lot, prbunhea.style,prbunhea.misc12,
prbunhea.misc4, prbunhea.misc13, shipment.misc5, revdd,
orig_shipcal, prbunhea.DefShipCal, prbunhea.misc14,
prbunhea.rdacode, shipment.broker, prbunhea.rfactory, ship_to_1
) data,
(select CAST(tam.name AS CHAR(20)) AS name,
tam.id_ta,
tam.template,
brd_actual_date,
crd_actual_date,
cls_actual_date,
dep_actual_date,
arv_actual_date,
dlv_actual_date,
rcv_actual_date,
idc_actual_date,
bkd_actual_date,
aci_actual_date,
dlt_actual_date,
lot_actual_date,
dcr_actual_date,
ccd_actual_date
from
(select * from tamaster where calendarmodule = 'Shipment') as tam left outer join
(select id_ta,
max(brd_actual_date) as brd_actual_date,
max(crd_actual_date) as crd_actual_date,
max(cls_actual_date) as cls_actual_date,
max(dep_actual_date) as dep_actual_date,
max(arv_actual_date) as arv_actual_date,
max(dlv_actual_date) as dlv_actual_date,
max(rcv_actual_date) as rcv_actual_date,
max(idc_actual_date) as idc_actual_date,
max(bkd_actual_date) as bkd_actual_date,
max(aci_actual_date) as aci_actual_date,
max(dlt_actual_date) as dlt_actual_date,
max(lot_actual_date) as lot_actual_date,
max(dcr_actual_date) as dcr_actual_date,
max(ccd_actual_date) as ccd_actual_date
from
(
-- CRD start
select id_ta,
--name,
crd_actual_date=
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as brd_actual_date ,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'CRD - CARGO READY DATE'
-- CRD end
union all
-- BRD start
select id_ta,
--name,
null as crd_actual_date,
brd_actual_date=
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END ,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'BRD - BOOKING REQUEST DATE'
-- BRD end
union all
-- CLS start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
cls_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'CLS - SHIP CLOSE'
-- CLS end
union all
-- DEP start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
dep_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DEP - CONFIRMED ON BOARD'
-- DEP end
union all
-- ARV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
arv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'ARV - ACTUAL DATE OF ARRIVAL'
-- ARV end
union all
-- DLV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
dlv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DLV - CARGO RECEIVED AT DC (POD)'
-- DLV end
union all
-- RCV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
rcv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'RCV - RECEIVE DC'
-- RCV end
union all
-- IDC start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
idc_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'IDC - IN DISTRIBUTION CENTER'
-- IDC end
union all
-- BKD start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
bkd_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'BKD - BOOKING APPROVED'
-- BKD end
union all
-- ACI start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
aci_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'ACI - DATE ENTRY IS FILED WITH CUSTOMS'
-- ACI end
union all
-- DLT start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
dlt_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DLT - ARRIVAL AT ATL RAIL / CY'
-- DLT end
union all
-- LOT start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
lot_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'LOT - LOADED ON TRUCK OR TRAIN'
-- LOT end
union all
-- DCR start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end
union all
-- CCD start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
ccd_actual_date =
CASE year(act_finish)
WHEN 1900 THEN null
WHEN 1899 THEN null
ELSE act_finish
END
from tacalendar where name = 'CCD - CUSTOMS CLEARED'
-- CCD end
) as cal group by id_ta ) as cal_temp
on tam.id_ta = cal_temp.id_ta) as cal, tmp_wcc_out_products p
where cast(data.shipment as char(20)) = cast(cal.name as char(20))
and data.style = p.prodcode
order by data.shipment, data.po_line

|||

We solved the issue by making the following change. I am wondering if SS 2005 handles null values differently.

Before:

union all
-- DCR start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end

After:

union all
-- DCR start
select id_ta,
--name,
convert(datetime,'12/30/1899',101) crd_actual_date,
convert(datetime,'12/30/1899',101) brd_actual_date,
convert(datetime,'12/30/1899',101) cls_actual_date,
convert(datetime,'12/30/1899',101) dep_actual_date,
convert(datetime,'12/30/1899',101) arv_actual_date,
convert(datetime,'12/30/1899',101) dlv_actual_date,
convert(datetime,'12/30/1899',101) rcv_actual_date,
convert(datetime,'12/30/1899',101) idc_actual_date,
convert(datetime,'12/30/1899',101) bkd_actual_date,
convert(datetime,'12/30/1899',101) aci_actual_date,
convert(datetime,'12/30/1899',101) dlt_actual_date,
convert(datetime,'12/30/1899',101) lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN convert(datetime,'12/30/1899',101) WHEN 1900 THEN convert(datetime,'12/30/1899',101)
ELSE act_finish
END,
convert(datetime,'12/30/1899',101) ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end|||

I can't see where exactly the problem is by looking at the work workaround that you implemented (at least with a first glance). Is it possible to reduce the query to a small statement that shows that NULLs are treated differently between the two versions of SQL Server ? That will help us investigate the problem.

Thanks a lot,

Leo

Query multiple database tables

Sql2005? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?

If you are doing this in SSIS then look up property expressions in Books Online. You can use this to dynamically build the SELECT statement, generating the table name based on a date, or today's date - 1.

Having tables in this way seems a bit strange, certainly at such a low level of day. You may want to investigate partitioned views, or partitioned tables. These concepts present a uniform view a to something like a SELECT statement, but you can still organise the data into tables or partitions respectively.

Query List of Datetimes for same date

Hi:

I have created a table that has a column of smalldatetimes. For any given day, there may be different records with the same date but with different times. I've created the query SELECT Event FROM Events where Start=@.Start and I input start as a selected date from the calendar control. Because of the different times, I do not get any matches. Could someone help me with a query that will get all of the events on the same day?

Regards,

Roger

Yep, dealing with the fact that SQL Server date values include the times can be a pain. The solution usually involves doing one of two things, either normalizing the date values to use the default time of 12:00 (midnight, or 0 seconds from midnight) or using the BETWEEN keyword in the WHERE clause of the query.

Probably the best resource for figuring out how to proceed is Itzik Ben-Gan's series of article for SQL Server Magazine about date/time values. I'm not sure whether you have to be a subscriber to read them, but the first in the series of five isDATETIME Calculations, part 1.

One way of doing the query would be like this, using one of Itzik's techniques to compare the table's DateTimeStart field to today:

SELECT * FROM [FAHCCalendar]
where DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101')
= DATEADD(day, DATEDIFF(day, '19000101', [DateTimeStart]), '19000101')

That is only one of several techniques he uses. To try this out in a more raw form to see what is happening, try this query:

SELECT DATEADD(day, DATEDIFF(day,
'19000101', GETDATE()),
'19000101');

Otherwise you can use the T-SQL date functions to extract the day, month, and year from both dates and construct a normalized dates to compare.

Make sense?

Don

|||

Perfect - Thanks Don!

Roger