We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
Thanks
Randy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software
|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus
Showing posts with label date. Show all posts
Showing posts with label date. Show all posts
Thursday, March 29, 2012
Determine when a Service Pack was applied?
We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
ThanksRandy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus
the date the last service pack was applied?
ThanksRandy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus
Determine when a Service Pack was applied?
We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
ThanksRandy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus
the date the last service pack was applied?
ThanksRandy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus
Tuesday, March 27, 2012
Determine last access of a database or table
Does anybody know of a way to determine the last date/time a table has been accessed (query/update)?
I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table.
I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience.
Any info will help greatly!
Thanks!
Why not put a SQL Trace on each suspect database? That way you can soon determine the activity.
I like that idea. Thanks for the suggestion!
Sunday, March 25, 2012
Determine Duration between Two Dates
All
I have a table of members and I would like to determine how long each member
has been a member based upon the current date. I would like the result
retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
assistance would be appreciated.
CREATE TABLE member
(
MemberID INT,
DateJoined SMALLDATETIME
)
INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
Desired Result:
1 1999-01-11 2005-10-13 6y 9m 2d
2 2004-12-26 2005-10-13 0y 10m 18d
3 2005-01-01 2005-10-13 0y 10m 12d
ThanksHi David,
There must be a simpler way than this, but what the heck...
SELECT id, dt, y, m,
diff_d - CASE WHEN dt_ym + diff_d > today THEN 1 ELSE 0 END AS d
FROM
(
SELECT *, DATEDIFF(day, dt_ym, today) AS diff_d
FROM
(
SELECT id, dt, today, y, m,
DATEADD(month, m, dt_y) AS dt_ym
FROM
(
SELECT id, dt, today, y, dt_y,
m_diff - CASE WHEN DATEADD(month, m_diff, dt_y) > today
THEN 1 ELSE 0 END AS m
FROM
(
SELECT *, DATEDIFF(month, dt_y, today) AS m_diff
FROM
(
SELECT *, DATEADD(year, y, dt) AS dt_y
FROM
(
SELECT id, dt, today,
y_diff - CASE WHEN DATEADD(year, y_diff, dt) > today
THEN 1 ELSE 0 END AS y
FROM
(
SELECT *, DATEDIFF(year, dt, today) AS y_diff
FROM
(
SELECT MemberID AS id, DateJoined AS dt,
CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME) AS today
FROM member
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5
) AS D6
) AS D7
) AS D8;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"David" <David@.discussions.microsoft.com> wrote in message
news:44CF768B-1213-453F-8A62-024857AF5089@.microsoft.com...
> All
> I have a table of members and I would like to determine how long each
> member
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks|||Hi David
Probably you can check the link.
http://chanduas.blogspot.com/2005/0...lating-age.html
This is not the exact solution but can give u an idea
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David" wrote:
> All
> I have a table of members and I would like to determine how long each memb
er
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks
I have a table of members and I would like to determine how long each member
has been a member based upon the current date. I would like the result
retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
assistance would be appreciated.
CREATE TABLE member
(
MemberID INT,
DateJoined SMALLDATETIME
)
INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
Desired Result:
1 1999-01-11 2005-10-13 6y 9m 2d
2 2004-12-26 2005-10-13 0y 10m 18d
3 2005-01-01 2005-10-13 0y 10m 12d
ThanksHi David,
There must be a simpler way than this, but what the heck...
SELECT id, dt, y, m,
diff_d - CASE WHEN dt_ym + diff_d > today THEN 1 ELSE 0 END AS d
FROM
(
SELECT *, DATEDIFF(day, dt_ym, today) AS diff_d
FROM
(
SELECT id, dt, today, y, m,
DATEADD(month, m, dt_y) AS dt_ym
FROM
(
SELECT id, dt, today, y, dt_y,
m_diff - CASE WHEN DATEADD(month, m_diff, dt_y) > today
THEN 1 ELSE 0 END AS m
FROM
(
SELECT *, DATEDIFF(month, dt_y, today) AS m_diff
FROM
(
SELECT *, DATEADD(year, y, dt) AS dt_y
FROM
(
SELECT id, dt, today,
y_diff - CASE WHEN DATEADD(year, y_diff, dt) > today
THEN 1 ELSE 0 END AS y
FROM
(
SELECT *, DATEDIFF(year, dt, today) AS y_diff
FROM
(
SELECT MemberID AS id, DateJoined AS dt,
CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME) AS today
FROM member
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5
) AS D6
) AS D7
) AS D8;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel![url]http://www.microsoft.com/israel/sql/sqlw
/default.mspx[/url]"David" <David@.discussions.microsoft.com> wrote in message
news:44CF768B-1213-453F-8A62-024857AF5089@.microsoft.com...
> All
> I have a table of members and I would like to determine how long each
> member
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks|||Hi David
Probably you can check the link.
http://chanduas.blogspot.com/2005/0...lating-age.html
This is not the exact solution but can give u an idea
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David" wrote:
> All
> I have a table of members and I would like to determine how long each memb
er
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks
determine db last access date?
Is there a way to tell the last time a database was accessed?
You can determine the last time a user logged into the SQL server, but I
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?
|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>
|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?
|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.co...filer_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/produc...k/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
sql
You can determine the last time a user logged into the SQL server, but I
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?
|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>
|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?
|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.co...filer_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/produc...k/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
sql
determine db last access date?
Is there a way to tell the last time a database was accessed?You can determine the last time a user logged into the SQL server, but I
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>> Is there a way to tell the last time a database was accessed?
>|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>> Is there a way to tell the last time a database was accessed?
>|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
determine db last access date?
Is there a way to tell the last time a database was accessed?You can determine the last time a user logged into the SQL server, but I
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.c...ofiler_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.c...ofiler_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
determine days of the year
Hi there, is that any function in ms sql server 2000 where if i pass a date or a year then it could gives me the total days of year from the parameter?
in mySQL got select DAYOFYEAR(date);
can some one guide me on this please...i need to use it for a leap year function for my SP!There's no built-in function that will return the number of days in a year for a given date.However you can create a function something like
CREATE FUNCTION [dbo].[GetDaysInYear] ( @.pDate DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @.IsLeapYear BIT
SET @.IsLeapYear = 0
IF (YEAR( @.pDate ) % 4 = 0 AND YEAR( @.pDate ) % 100 != 0) OR
YEAR( @.pDate ) % 400 = 0
SET @.IsLeapYear = 1
RETURN 365 + @.IsLeapYear
END
GO
Then you can call your function directly in your SP like
GetDaysInYear (Your date goes here)|||CREATE FUNCTION GetDaysInYear
(
@.pDate DATETIME
)
RETURNS INT
AS
BEGIN
Declare @.days int
Declare @.firstDay varchar(15)
set @.firstDay = '1/1/' + Cast(Year(@.pDate) as varchar)
Select @.days = DATEDIFF(dd, Convert(Varchar, @.firstDay, 101), @.pDate)
RETURN @.days
END
GO
This function behaves similar to MySQL DAYOFYEAR(date) function
(only diff. is I think with 1-Jan)|||mihir, you were close but the calculation is off by 1 for all dates
:)|||Hi Thanx nick.ncs
i think i can just use your :
IF (YEAR( @.pDate ) % 4 = 0 AND YEAR( @.pDate ) % 100 != 0) OR
YEAR( @.pDate ) % 400 = 0
it wats i need after all!
thank you so much nick.ncs :eek:|||another way is to check if the 60th day of the year is in feb. see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83637|||declare @.dt datetime
set @.dt='2007-06-18'
select datediff(dd,
dateadd(yy,datediff(yy,0,@.dt),0)
,dateadd(yy,datediff(yy,0,@.dt)+1,0)
) DaysInYear|||pdreyer, that is gorgeous
wherever did you learn that?
:)|||select datepart(dy, getdate())|||mcrowley, welcome to the thread
please note your query produces day in year
the problem was days in year
i.e. either 365 or 366
:)|||but your idea was a good one :)
select datepart(dy, dateadd(dd,-1,dateadd(yy,datediff(yy,0,@.dt)+1,0)))|||Doh !|||don't beat yourself up about it, you still had a great idea, but it would only have worked for Dec 31st, so that's what i changed it slightly to do
:)
in mySQL got select DAYOFYEAR(date);
can some one guide me on this please...i need to use it for a leap year function for my SP!There's no built-in function that will return the number of days in a year for a given date.However you can create a function something like
CREATE FUNCTION [dbo].[GetDaysInYear] ( @.pDate DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @.IsLeapYear BIT
SET @.IsLeapYear = 0
IF (YEAR( @.pDate ) % 4 = 0 AND YEAR( @.pDate ) % 100 != 0) OR
YEAR( @.pDate ) % 400 = 0
SET @.IsLeapYear = 1
RETURN 365 + @.IsLeapYear
END
GO
Then you can call your function directly in your SP like
GetDaysInYear (Your date goes here)|||CREATE FUNCTION GetDaysInYear
(
@.pDate DATETIME
)
RETURNS INT
AS
BEGIN
Declare @.days int
Declare @.firstDay varchar(15)
set @.firstDay = '1/1/' + Cast(Year(@.pDate) as varchar)
Select @.days = DATEDIFF(dd, Convert(Varchar, @.firstDay, 101), @.pDate)
RETURN @.days
END
GO
This function behaves similar to MySQL DAYOFYEAR(date) function
(only diff. is I think with 1-Jan)|||mihir, you were close but the calculation is off by 1 for all dates
:)|||Hi Thanx nick.ncs
i think i can just use your :
IF (YEAR( @.pDate ) % 4 = 0 AND YEAR( @.pDate ) % 100 != 0) OR
YEAR( @.pDate ) % 400 = 0
it wats i need after all!
thank you so much nick.ncs :eek:|||another way is to check if the 60th day of the year is in feb. see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83637|||declare @.dt datetime
set @.dt='2007-06-18'
select datediff(dd,
dateadd(yy,datediff(yy,0,@.dt),0)
,dateadd(yy,datediff(yy,0,@.dt)+1,0)
) DaysInYear|||pdreyer, that is gorgeous
wherever did you learn that?
:)|||select datepart(dy, getdate())|||mcrowley, welcome to the thread
please note your query produces day in year
the problem was days in year
i.e. either 365 or 366
:)|||but your idea was a good one :)
select datepart(dy, dateadd(dd,-1,dateadd(yy,datediff(yy,0,@.dt)+1,0)))|||Doh !|||don't beat yourself up about it, you still had a great idea, but it would only have worked for Dec 31st, so that's what i changed it slightly to do
:)
Determine date when each user last accessed the database
I need to clean up some security issues. We have numerous users who access the database and I'm not confident which logins can be deleted. I've already done sp_who and sp_who2 and neither of them gave me exactly what I wanted (I know they didn't an employee who was terminated but certainly access the database numerous times before he left). I also looked at the sys.sysprocesses table. Is there another system table that can be tapped into for this?
Thanks!
It depends on whether you're looking for a change to the objects or simply an access to the data. For data, you need to set some things up to track who has looked at it recently - and that will only work if they used an application that reports who the user was, such as SQL Server Management Studio. If you have not set the system up to track data access or the application they used to get to the data doesn't report the accurate name of the user, then you won't be able to go back in time to find out what they did.
Thanks -
Buck Woody
|||One option would be to create a trigger to populate a table of user logins. This might be resource intensive if there is a lot of login activity.
Or, you can turn on login auditing for successful logins and search through the Event log.
You can use a third-party database auditing tool.
Determine Date Range Falls between other Date Range
I need to figure out how to see if a date in a date range falls between
another date range. For instance - I need to know if any date between
3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
Does anyone know of a way to code that without using a cursor to go
through every day to see if that day of the month is between the other
date range?
Melissambonsted@.yahoo.com wrote:
> I need to figure out how to see if a date in a date range falls between
> another date range. For instance - I need to know if any date between
> 3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
> Does anyone know of a way to code that without using a cursor to go
> through every day to see if that day of the month is between the other
> date range?
> Melissa
Here's some sample data and a query:
CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
INSERT INTO tbl VALUES ('20060301', '20060331');
DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
SET @.dt_from = '20030216';
SET @.dt_to = '20060401';
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from >= @.dt_from
AND dt_to <= @.dt_to ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 1 Apr 2006 14:21:55 -0800, David Portas wrote:
>mbonsted@.yahoo.com wrote:
>Here's some sample data and a query:
>CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
>CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
>INSERT INTO tbl VALUES ('20060301', '20060331');
>DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
>SET @.dt_from = '20030216';
>SET @.dt_to = '20060401';
>SELECT dt_from, dt_to
> FROM tbl
> WHERE dt_from >= @.dt_from
> AND dt_to <= @.dt_to ;
Hi David,
This will only find date ranges completely embedded in another date
range. To find any overlap, change it to
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from <= @.dt_to
AND dt_to >= @.dt_from ;
(Change <= and >= to < and > if one range starting the same day that
another range ends is not considered an overlap)
Hugo Kornelis, SQL Server MVPsql
another date range. For instance - I need to know if any date between
3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
Does anyone know of a way to code that without using a cursor to go
through every day to see if that day of the month is between the other
date range?
Melissambonsted@.yahoo.com wrote:
> I need to figure out how to see if a date in a date range falls between
> another date range. For instance - I need to know if any date between
> 3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
> Does anyone know of a way to code that without using a cursor to go
> through every day to see if that day of the month is between the other
> date range?
> Melissa
Here's some sample data and a query:
CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
INSERT INTO tbl VALUES ('20060301', '20060331');
DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
SET @.dt_from = '20030216';
SET @.dt_to = '20060401';
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from >= @.dt_from
AND dt_to <= @.dt_to ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 1 Apr 2006 14:21:55 -0800, David Portas wrote:
>mbonsted@.yahoo.com wrote:
>Here's some sample data and a query:
>CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
>CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
>INSERT INTO tbl VALUES ('20060301', '20060331');
>DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
>SET @.dt_from = '20030216';
>SET @.dt_to = '20060401';
>SELECT dt_from, dt_to
> FROM tbl
> WHERE dt_from >= @.dt_from
> AND dt_to <= @.dt_to ;
Hi David,
This will only find date ranges completely embedded in another date
range. To find any overlap, change it to
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from <= @.dt_to
AND dt_to >= @.dt_from ;
(Change <= and >= to < and > if one range starting the same day that
another range ends is not considered an overlap)
Hugo Kornelis, SQL Server MVPsql
Determine date Database was last used ?
Hi There
I am trying to establish the date a database was last used.
At first i checked out sp_helpfile and sysfiles to see if there was a date last modified, i also tried using xp_cmdshell to check the date on the actual .mdf or .ldf on the o/s but if the file has not grown the date will be the create date.
I need to establish the date a database was last used, either the last time someone logged into it or the last time any sort of command was run against the database, i cannot use sysprocesses as there are no connections to the database, so i need to determine the last time there actually were any processes run against this database.
And i need to use tsql, checking audit logs etc is not viable, can anyone help ?
Thanx
There is no easy way to get this information. The database engine doesn't keep track of these type of statistics. You could create a profiler trace that tracks the login/logout events and filters on the database column to get a list of connections. But this will not track cases where someone connects to a different database and then does a USE database to switch context. Alternatively, you could set the auto close database option. This will force the database to be closed automatically when there are no connections. When an auto-closed database is opened when a new connection tries to access it you will see a message in the SQL error log. So you could search for these messages to find out when the database was last opened. This of course requires that you retain your error logs. Note that using auto close has performance implications so you should be careful about using it. So what is the reason for trying to determine when a database was last used? What do you want to do with that information?|||i think you can make use of third pary tools. "lumigent log explorer"
if youre into auditing logins alone. maybe you should turn on the C2 auditing mechanism of sql server
|||
Hi Umachanda
I had a feeling this would be the case, it was a bit of long shot.
I have to consolidate over 200 databases for a large company, unfortunately they have not had a proper DBA, also no one really knows their sql environment, too simplify the task i thought i should first compile a list of databases still in use to be consolidated. As i have been told that some of the databases are probably no longer used, howver no one can accurately provide me with a list so i was hpoing to somehow determine databases that had not been used in say the last 3 months.
But i can see this is not going to be an easy task.
Thank You for the feedback anyway.
Cheers
|||It looks like you can go with the auto-close approach although it has some performance impact. You could try setting this for the database that you don't know for sure are used by some application. This will at least limit your scope to a subset of the 200 databases. As suggested, you can take a look at 3rd party tools also but I am not sure if they have a canned mechanism to answer these type of questions and it will probably cost a lot more to solve this problem.|||Cool Thanx UmachandarDetermine a date not in the table
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegr oups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegr oups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
Determine a date not in the table
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegroups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegroups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
Thursday, March 22, 2012
Determine a date not in the table
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegroups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegroups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
Determin start and end date of a month
Hi all,
does anyone know of a way of dynamically determining the start and end date
of a month.
i.e. if i pass 15th March as a parameter, I can determine that the start
date is 1st March (which is obvious) and the end date is 31st March -- this
is the key part!
Many thanks
ImmyImmy,
Hints:
You know what is the 1st day of the NEXT month, too.
So you date (add) minus 1 day...
HTH,
Robert.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OBf1GoFRGHA.252@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> does anyone know of a way of dynamically determining the start and end
> date of a month.
> i.e. if i pass 15th March as a parameter, I can determine that the start
> date is 1st March (which is obvious) and the end date is 31st March --
> this is the key part!
> Many thanks
> Immy
>|||Rob... Yes, this I know... but it doesnt get me around the problem.
Basically, I am creating a procedure that will add information into a table
about 'A' given day... they day could be any day and will run daily.
Thus I need to be able to determine that the current day is say... 21st Feb
and that it's end date is 28th/289th etc.. so the trick is that come the 1st
of March, it will have different end dates to Feb... and then of course leap
years... etc...
"news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
> Immy,
> Hints:
> You know what is the 1st day of the NEXT month, too.
> So you date (add) minus 1 day...
> HTH,
> Robert.
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OBf1GoFRGHA.252@.TK2MSFTNGP10.phx.gbl...
>|||A calendar table would help here and with all sorts of Date problems.
http://www.aspfaq.com/show.asp?id=2519
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Rob... Yes, this I know... but it doesnt get me around the problem.
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
> Thus I need to be able to determine that the current day is say... 21st
> Feb and that it's end date is 28th/289th etc.. so the trick is that come
> the 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
>
> "news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
> news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
>|||THATS the BAD BOY!!!
Actually - I am creating a calendar system.. hence the question. Many thanks
Immy
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:OXCVv%23FRGHA.1576@.tk2msftngp13.phx.gbl...
>A calendar table would help here and with all sorts of Date problems.
> http://www.aspfaq.com/show.asp?id=2519
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||Immy:
> Rob... Yes, this I know... but it doesnt get me around the problem.
Perhaps I'm misunderstanding.
> and that it's end date is 28th/289th etc.. so the trick is that come the
> 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
Using the technique I have already hinted at, the intricacies of the
calendar (including leap years) would all be handled by the server.
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
OK, here is an example for you:
--DROP TABLE #Test
GO
CREATE TABLE #Test (SomeDay DATETIME NOT NULL)
GO
SET DATEFORMAT YMD
INSERT #Test (SomeDay) VALUES ('2005-02-01')
INSERT #Test (SomeDay) VALUES ('2004-02-01')
INSERT #Test (SomeDay) VALUES ('2003-02-01')
INSERT #Test (SomeDay) VALUES ('2002-02-01')
INSERT #Test (SomeDay) VALUES ('2001-02-01')
INSERT #Test (SomeDay) VALUES ('2000-02-01')
INSERT #Test (SomeDay) VALUES ('1999-02-01')
INSERT #Test (SomeDay) VALUES ('1999-03-02')
INSERT #Test (SomeDay) VALUES ('1999-04-03')
INSERT #Test (SomeDay) VALUES ('1999-05-04')
INSERT #Test (SomeDay) VALUES ('1999-06-05')
INSERT #Test (SomeDay) VALUES ('1999-08-31')
GO
SET DATEFORMAT YMD
SELECT SomeDay,
LastDayOfMonthOfSomeDay = DATEADD(d, -1,
(/* This expression is the FIRST day of the FOLLOWING month */
CONVERT(DATETIME, CAST(YEAR(DATEADD(m, 1, SomeDay)) AS CHAR(4)) + '/'
+ CAST(MONTH(DATEADD(m, 1, SomeDay)) AS VARCHAR(2)) + '/1')
/**/)
)
FROM
#Test
Robert
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
> Thus I need to be able to determine that the current day is say... 21st
> Feb and that it's end date is 28th/289th etc.. so the trick is that come
> the 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
>
> "news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
> news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
>|||Glad I could help.
I'm not sure that I like being called a BAD BOY in this newsgroup though.
:-)
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OPw9wDGRGHA.1576@.tk2msftngp13.phx.gbl...
> THATS the BAD BOY!!!
> Actually - I am creating a calendar system.. hence the question. Many
> thanks
> Immy
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:OXCVv%23FRGHA.1576@.tk2msftngp13.phx.gbl...|||Was referring to the solution.. not u! ;)
Cheers
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23t5IcIGRGHA.336@.TK2MSFTNGP12.phx.gbl...
> Glad I could help.
> I'm not sure that I like being called a BAD BOY in this newsgroup though.
> :-)
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OPw9wDGRGHA.1576@.tk2msftngp13.phx.gbl...
>|||On Fri, 10 Mar 2006 16:09:19 -0000, Immy wrote:
>Rob... Yes, this I know... but it doesnt get me around the problem.
>Basically, I am creating a procedure that will add information into a table
>about 'A' given day... they day could be any day and will run daily.
>Thus I need to be able to determine that the current day is say... 21st Feb
>and that it's end date is 28th/289th etc.. so the trick is that come the 1s
t
>of March, it will have different end dates to Feb... and then of course lea
p
>years... etc...
Hi Immy,
Here's yet a third way to do this (using the test data kindly provided
by Robert):
SELECT SomeDay,
DATEADD(month,
DATEDIFF(month, '20050101', SomeDay),
'20050131') AS LastDayOfMonthOfSomeDay
FROM #Test
The trick here is to calculate the number of full months between your
date and the first month of a fixed starting point, then add that number
to the last day of the same month. You are free to choose any date
within the range of allowed datetimes as starting point, but make sure
to pick a month with 31 days!
Hugo Kornelis, SQL Server MVP
does anyone know of a way of dynamically determining the start and end date
of a month.
i.e. if i pass 15th March as a parameter, I can determine that the start
date is 1st March (which is obvious) and the end date is 31st March -- this
is the key part!
Many thanks
ImmyImmy,
Hints:
You know what is the 1st day of the NEXT month, too.
So you date (add) minus 1 day...
HTH,
Robert.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OBf1GoFRGHA.252@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> does anyone know of a way of dynamically determining the start and end
> date of a month.
> i.e. if i pass 15th March as a parameter, I can determine that the start
> date is 1st March (which is obvious) and the end date is 31st March --
> this is the key part!
> Many thanks
> Immy
>|||Rob... Yes, this I know... but it doesnt get me around the problem.
Basically, I am creating a procedure that will add information into a table
about 'A' given day... they day could be any day and will run daily.
Thus I need to be able to determine that the current day is say... 21st Feb
and that it's end date is 28th/289th etc.. so the trick is that come the 1st
of March, it will have different end dates to Feb... and then of course leap
years... etc...
"news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
> Immy,
> Hints:
> You know what is the 1st day of the NEXT month, too.
> So you date (add) minus 1 day...
> HTH,
> Robert.
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OBf1GoFRGHA.252@.TK2MSFTNGP10.phx.gbl...
>|||A calendar table would help here and with all sorts of Date problems.
http://www.aspfaq.com/show.asp?id=2519
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Rob... Yes, this I know... but it doesnt get me around the problem.
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
> Thus I need to be able to determine that the current day is say... 21st
> Feb and that it's end date is 28th/289th etc.. so the trick is that come
> the 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
>
> "news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
> news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
>|||THATS the BAD BOY!!!
Actually - I am creating a calendar system.. hence the question. Many thanks
Immy
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:OXCVv%23FRGHA.1576@.tk2msftngp13.phx.gbl...
>A calendar table would help here and with all sorts of Date problems.
> http://www.aspfaq.com/show.asp?id=2519
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||Immy:
> Rob... Yes, this I know... but it doesnt get me around the problem.
Perhaps I'm misunderstanding.
> and that it's end date is 28th/289th etc.. so the trick is that come the
> 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
Using the technique I have already hinted at, the intricacies of the
calendar (including leap years) would all be handled by the server.
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
OK, here is an example for you:
--DROP TABLE #Test
GO
CREATE TABLE #Test (SomeDay DATETIME NOT NULL)
GO
SET DATEFORMAT YMD
INSERT #Test (SomeDay) VALUES ('2005-02-01')
INSERT #Test (SomeDay) VALUES ('2004-02-01')
INSERT #Test (SomeDay) VALUES ('2003-02-01')
INSERT #Test (SomeDay) VALUES ('2002-02-01')
INSERT #Test (SomeDay) VALUES ('2001-02-01')
INSERT #Test (SomeDay) VALUES ('2000-02-01')
INSERT #Test (SomeDay) VALUES ('1999-02-01')
INSERT #Test (SomeDay) VALUES ('1999-03-02')
INSERT #Test (SomeDay) VALUES ('1999-04-03')
INSERT #Test (SomeDay) VALUES ('1999-05-04')
INSERT #Test (SomeDay) VALUES ('1999-06-05')
INSERT #Test (SomeDay) VALUES ('1999-08-31')
GO
SET DATEFORMAT YMD
SELECT SomeDay,
LastDayOfMonthOfSomeDay = DATEADD(d, -1,
(/* This expression is the FIRST day of the FOLLOWING month */
CONVERT(DATETIME, CAST(YEAR(DATEADD(m, 1, SomeDay)) AS CHAR(4)) + '/'
+ CAST(MONTH(DATEADD(m, 1, SomeDay)) AS VARCHAR(2)) + '/1')
/**/)
)
FROM
#Test
Robert
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
> Thus I need to be able to determine that the current day is say... 21st
> Feb and that it's end date is 28th/289th etc.. so the trick is that come
> the 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
>
> "news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
> news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
>|||Glad I could help.
I'm not sure that I like being called a BAD BOY in this newsgroup though.
:-)
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OPw9wDGRGHA.1576@.tk2msftngp13.phx.gbl...
> THATS the BAD BOY!!!
> Actually - I am creating a calendar system.. hence the question. Many
> thanks
> Immy
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:OXCVv%23FRGHA.1576@.tk2msftngp13.phx.gbl...|||Was referring to the solution.. not u! ;)
Cheers
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23t5IcIGRGHA.336@.TK2MSFTNGP12.phx.gbl...
> Glad I could help.
> I'm not sure that I like being called a BAD BOY in this newsgroup though.
> :-)
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OPw9wDGRGHA.1576@.tk2msftngp13.phx.gbl...
>|||On Fri, 10 Mar 2006 16:09:19 -0000, Immy wrote:
>Rob... Yes, this I know... but it doesnt get me around the problem.
>Basically, I am creating a procedure that will add information into a table
>about 'A' given day... they day could be any day and will run daily.
>Thus I need to be able to determine that the current day is say... 21st Feb
>and that it's end date is 28th/289th etc.. so the trick is that come the 1s
t
>of March, it will have different end dates to Feb... and then of course lea
p
>years... etc...
Hi Immy,
Here's yet a third way to do this (using the test data kindly provided
by Robert):
SELECT SomeDay,
DATEADD(month,
DATEDIFF(month, '20050101', SomeDay),
'20050131') AS LastDayOfMonthOfSomeDay
FROM #Test
The trick here is to calculate the number of full months between your
date and the first month of a fixed starting point, then add that number
to the last day of the same month. You are free to choose any date
within the range of allowed datetimes as starting point, but make sure
to pick a month with 31 days!
Hugo Kornelis, SQL Server MVP
Detecting the INSERT format for DATE database fields?
Assume I got a database resp. table defintion with a DATE field. How do I fi
nd out in which format the
field should be filled in an INSERT SQL statement?
Sometimes when I enter e.g.
INSERT INTO ... VALUES (...., '2005-01-25',....);
or
INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
I got an error like:
ORA-01861: literal does not match format string
before I realized that
INSERT INTO ... VALUES (...., '25-01-2005',...);
is the correct format.
TomThomas Jerkins (tomjerk@.hotmail.com) wrote:
: Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
: field should be filled in an INSERT SQL statement?
: Sometimes when I enter e.g.
: INSERT INTO ... VALUES (...., '2005-01-25',....);
: or
: INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
: I got an error like:
: ORA-01861: literal does not match format string
: before I realized that
: INSERT INTO ... VALUES (...., '25-01-2005',...);
: is the correct format.
You asked in an oracle group, so I'll give the oracle answer.
INSERT INTO ...
VALUES (...., to_date('25-01-2005','DD-MM-YYYY') ,...);
look up to_date for all the options.
This space not for rent.|||Thomas Jerkins wrote:
> Assume I got a database resp. table defintion with a DATE field. How
> do I find out in which format the field should be filled in an INSERT
> SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
The only guaranteed formats inserting a date are the following:
select convert(datetime, 'YYYY-MM-DDThh:mm:ss.mmm')
select convert(datetime, 'YYYYMMDD hh:mm:ss.mmm')
All others are subject the locale and risk throwing exceptions or
inserting the incorrect date.
David Gugick
Imceda Software
www.imceda.com|||"Thomas Jerkins" <tomjerk@.hotmail.com> a crit dans le message de
news:cst3bg$osr$05$1@.news.t-online.com...
| Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format
the
| field should be filled in an INSERT SQL statement?
|
| Sometimes when I enter e.g.
|
| INSERT INTO ... VALUES (...., '2005-01-25',....);
| or
| INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
|
| I got an error like:
|
| ORA-01861: literal does not match format string
|
| before I realized that
|
| INSERT INTO ... VALUES (...., '25-01-2005',...);
|
| is the correct format.
|
| Tom
|
Never rely on implicit conversion.
Use to_date to explicitly convert your external format to date datatype:
insert into ... values (..., to_date('2005-01-25','YYYY-MM-DD'),...);
or
insert into ... values (..., to_date('2005-01-25-13.14.15.00','YYYY-MM-DD-HH
24.MI.SS.FF2'),...);
or whatever is your external format.
This is the correct way.
Regards
Michel Cadot|||On Sat, 22 Jan 2005 09:37:04 +0100, tomjerk@.hotmail.com (Thomas
Jerkins) wrote:
>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
>Sometimes when I enter e.g.
>INSERT INTO ... VALUES (...., '2005-01-25',....);
>or
>INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
>I got an error like:
>ORA-01861: literal does not match format string
>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
>Tom
You should never rely on the default date format, and always apply the
to_date function to any date literal submitted.
The default date format, btw, is in the docs (no one ever reads them)
and can be queried from nls_session_parameters.
But you should stop developing bad habits and not rely on it. It can
be changed on client level very easily, in which case your application
won't work anymore.
Sybrand Bakker, Senior Oracle DBA|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
Please don't cross post Oracle problems in other newsgroups. But if you were
using DB2, it would take the date in either order.|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
> find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
you can check the setting of NLS_DATE_FORMAT in the V$NLS_PARAMETERS view
however, you should not rely on the default format because it can be changed
by the dba or by other code that you run
1) if using 10g, use the DATE keyword to specify an ansi date literal, ie
DATE '2005-02-22' (no time element)
2) for full oracle portability and reliability either use TO_CHAR with a
specific date format, ie, TO_CHAR('022205 15:22', 'RRMMDD HH24:MI')
3) or, if you could explicitly set the format for your session with ALTER
SESSION SET NLS_DATE_FORMAT = 'the format of your choice'
check the SQL manual at tahiti.oracle.com for addtional date formats
and _please_ ignore any examples that you ever see, including in Oracle docs
and courseware and OCP stuff, that assume a specific date format without
using one of these three techniques|||What is Oracle?|||On 22 Jan 2005 10:03:57 -0800, Max wrote:
>What is Oracle?
http://en.wikipedia.org/wiki/Oracle
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sat, 22 Jan 2005 09:37:04 +0100, Thomas Jerkins wrote:
>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
Hi Tom,
Most of the answers you got relate to Oracle. The error code indicates
that that's what you want. But since you posted in a SQL Server newsgroup,
the correct answer for SQL Server is:
a) Use one of the unambiguous formats:
- yyyymmdd for date only
- yyyy-mm-ddThh:mm:ss for date plus time
- yyyy-mm-ddThh:mm:ss.ttt for date plus time including milliseconds
b) Use explicit conversion using a style parameter (look up CONVERT and
CAST in Books Online for a list of all supported styles)
c) Use any format that matches your regional settings and pray that nobody
changes the regional setting and that you enver have to export your code
to other countries.
Actually, I recommend using only one of the first two options. Option a
takes the least number of keystrokes.
>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
Not for SQL Server, it isn't. Even though it won't fail for all settings,
it will fail for some.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
nd out in which format the
field should be filled in an INSERT SQL statement?
Sometimes when I enter e.g.
INSERT INTO ... VALUES (...., '2005-01-25',....);
or
INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
I got an error like:
ORA-01861: literal does not match format string
before I realized that
INSERT INTO ... VALUES (...., '25-01-2005',...);
is the correct format.
TomThomas Jerkins (tomjerk@.hotmail.com) wrote:
: Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
: field should be filled in an INSERT SQL statement?
: Sometimes when I enter e.g.
: INSERT INTO ... VALUES (...., '2005-01-25',....);
: or
: INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
: I got an error like:
: ORA-01861: literal does not match format string
: before I realized that
: INSERT INTO ... VALUES (...., '25-01-2005',...);
: is the correct format.
You asked in an oracle group, so I'll give the oracle answer.
INSERT INTO ...
VALUES (...., to_date('25-01-2005','DD-MM-YYYY') ,...);
look up to_date for all the options.
This space not for rent.|||Thomas Jerkins wrote:
> Assume I got a database resp. table defintion with a DATE field. How
> do I find out in which format the field should be filled in an INSERT
> SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
The only guaranteed formats inserting a date are the following:
select convert(datetime, 'YYYY-MM-DDThh:mm:ss.mmm')
select convert(datetime, 'YYYYMMDD hh:mm:ss.mmm')
All others are subject the locale and risk throwing exceptions or
inserting the incorrect date.
David Gugick
Imceda Software
www.imceda.com|||"Thomas Jerkins" <tomjerk@.hotmail.com> a crit dans le message de
news:cst3bg$osr$05$1@.news.t-online.com...
| Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format
the
| field should be filled in an INSERT SQL statement?
|
| Sometimes when I enter e.g.
|
| INSERT INTO ... VALUES (...., '2005-01-25',....);
| or
| INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
|
| I got an error like:
|
| ORA-01861: literal does not match format string
|
| before I realized that
|
| INSERT INTO ... VALUES (...., '25-01-2005',...);
|
| is the correct format.
|
| Tom
|
Never rely on implicit conversion.
Use to_date to explicitly convert your external format to date datatype:
insert into ... values (..., to_date('2005-01-25','YYYY-MM-DD'),...);
or
insert into ... values (..., to_date('2005-01-25-13.14.15.00','YYYY-MM-DD-HH
24.MI.SS.FF2'),...);
or whatever is your external format.
This is the correct way.
Regards
Michel Cadot|||On Sat, 22 Jan 2005 09:37:04 +0100, tomjerk@.hotmail.com (Thomas
Jerkins) wrote:
>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
>Sometimes when I enter e.g.
>INSERT INTO ... VALUES (...., '2005-01-25',....);
>or
>INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
>I got an error like:
>ORA-01861: literal does not match format string
>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
>Tom
You should never rely on the default date format, and always apply the
to_date function to any date literal submitted.
The default date format, btw, is in the docs (no one ever reads them)
and can be queried from nls_session_parameters.
But you should stop developing bad habits and not rely on it. It can
be changed on client level very easily, in which case your application
won't work anymore.
Sybrand Bakker, Senior Oracle DBA|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
Please don't cross post Oracle problems in other newsgroups. But if you were
using DB2, it would take the date in either order.|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
> find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
you can check the setting of NLS_DATE_FORMAT in the V$NLS_PARAMETERS view
however, you should not rely on the default format because it can be changed
by the dba or by other code that you run
1) if using 10g, use the DATE keyword to specify an ansi date literal, ie
DATE '2005-02-22' (no time element)
2) for full oracle portability and reliability either use TO_CHAR with a
specific date format, ie, TO_CHAR('022205 15:22', 'RRMMDD HH24:MI')
3) or, if you could explicitly set the format for your session with ALTER
SESSION SET NLS_DATE_FORMAT = 'the format of your choice'
check the SQL manual at tahiti.oracle.com for addtional date formats
and _please_ ignore any examples that you ever see, including in Oracle docs
and courseware and OCP stuff, that assume a specific date format without
using one of these three techniques|||What is Oracle?|||On 22 Jan 2005 10:03:57 -0800, Max wrote:
>What is Oracle?
http://en.wikipedia.org/wiki/Oracle
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sat, 22 Jan 2005 09:37:04 +0100, Thomas Jerkins wrote:
>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
Hi Tom,
Most of the answers you got relate to Oracle. The error code indicates
that that's what you want. But since you posted in a SQL Server newsgroup,
the correct answer for SQL Server is:
a) Use one of the unambiguous formats:
- yyyymmdd for date only
- yyyy-mm-ddThh:mm:ss for date plus time
- yyyy-mm-ddThh:mm:ss.ttt for date plus time including milliseconds
b) Use explicit conversion using a style parameter (look up CONVERT and
CAST in Books Online for a list of all supported styles)
c) Use any format that matches your regional settings and pray that nobody
changes the regional setting and that you enver have to export your code
to other countries.
Actually, I recommend using only one of the first two options. Option a
takes the least number of keystrokes.
>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
Not for SQL Server, it isn't. Even though it won't fail for all settings,
it will fail for some.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Wednesday, March 21, 2012
Detecting currently used hierarchy
Hi,
I want to write a mdx for calculated field in SSAS 2005. This formula will use PERIODSTODATE function. But my date dimension has two hierarchies and I want to cover two of them in the same formula. Is there a way to detect which hierarchy is used. (Time dimension can be placed on filter axis, column axis or row axis)
Thanks in advance.
Nilgun Celikok
Sorry, this can't be done. You would need to create a calculation for each hierarchy.
The reason is that technically both hierarchies always have a concept of a current member. Even when they are not explicitly mentioned in a query, they always have a context. You can see this visually by creating a query with one hierarchy on the columns and one on the rows. In this situation SSAS wound have no way of figuring out which is the "current" hierarchy.
There is a small advantage in that this makes your measures unabiguous. There is less chance of users reporting conflicting results if you have a "Financial YTD" measure and a "Calendar YTD" measure. If you just had a "YTD" measure and your users are anything like mine, then there will be atleast one person who will pick a date without really thinking about which hierarchy they are using.
|||Hi,
I beleive that this can be done using the SCOPE statement, and redefining the calculation for the different heirarchies.
I've defined this calc to only work at the Month or Week level of the time dimension, all other levels should return a blank as the calculation would not make sense.
Here is my example.
Not sure what the result is when you combine the dimensions, something to test!
CREATE MEMBER CURRENTCUBE.[Comparatives].[Same Time LY]
AS Null,
FORMAT_STRING = "#,#",
VISIBLE = 1;
SCOPE(EXCEPT(Measures.Members, {[Measures].[CustCount], [Measures].[SKUCount]}));
SCOPE([Time].[by Month].[Month].Members);
([Comparatives].[Same Time LY]=([Time].[by Month].CurrentMember.Lag(12), [Comparatives].&[1]));
END SCOPE;
SCOPE([Time].[by Week].[Week].Members);
([Comparatives].[Same Time LY]=([Time].[by Week].CurrentMember.Lag(12), [Comparatives].&[1]));
END SCOPE;
END SCOPE;
|||It depends what Nilgun meant by "different Hierarchies". I am assuming that because he mentions using PeriodsToDate() he has a configuration with 2 multi-level hierarchies like a calendar and financial hierarchy. This would mean that a given date would have a different set of ancestors depending on which hierarchy we were talking about.
Your example of calculating attribute members differently using scope statements would work if this was the situation Nilgun was attempting to describe.
PS. If you put weeks and months on the rows and columns, my guess is that the week calculation would win as it is last in the calc script, but in the normal course of things there is not direct relationship, so picking a month does not imply setting an particular week. Where as with the financial, calendar hierarchy example, selecting "August 2007" makes it the current member in both hierarchies as there is only the one month member.
|||I have one DATE dimension that has two hierachies.
First one called HDATE1 has following levels
.Year
..Month
...Day
Second one called HDATE2 has following levels
.Year
..Week
...Day
I want to detect if HDATE1 or HDATE2 is used.|||If this is your situation then Bernaud's approach would work. I was concerned that you had different year attributes.
For the Days, it does not matter which hierarchy you use to do the periodsToDate() calc. YTD at August 30 is the same whether you use the week or month. Then you just need different scopes for the week and month attributes, similar to Bernaud's example, but using the PeriodsToDate() function instead of a lag.
Detecting currently used hierarchy
Hi,
I want to write a mdx for calculated field in SSAS 2005. This formula will use PERIODSTODATE function. But my date dimension has two hierarchies and I want to cover two of them in the same formula. Is there a way to detect which hierarchy is used. (Time dimension can be placed on filter axis, column axis or row axis)
Thanks in advance.
Nilgun Celikok
Sorry, this can't be done. You would need to create a calculation for each hierarchy.
The reason is that technically both hierarchies always have a concept of a current member. Even when they are not explicitly mentioned in a query, they always have a context. You can see this visually by creating a query with one hierarchy on the columns and one on the rows. In this situation SSAS wound have no way of figuring out which is the "current" hierarchy.
There is a small advantage in that this makes your measures unabiguous. There is less chance of users reporting conflicting results if you have a "Financial YTD" measure and a "Calendar YTD" measure. If you just had a "YTD" measure and your users are anything like mine, then there will be atleast one person who will pick a date without really thinking about which hierarchy they are using.
|||Hi,
I beleive that this can be done using the SCOPE statement, and redefining the calculation for the different heirarchies.
I've defined this calc to only work at the Month or Week level of the time dimension, all other levels should return a blank as the calculation would not make sense.
Here is my example.
Not sure what the result is when you combine the dimensions, something to test!
CREATE MEMBER CURRENTCUBE.[Comparatives].[Same Time LY]
AS Null,
FORMAT_STRING = "#,#",
VISIBLE = 1;
SCOPE(EXCEPT(Measures.Members, {[Measures].[CustCount], [Measures].[SKUCount]}));
SCOPE([Time].[by Month].[Month].Members);
([Comparatives].[Same Time LY]=([Time].[by Month].CurrentMember.Lag(12), [Comparatives].&[1]));
END SCOPE;
SCOPE([Time].[by Week].[Week].Members);
([Comparatives].[Same Time LY]=([Time].[by Week].CurrentMember.Lag(12), [Comparatives].&[1]));
END SCOPE;
END SCOPE;
|||It depends what Nilgun meant by "different Hierarchies". I am assuming that because he mentions using PeriodsToDate() he has a configuration with 2 multi-level hierarchies like a calendar and financial hierarchy. This would mean that a given date would have a different set of ancestors depending on which hierarchy we were talking about.
Your example of calculating attribute members differently using scope statements would work if this was the situation Nilgun was attempting to describe.
PS. If you put weeks and months on the rows and columns, my guess is that the week calculation would win as it is last in the calc script, but in the normal course of things there is not direct relationship, so picking a month does not imply setting an particular week. Where as with the financial, calendar hierarchy example, selecting "August 2007" makes it the current member in both hierarchies as there is only the one month member.
|||I have one DATE dimension that has two hierachies.
First one called HDATE1 has following levels
.Year
..Month
...Day
Second one called HDATE2 has following levels
.Year
..Week
...Day
I want to detect if HDATE1 or HDATE2 is used.|||If this is your situation then Bernaud's approach would work. I was concerned that you had different year attributes.
For the Days, it does not matter which hierarchy you use to do the periodsToDate() calc. YTD at August 30 is the same whether you use the week or month. Then you just need different scopes for the week and month attributes, similar to Bernaud's example, but using the PeriodsToDate() function instead of a lag.
Detecting bad date with YYYYMMDD format
Every night we get a big dump of data from one of our outside financial
companies. In the file, one of the columns is a date, with the
following format:
YYYYMMDD
When we migrate this data into our database, SQL Server will
automaically cast this 8-character "varchar" field to a smalldatetime
field. This means that I can do this:
UPDATE table
SET datefield = tb.datefield
FROM loadedfile tb
However, I recently discovered that they sent us a few bad dates
(20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value."
So, my question is -- how do I go about detecting that the 8-character
string is not a valid date, while doing it on-the-fly inside the UPDATE
statement?Do your import into a seperate table with generic data types. Add a column
to the head of the table that indicates the attempted date of import. Add
another column that will indicate the type of error that you are having
with the column.
Now, do the import to this table and update column1 with getdate and column2
with NULL.
Next, run a routine that will validate all the data and update column2 from
null to something that indicates error.
Finally, do your normal import from this new table only where column2 is
null. Now you have a history of all the issues.
When everything is complete, if there is a count(*) > 0 in this table,
meaning that there were errors, you can email someone or just toss red
flags how ever you do this.
You can also check count(*) to see if > 0 and if true then you might not
want to do any of the import process and throw your flags.
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
new|||You can use the ISDATE() function, which returns 1 if valid and 0 if not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jsevlie@.gmail.com> wrote in message news:1132076310.299318.326260@.g43g2000cwa.googlegroups
.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||You can use a WHERE clause to limit the rows processed, or use CASE within
the statement.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
<jsevlie@.gmail.com> wrote in message
news:1132076310.299318.326260@.g43g2000cwa.googlegroups.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Try this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT ISDATE(@.Date)
It returns "1" (which is obviously not right!)
Howver, do this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT CAST(@.date AS smalldatetime)
And it throws the error I'm getting.
Then, I did this:
declare @.dyear varchar(10)
declare @.dmonth varchar(10)
declare @.dday varchar(10)
declare @.newdate varchar(10)
IF ISDATE(@.Date) = 1 BEGIN
set @.dyear = substring(@.Date, 1, 4)
set @.dmonth = substring(@.Date, 5, 2)
set @.dday = substring(@.Date, 7, 2)
set @.newdate = @.dyear + '-' + @.dmonth + '-' + @.dday
select @.Date, @.newdate, isdate(@.newdate)
END
And it still thinks my string is a valid date. What gives?|||BOL shows a SmallDateTime as:
Date and time data from January 1, 1900, through June 6, 2079, with accuracy
to the minute.
A regular DateTime field will handled dates between the years 1753 and 9999.
So the first option is to store the data as a DateTime instead of a
SmallDateTime, and CAST the field to DateTime:
UPDATE table
SET datefield = CAST(tb.datefield AS DATETIME)
FROM loadedfile tb
If the field in table is SmallDateTime and you cannot change it, then you
will have to throw in some sort of business logic - what happens if the date
is > 2079? If it is to set the date to be entered as 1/1/2079, then you
could do something like this:
UPDATE table
SET datefield = CASE WHEN CAST(db.datefield AS DATETIME) > '1/1/2079'
THEN '1/1/2079'
ELSE CAST(tb.datefield AS SMALLDATETIME)
END
FROM loadedfile tb
This will check to see if the data passed in is too big, and if it is pass
in the date of '1/1/2079'. You could then query the table for anything with
the '1/1/2079' date and know it was something that was wrong - and follow up
to fix it.
Hope this helps some
Rob|||-- check for the smalldatetime limits
update table
set datefield = tb.datefield
from loadedfile tb
where tb.datefield>='19000101' and tb.datefield<='20790606'
-- report on those that are out of smalldatetime range
-- the implicit int conversion should be OK, unless other bad data is
there as well
select <col list>
from loadedfile
where datefield<'19000101' or datefield>'20790606'
you may want to change the datatype of the loadedfile table column to
datetime [not smalldatetime, to avoid errors] instead of varchar, then
you'd be comparing dates to dates.
jsevlie@.gmail.com wrote:
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Very nice, I like the idea about casting to a full datetime string and
then seeing if it passes a threshold. That should work, I'll give it a
try.
Thanks!
companies. In the file, one of the columns is a date, with the
following format:
YYYYMMDD
When we migrate this data into our database, SQL Server will
automaically cast this 8-character "varchar" field to a smalldatetime
field. This means that I can do this:
UPDATE table
SET datefield = tb.datefield
FROM loadedfile tb
However, I recently discovered that they sent us a few bad dates
(20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value."
So, my question is -- how do I go about detecting that the 8-character
string is not a valid date, while doing it on-the-fly inside the UPDATE
statement?Do your import into a seperate table with generic data types. Add a column
to the head of the table that indicates the attempted date of import. Add
another column that will indicate the type of error that you are having
with the column.
Now, do the import to this table and update column1 with getdate and column2
with NULL.
Next, run a routine that will validate all the data and update column2 from
null to something that indicates error.
Finally, do your normal import from this new table only where column2 is
null. Now you have a history of all the issues.
When everything is complete, if there is a count(*) > 0 in this table,
meaning that there were errors, you can email someone or just toss red
flags how ever you do this.
You can also check count(*) to see if > 0 and if true then you might not
want to do any of the import process and throw your flags.
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
new|||You can use the ISDATE() function, which returns 1 if valid and 0 if not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jsevlie@.gmail.com> wrote in message news:1132076310.299318.326260@.g43g2000cwa.googlegroups
.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||You can use a WHERE clause to limit the rows processed, or use CASE within
the statement.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
<jsevlie@.gmail.com> wrote in message
news:1132076310.299318.326260@.g43g2000cwa.googlegroups.com...
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Try this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT ISDATE(@.Date)
It returns "1" (which is obviously not right!)
Howver, do this:
DECLARE @.Date varchar(8)
SET @.Date = '20851014'
SELECT CAST(@.date AS smalldatetime)
And it throws the error I'm getting.
Then, I did this:
declare @.dyear varchar(10)
declare @.dmonth varchar(10)
declare @.dday varchar(10)
declare @.newdate varchar(10)
IF ISDATE(@.Date) = 1 BEGIN
set @.dyear = substring(@.Date, 1, 4)
set @.dmonth = substring(@.Date, 5, 2)
set @.dday = substring(@.Date, 7, 2)
set @.newdate = @.dyear + '-' + @.dmonth + '-' + @.dday
select @.Date, @.newdate, isdate(@.newdate)
END
And it still thinks my string is a valid date. What gives?|||BOL shows a SmallDateTime as:
Date and time data from January 1, 1900, through June 6, 2079, with accuracy
to the minute.
A regular DateTime field will handled dates between the years 1753 and 9999.
So the first option is to store the data as a DateTime instead of a
SmallDateTime, and CAST the field to DateTime:
UPDATE table
SET datefield = CAST(tb.datefield AS DATETIME)
FROM loadedfile tb
If the field in table is SmallDateTime and you cannot change it, then you
will have to throw in some sort of business logic - what happens if the date
is > 2079? If it is to set the date to be entered as 1/1/2079, then you
could do something like this:
UPDATE table
SET datefield = CASE WHEN CAST(db.datefield AS DATETIME) > '1/1/2079'
THEN '1/1/2079'
ELSE CAST(tb.datefield AS SMALLDATETIME)
END
FROM loadedfile tb
This will check to see if the data passed in is too big, and if it is pass
in the date of '1/1/2079'. You could then query the table for anything with
the '1/1/2079' date and know it was something that was wrong - and follow up
to fix it.
Hope this helps some
Rob|||-- check for the smalldatetime limits
update table
set datefield = tb.datefield
from loadedfile tb
where tb.datefield>='19000101' and tb.datefield<='20790606'
-- report on those that are out of smalldatetime range
-- the implicit int conversion should be OK, unless other bad data is
there as well
select <col list>
from loadedfile
where datefield<'19000101' or datefield>'20790606'
you may want to change the datatype of the loadedfile table column to
datetime [not smalldatetime, to avoid errors] instead of varchar, then
you'd be comparing dates to dates.
jsevlie@.gmail.com wrote:
> Every night we get a big dump of data from one of our outside financial
> companies. In the file, one of the columns is a date, with the
> following format:
> YYYYMMDD
> When we migrate this data into our database, SQL Server will
> automaically cast this 8-character "varchar" field to a smalldatetime
> field. This means that I can do this:
> UPDATE table
> SET datefield = tb.datefield
> FROM loadedfile tb
> However, I recently discovered that they sent us a few bad dates
> (20851031 -- Oct 31 2085?). SQL server throws an error, saying "The
> conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value."
> So, my question is -- how do I go about detecting that the 8-character
> string is not a valid date, while doing it on-the-fly inside the UPDATE
> statement?
>|||Very nice, I like the idea about casting to a full datetime string and
then seeing if it passes a threshold. That should work, I'll give it a
try.
Thanks!
Subscribe to:
Posts (Atom)