Date Query question

I am trying to access one of our SQL student tables by the change_date field. What is the proper way to get results when looking for a change_date >= "9/25/2006".

The field data looks like this: 9/25/2006 8:15:02 AM

Thx

The answer depends on how you are accessing the database. What application and/or API are you using? And what difficulties are you encountering?|||

I'm using the the query option in SQL Server Enterprise Manager. I'm getting no results when I know the data is there.

Thx

|||

Your change_date is most likely a Date/Time data type. In which case your sql string will look like this:

change_date >= #9/25/2006#

Whenever your querying data from a table, you need to think about what type of data of the fields you want to filter you search on.

If it's a date, time, or both, your gonna wrap your data within numeric/lbs. signs (Shift-3).

ex: date >= #1/1/2000#

If it's a string, your gonna wrap your data in single quotes.

ex: string_value = 'my value'

If it's a integer, you will not need to wrap the data in anything.

ex: integer_value = 100

|||

In order to retreive data from a table, where the data contains a time component other than midnight, you will have to accomodate the time issue in your search criteria.

One of the best methods is the following:

WHERE ( Change_Date >= '2006/09/25'
AND Change_Date < '2006/09/26'

This 'brackets' the day from midnight the 25th until just before midnight the 26th. Therefore the time of day is immaterial -all times during the day will be retreived. And it will use any indexing that may be available for Change_Date.

|||

AJ,

You forgot to remove your Access Developer brain and insert the SQL Server brain this morning. (Long holidays do that to us. )

T-SQL requires single quotes around the datetime values, whereas Access uses the pound sign (#).

|||

Arnie,

Works great, thanks.

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?
I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?|||Thanks! A lot of good information on your hyperlink.

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manage
r
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on wha
t
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

Date issues

I have 2 tables the first table has master records. The secong has child
records. I need to get the latest record in the 2nd table using the
'ProgramLOIAcknowledged' date. The only problem is the record does not show
up if it is NULL. I tried to put a date in there using getdate(), but I
really just want an emply field.
SELECT MySubQuery2.ProgramPendingID, dbo.tblACRDIMProgramTemp.SysOrgSID,
dbo.tblACRDIMProgramTemp.ProgramName,
MySubQuery2.ProgramLOIAcknowledged
FROM dbo.tblACRDIMProgramTemp INNER JOIN
(SELECT tblACRDIMProgramPending.*
FROM tblACRDIMProgramPending JOIN
(SELECT
tblACRDIMProgramPending.ProgramSID, Max(case
tblACRDIMProgramPending.ProgramLOIAcknowledged When NULL Then GetDate() Else
tblACRDIMProgramPending.ProgramLOIAcknowledged End) as MyMaxDate
FROM
tblACRDIMProgramPending
GROUP BY
ProgramSID) AS MySubQuery1 ON tblACRDIMProgramPending.ProgramSID =
MySubQuery1.ProgramSID AND
tblACRDIMProgramPending.ProgramLOIAcknowledged
= MySubQuery1.MyMaxDate) MySubQuery2 ON
dbo.tblACRDIMProgramTemp.ProgramSID =
MySubQuery2.ProgramSID
WHERE (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)What about Max(ISNULL(tblACRDIMProgramPending.ProgramLOIAcknowledged,'')
HTH, Jens Smeyer.
"Fetty" <dfetrow410@.hotmail.com> schrieb im Newsbeitrag
news:u$z8Z2bQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>I have 2 tables the first table has master records. The secong has child
>records. I need to get the latest record in the 2nd table using the
>'ProgramLOIAcknowledged' date. The only problem is the record does not show
>up if it is NULL. I tried to put a date in there using getdate(), but I
>really just want an emply field.
>
> SELECT MySubQuery2.ProgramPendingID,
> dbo.tblACRDIMProgramTemp.SysOrgSID, dbo.tblACRDIMProgramTemp.ProgramName,
> MySubQuery2.ProgramLOIAcknowledged
> FROM dbo.tblACRDIMProgramTemp INNER JOIN
> (SELECT tblACRDIMProgramPending.*
> FROM tblACRDIMProgramPending JOIN
> (SELECT
> tblACRDIMProgramPending.ProgramSID, Max(case
> tblACRDIMProgramPending.ProgramLOIAcknowledged When NULL Then GetDate()
> Else tblACRDIMProgramPending.ProgramLOIAcknowledged End) as MyMaxDate
> FROM
> tblACRDIMProgramPending
> GROUP BY
> ProgramSID) AS MySubQuery1 ON tblACRDIMProgramPending.ProgramSID =
> MySubQuery1.ProgramSID AND
> tblACRDIMProgramPending.ProgramLOIAcknowledged = MySubQuery1.MyMaxDate)
> MySubQuery2 ON
> dbo.tblACRDIMProgramTemp.ProgramSID =
> MySubQuery2.ProgramSID
> WHERE (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
>|||d not work
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238jww5bQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> What about Max(ISNULL(tblACRDIMProgramPending.ProgramLOIAcknowledged,'')
> HTH, Jens Smeyer.
> "Fetty" <dfetrow410@.hotmail.com> schrieb im Newsbeitrag
> news:u$z8Z2bQFHA.3664@.TK2MSFTNGP15.phx.gbl...
>|||> The only problem is the record does not show up if it is NULL.
The row does not show up when column [?] is null. Try:
SELECT
MySubQuery2.ProgramPendingID,
dbo.tblACRDIMProgramTemp.SysOrgSID,
dbo.tblACRDIMProgramTemp.ProgramName,
MySubQuery2.ProgramLOIAcknowledged
FROM
dbo.tblACRDIMProgramTemp
INNER JOIN
(
SELECT
tblACRDIMProgramPending.*
FROM
tblACRDIMProgramPending
JOIN
(
SELECT
tblACRDIMProgramPending.ProgramSID,
Max(isnull(tblACRDIMProgramPending.ProgramLOIAcknowledged, GetDate())) as
MyMaxDate
FROM
tblACRDIMProgramPending
GROUP BY
ProgramSID
) AS MySubQuery1
ON tblACRDIMProgramPending.ProgramSID = MySubQuery1.ProgramSID
AND tblACRDIMProgramPending.ProgramLOIAcknowledged = MySubQuery1.MyMaxDate
) MySubQuery2
ON dbo.tblACRDIMProgramTemp.ProgramSID = MySubQuery2.ProgramSID
WHERE
(dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
AMB
"Fetty" wrote:

> I have 2 tables the first table has master records. The secong has child
> records. I need to get the latest record in the 2nd table using the
> 'ProgramLOIAcknowledged' date. The only problem is the record does not sho
w
> up if it is NULL. I tried to put a date in there using getdate(), but I
> really just want an emply field.
>
> SELECT MySubQuery2.ProgramPendingID, dbo.tblACRDIMProgramTemp.SysOrgSI
D,
> dbo.tblACRDIMProgramTemp.ProgramName,
> MySubQuery2.ProgramLOIAcknowledged
> FROM dbo.tblACRDIMProgramTemp INNER JOIN
> (SELECT tblACRDIMProgramPending.*
> FROM tblACRDIMProgramPending JOIN
> (SELECT
> tblACRDIMProgramPending.ProgramSID, Max(case
> tblACRDIMProgramPending.ProgramLOIAcknowledged When NULL Then GetDate() El
se
> tblACRDIMProgramPending.ProgramLOIAcknowledged End) as MyMaxDate
> FROM
> tblACRDIMProgramPending
> GROUP BY
> ProgramSID) AS MySubQuery1 ON tblACRDIMProgramPending.ProgramSID =
> MySubQuery1.ProgramSID AND
> tblACRDIMProgramPending
.ProgramLOIAcknowledged
> = MySubQuery1.MyMaxDate) MySubQuery2 ON
> dbo.tblACRDIMProgramTemp.ProgramSID =
> MySubQuery2.ProgramSID
> WHERE (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
>
>|||I tried that too
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:AD944C94-4200-400E-AA6B-BB1AAE5F91C4@.microsoft.com...
> The row does not show up when column [?] is null. Try:
> SELECT
> MySubQuery2.ProgramPendingID,
> dbo.tblACRDIMProgramTemp.SysOrgSID,
> dbo.tblACRDIMProgramTemp.ProgramName,
> MySubQuery2.ProgramLOIAcknowledged
> FROM
> dbo.tblACRDIMProgramTemp
> INNER JOIN
> (
> SELECT
> tblACRDIMProgramPending.*
> FROM
> tblACRDIMProgramPending
> JOIN
> (
> SELECT
> tblACRDIMProgramPending.ProgramSID,
> Max(isnull(tblACRDIMProgramPending.ProgramLOIAcknowledged, GetDate())) as
> MyMaxDate
> FROM
> tblACRDIMProgramPending
> GROUP BY
> ProgramSID
> ) AS MySubQuery1
> ON tblACRDIMProgramPending.ProgramSID = MySubQuery1.ProgramSID
> AND tblACRDIMProgramPending.ProgramLOIAcknowledged = MySubQuery1.MyMaxDate
> ) MySubQuery2
> ON dbo.tblACRDIMProgramTemp.ProgramSID = MySubQuery2.ProgramSID
> WHERE
> (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
>
> AMB
>
> "Fetty" wrote:
>|||Then give us real specs, and tell us what "d not work" means. Error
message? If so, what is it? Wrong results? If so, how are they wrong?
What results were you expecting?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Fetty" <dfetrow410@.hotmail.com> wrote in message
news:OoDeTlcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
>I tried that too|||On Fri, 15 Apr 2005 09:18:21 -0400, Fetty wrote:

>I have 2 tables the first table has master records. The secong has child
>records. I need to get the latest record in the 2nd table using the
>'ProgramLOIAcknowledged' date. The only problem is the record does not show
>up if it is NULL. I tried to put a date in there using getdate(), but I
>really just want an emply field.
>
>SELECT MySubQuery2.ProgramPendingID, dbo.tblACRDIMProgramTemp.SysOrgSID
,
>dbo.tblACRDIMProgramTemp.ProgramName,
> MySubQuery2.ProgramLOIAcknowledged
>FROM dbo.tblACRDIMProgramTemp INNER JOIN
> (SELECT tblACRDIMProgramPending.*
> FROM tblACRDIMProgramPending JOIN
> (SELECT
>tblACRDIMProgramPending.ProgramSID, Max(case
>tblACRDIMProgramPending.ProgramLOIAcknowledged When NULL Then GetDate() Els
e
>tblACRDIMProgramPending.ProgramLOIAcknowledged End) as MyMaxDate
> FROM
>tblACRDIMProgramPending
> GROUP BY
>ProgramSID) AS MySubQuery1 ON tblACRDIMProgramPending.ProgramSID =
>MySubQuery1.ProgramSID AND
> tblACRDIMProgramPending.
ProgramLOIAcknowledged
>= MySubQuery1.MyMaxDate) MySubQuery2 ON
> dbo.tblACRDIMProgramTemp.ProgramSID =
>MySubQuery2.ProgramSID
>WHERE (dbo.tblACRDIMProgramTemp.SysOrgSID = 2)
>
Hi Fetty,
As others already indicated, it's nigh on impoossible to help you
without knowing your tables, sample data and expected output. And it
would also help if you could reformat the SQL to be more readable, and
replace those long unintelligible table names with short and snappy
aliases.
But allow me to take a wild shot:
SELECT sub2.ProgramPendingID,
temp.SysOrgSID,
temp.ProgramName,
sub2.ProgramLOIAcknowledged
FROM dbo.tblACRDIMProgramTemp AS temp
INNER JOIN (SELECT pend.*
FROM tblACRDIMProgramPending AS pend
INNER JOIN (SELECT ProgramSID,
MAX(COALESCE(ProgramLOIAcknowledged,
getdate()) AS MyMaxDate
FROM tblACRDIMProgramPending
GROUP BY ProgramSID) AS sub1
ON pend.ProgramSID = sub1.ProgramSID
AND COALESCE(pend.ProgramLOIAcknowledged,
getdate() = sub1.MyMaxDate) AS sub2
ON temp.ProgramSID = sub2.ProgramSID
WHERE temp.SysOrgSID = 2
(untested, of course)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Date in string format has to be changed datetime format

I have date coming to one page as a string in the following format
"May 4 2005 12:00AM"

I need to query one of my tables using this date in combination of other nondate values. How can I convert this date into valid sql server datetime format before I query a database tables

Please help

Hi,
you can use Sql function CONVERT :
convert(datetime,MYPARAM,110)
|||That format will work -- depending on your locale. Therecommended date format for SQL Server is the ISO format: YYYYMMDDHH:MM:SS.MS
Note that :SS.MS can be left off, or the entire time portion can be left off and the time will default to 12:00 a.m.
Is that what you're looking for?
|||

Hi there, I have some problem here, please help.

I use Format funtion as the format is "dd-MMM-yy" and my input date is "18/09/2005". system return me 4 char month instead of 3 char. 18-Sept-05. Anyone has idea how to solve this?

The regional setting of the PC is ok. So what other setting have been left off? IM using Visual Studio .net 2003 and working on XP professional with SP1 pc. Thanks!

Date formats in SQL

I need to compare a date field in a WHERE clause and I'm finding it very
difficult to do so.
I have a date stored in tables as integers like so:
20060317
etc
I need to compare todays date (less a day) to get the data out of the
table...so my query looks like this:
WHERE
IDPERI > 200601 and ididat =
case when UPPER(datename(dw,getdate())) = 'MONDAY'
then
cast(dateadd(dd,-3,getdate()) as int)
--and ididat < 20060320
else
cast(dateadd(dd,-1,getdate()) as int)
--and ididat < 20060320
end
However, the GetDate() statements between the then and else clauses return
the following:
2006-03-17 15:08:15.663
Now I just want the date to be yyyymmdd so I can compare. I have tried just
about everything I know and I cannot get the format to be yyyymmdd.
Thx
BillyBil, CAST your integer AS CHAR(8) il will workd fine.
A +
Billy a écrit :
> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SET @.DT_CURDATE = CAST(FLOOR(CAST(getdate() AS DECIMAL(15, 8))) AS DATETIME)
And then use @.DT_CURDATE instead of getdate()
Hope that helps
"Billy" wrote:

> I need to compare a date field in a WHERE clause and I'm finding it very
> difficult to do so.
> I have a date stored in tables as integers like so:
> 20060317
> etc
> I need to compare todays date (less a day) to get the data out of the
> table...so my query looks like this:
> WHERE
> IDPERI > 200601 and ididat =
> case when UPPER(datename(dw,getdate())) = 'MONDAY'
> then
> cast(dateadd(dd,-3,getdate()) as int)
> --and ididat < 20060320
> else
> cast(dateadd(dd,-1,getdate()) as int)
> --and ididat < 20060320
> end
> However, the GetDate() statements between the then and else clauses return
> the following:
> 2006-03-17 15:08:15.663
> Now I just want the date to be yyyymmdd so I can compare. I have tried jus
t
> about everything I know and I cannot get the format to be yyyymmdd.
> Thx
> Billy|||On Mon, 20 Mar 2006 07:57:32 -0800, Billy wrote:

>I need to compare a date field in a WHERE clause and I'm finding it very
>difficult to do so.
>I have a date stored in tables as integers like so:
>20060317
Hi Billy,
Don't do this.
Change your DB. Store the date as a datetime or a smalldatetime. It'll
save you lots of headaches.
(snip)
>However, the GetDate() statements between the then and else clauses return
>the following:
>2006-03-17 15:08:15.663
>Now I just want the date to be yyyymmdd so I can compare. I have tried just
>about everything I know and I cannot get the format to be yyyymmdd.
Ugly kludge:
SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS int)
Hugo Kornelis, SQL Server MVP

Date Fields - To Null or Not to Null

I am new to MS-SQL and I am doing some testing with converting a few of our current tables from Pervasive 2000i format. The first thing I run into is problems with data fields. We have a lot of date fields in our various tables that have no date (00/00/0000) which is correct. It could mean for instance that something has not happened yet (i.e. Posting Date).

Ok here is my question: Is it better not to allow Nulls in Date fields? If so, what is the best practice to have for the default (i.e. 01/01/1901) date?

We use Crystal Reports and a RAD tool to produce our many complex reports. So this will be a very important decision.

Thanks,

FrankMy vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.

blindman|||I agree... Go NULL all the way

Originally posted by blindman
My vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.

blindman|||You have 3 options as I see it..

1) Allow Nulls
Pros: Quick
Cons: Requires 3 valued logic, not very indexable

2) Define and Document a "bogus" date as blindman called it
Pros: You actually have a value that can use an index
Cons: Documentation and logic

3) Project away the date field into a new table using a one-to-none or one-to-one constraint.
Pros: Nice and relational...
Cons: Requires an extra join.

Depending on what option you do go for, don't forget that you can setup views to help out ie: ViewValidDates (Good Dates) and ViewNonValidDates (NULLs, BogusDate etc...)|||definitely use nulls

byrmol, since when can you not index a column that has nulls?

or are you implying that the index is never used? i know i've created indexes on columns with nulls (i was on sql 7) but i never bothered to dig into the execution plan to see if the index was actually being used

the "default" date will get you into WAY more trouble than nulls

that is because the "default" date forces upon you a different "third-value logic" and therefore since you have to code for three values anyway, it is better to use nulls, since there are cases (certain sql constructions) where nulls are easier than having always to remember to add "where datefield <> 'xxxx-xx-xx'" to each and every query, which, i guarantee you, somebody (usually yourself) will forget to do

as for the third option, normalizing the date out into a separate table, just so there's no column in the main table with a null in it -- i know where you're coming from but i really don't think you get much from this, because although you can bring in the date with a join, imagine doing that for all the fields in a typical application which can go null, and suddenly you have to code twenty-seven-table-join monstrosities just to get an "original" row

rudy|||null sounds good to me....

Date field and replication

Is it true that using date fields in tables being
replicated is not recommended? If true, why?
Thanks
Emma
Emma,
I've never heard this before, and many of my articles have datetime columns.
I do have a few thoughts though...
In snapshot replication I can't see how logically there can be any issues.
In transactional, if there is a default of GetDate() which is often used for
the DateAdded field, as in common with other detfaults, it will not be
transferred to the subscriber so no issue there. Queued updating subscribers
and datetime primary keys could conceivably cause problems, but this is a
relatively obscure situation as the recommendation is not to use datetime
columns for PKs anyway.
The only real issues I can think of are to do with merge replication - if
you are using column-level conflict resolution and you have a "date-changed"
column, you'll get conflicts that you didn't necessarily anticipate. The
other issue you might find is if you are using filters based on date, and
the locale is different on the subscriber and publisher, unexpected records
may be filtered out, and a recent poster was using customided conflict
resolution to solve a similar issue.
If anyone else can add to this list I'd also be interested.
HTH,
Paul Ibison
|||are you sure you are not thinking of the timestamp column. SQL Server 7.0
had problems replicating this data type.
Timestamp columns cannot be published by Publishers running SQL Server 7.0
or to Subscribers running SQL Server 7.0.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#f66WgLFEHA.576@.TK2MSFTNGP11.phx.gbl...
> Emma,
> I've never heard this before, and many of my articles have datetime
columns.
> I do have a few thoughts though...
> In snapshot replication I can't see how logically there can be any issues.
> In transactional, if there is a default of GetDate() which is often used
for
> the DateAdded field, as in common with other detfaults, it will not be
> transferred to the subscriber so no issue there. Queued updating
subscribers
> and datetime primary keys could conceivably cause problems, but this is a
> relatively obscure situation as the recommendation is not to use datetime
> columns for PKs anyway.
> The only real issues I can think of are to do with merge replication - if
> you are using column-level conflict resolution and you have a
"date-changed"
> column, you'll get conflicts that you didn't necessarily anticipate. The
> other issue you might find is if you are using filters based on date, and
> the locale is different on the subscriber and publisher, unexpected
records
> may be filtered out, and a recent poster was using customided conflict
> resolution to solve a similar issue.
> If anyone else can add to this list I'd also be interested.
> HTH,
> Paul Ibison
>
|||Thanks all for the response. I am thinking of the date
field. There are some fields in the database set as
VARCHAR and the vendor is refusing to change them to date
claiming that they will not work with replication.
Emma

>--Original Message--
>are you sure you are not thinking of the timestamp
column. SQL Server 7.0
>had problems replicating this data type.
>Timestamp columns cannot be published by Publishers
running SQL Server 7.0
>or to Subscribers running SQL Server 7.0.
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in
message
>news:#f66WgLFEHA.576@.TK2MSFTNGP11.phx.gbl...
have datetime
>columns.
there can be any issues.
which is often used
>for
detfaults, it will not be
Queued updating
>subscribers
problems, but this is a
not to use datetime
merge replication - if
have a
>"date-changed"
necessarily anticipate. The
based on date, and
publisher, unexpected
>records
customided conflict
interested.
>
>.
>
|||the vendor's lying or confused.
"Emma" <eeemore@.hotmail.com> wrote in message
news:1592901c41659$c8659f20$a501280a@.phx.gbl...
> Thanks all for the response. I am thinking of the date
> field. There are some fields in the database set as
> VARCHAR and the vendor is refusing to change them to date
> claiming that they will not work with replication.
> Emma
>
> column. SQL Server 7.0
> running SQL Server 7.0
> message
> have datetime
> there can be any issues.
> which is often used
> detfaults, it will not be
> Queued updating
> problems, but this is a
> not to use datetime
> merge replication - if
> have a
> necessarily anticipate. The
> based on date, and
> publisher, unexpected
> customided conflict
> interested.

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like “0001-01-01”. Before I import directly my AS400 tables into Access without problems because Access read those date as “1901-0
1-01”. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value “0001-01-01” to “1901-01-01” and then alter the data t
ype back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like “0001-01-01”. I could not do modification to the AS400 source. Is there a
way to solve that problem?
Thanks.
Jean-Paul
Montreal
JP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison
|||Thanks,
but not all the record have this value. When I import with package, I could go around it. But I need to be live, so, from my SQL Server, I link into the AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it said “conversion failure
.
Regards,
Jean-Paul
|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like â'0001-01-01â'. Before I import directly my AS400 tables into Access without problems because Access read those date as â'1901-01-01â'. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value â'0001-01-01â' to â'1901-01-01â' and then alter the data type back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like â'0001-01-01â'. I could not do modification to the AS400 source. Is there a way to solve that problem?
Thanks.
Jean-Paul
MontrealJP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with
a package to a text file, there are dates like “0001-01-01”. Before I i
mport directly my AS400 tables into Access without problems because Access r
ead those date as “1901-0
1-01”. Now we have to move to a SQL server. If I try to import with SQL Pa
ckage it crash unless I first change the fields in my SQL table to varChar(1
5), do the importation, change the value “0001-01-01” to “1901-01-01
and then alter the data t
ype back to smalldatetime. I have a lot of tables with numerous date fields.
I try to link the AS400 source to my SQL Server, I still could not read tab
les that have dates like “0001-01-01”. I could not do modification to th
e AS400 source. Is there a
way to solve that problem?
Thanks.
Jean-Paul
MontrealJP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison|||Thanks,
but not all the record have this value. When I import with package, I could
go around it. But I need to be live, so, from my SQL Server, I link into the
AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it
said “conversion failure
.
Regards,
Jean-Paul|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date Comparison

I have two tables that I am needing to link by the tables date field. In one table the date field is defined as varchar(23). The time for this field is always zeros.
Example: '2005-12-27 00:00:00.000'

The other table is defined as datetime, and it does have the date and time in this field.
Example: 2005-12-27 08:00:35.000

The problem i am having is
2005-12-27 00:00:00.000 does not = 2005-12-27 08:00:35.000.

Because I will never have more than one record on the same date I would like to be able to only compare the date. Example 2005-12-27 = 2005-12-27

Since the fields are 2 different field types, this is giving me a problem. Could someone please help. I have tried everything I know to do.

What I really need is the a way to format the datetime fields date into a string such as '2005-12-27'.

Hopefully what i have written makes sense.

Thanksselect cast(convert(varchar(11),getdate()) as Datetime)

this eliminates the time stamp and sets it to 2006-05-27 00:00:00.000

This will allow you to join

Suggestion make your varchar field a date field.|||Or,Use the below code to convert date to varchar and compare with ur varchar field

select convert(varchar,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0),121)|||That worked perfect! Thanks!

Date Attribute in Dimension

My dimension table has a date field that foreign keys into my time dimension table. The relational tables look something like this:

ProductRelease table

ReleaseId int (pk)

ReleaseDate datetime (fk)

TimeDay table

DayDate datetime (pk)

YearId int

In the dimension designer, my Product Release dimension has an attribute relationship between ReleaseDate and YearId. The problem is when I process the dimension I get an error like this:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_ProductRelease, Column: ReleaseDate, Value: 1/15/2007. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Product Release of Dimension: Product Release from Database: MyWarehouse, Record: 2.

But the TimeDay table does have a record whose primary key is 1/15/2007 (actually, 2007-01-15 00:00:00.000), so I'm not sure why I'm getting the error.

What's interesting is that if I use another Time dimension table whose primary key is an integer instead of a date field, and change my ProductRelease table to fk into that using an int field, everything works fine.

I may end up using the integer pk Time table anyway (although it is nice to see the date field in the foreign key table -- more convenient when browsing the data), but why would I get this error using the date data type? Does the OLAP engine have trouble with using date foreign keys?

Thanks for any help with this.

-Larry

It can be as simple as that the other table have a different time(but the same date) as the TimeDay table.

This Date(2007-01-15 00:00:00.000 ) is not the same as 2007-01-15 23:02:02.002.

HTH

Thomas Ivarsson

|||

I'm fairly certain all the values are pure dates w/o a time, but I'll double-check to make sure.

Thanks.

date and time diffs

I'm a newbie to CR so be gentle :) I'm using ver. 10 I'm going againt 2 tables in a SQL database.

The first table has contains problem reports and the second has the remediations they are linked by OutageID. My problem is that on the Problem table there can be several reords that have the same OutageID
but with different status' such as Unavailable, Update, and Available.
There would be only one Unavailable and one Available record but could be none to any number of Update records.
I want to calculate the number of hours and minute of lost time from the Unavailable timestamp to the Available timestamp. How do I code this formula?

ThanksTake a look at DateDiff function in help.