Putting dates into varchar

I've got 2 tables :

TABLE DateSqlServer
Date as DateTime

TABLE DateDb2
Date as VarChar(26)

I run these queries :

INSERT INTO DateSqlServer (Date) VALUES (GetDate())
INSERT INTO DateDb2 SELECT Date FROM DateSqlServer

Then, when I run :

SELECT Date FROM DateDb2

I get
"march 8 2004 3:45 PM"

instead of
"2004-03-08 03:45:12:000"

How can I transfer the date as I see it in table DateSQLServer
WITHOUT doing FORMATs on the Date column ?

Why does the INSERT transform the date format ?It's an implicit conversion..you need to CONVERT it into the format you want.

Dates are stored as 8 position numerc and SQL does the conversion that way...

It's another SQL Server "feature"

In my Own Opinion (MOO) it should fail.

Can you jam a varchar in to an int? No

Try

INSERT INTO DateDb2 (you really should always list the columns here)
SELECT CONVERT(varchar(26), [Date], 120)
FROM DateSqlServer

MOO|||I wanted to avoid using list of columns

I'm loading my SQL-Server database from the linked DB2 database
with a unique distributed query (good for all tables)

for each table (Table1, Table2, Table3, ..)

INSERT INTO [SQL-Server-DB].[TableX]
SELECT *
FROM OPENQUERY ([DB2-Server],
'SELECT DB2-DB.TableX.* FROM DB2-DB.TableX')

This way I did'nt need the structure of each table
AND the datatype of each column
to load the data|||I would recommend a different approach ...
Build a repository table and store name of tables and fields in that ...
build dynamic queries ( yeah, I know that is bad) for getting the data from the db2 tables with whatever conversions you need|||oye...

Where to start...

First, open a BIG bottle of Tequila...

Next, I'm big on the dynamic code generation...saves lots of time...

But not on a nightly process.

Yo uneed to know the structures and they need to be static.

Otherwise you're looking for trouble.

And while you're looking, it'll find you.

I would set up unloads of the data on DB2...ftp/copy it to your server and bcp

You can write format card generators...gen ftps, gen bcps...everything...

But then that's it...you have to then test it all, then install it...

As static sprocs...

Don't forget error handling...

What's the whole reason for this?

Oh, and MOO|||I am using this type of proc for import from SAP where the table structure remains constant ..|||Well if it's constant, why would you need dynamic anything (except to gen the statement in the first place?)

Putting Date into SQL Server

I'm trying to put a date into a SQL Server table. The database field type is "smalldatetime". The variable dDate is type "date" and contains: 2/2/2006 (although I think Cdate actually converts it to: #2/2/2006#). When I run the following code the date in the database is always ends up being: 1/1/1900.

Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _
"VALUES (" & dDate & ")", SqlConn)
daAppts.InsertCommand = cmd
daAppts.InsertCommand.Connection = SqlConn
daAppts.InsertCommand.ExecuteNonQuery()

Any other field types work fine, it's just dates that aren't working ?? Can someone please provide a code snippet showing me what I'm doing wrong??

Dates have to be single quoted:

Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _
"VALUES ('" & dDate & "')", SqlConn)

For clarity, here is the problem part enlarged (with the added single quotes):

('" & dDate & "')

|||That worked. Thank you.|||

Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _
"VALUES (@.dDate)", SqlConn)
cmd.parameters.add("@.dDate",sqldbtype.datetime).value=dDate
daAppts.InsertCommand = cmd
daAppts.InsertCommand.Connection = SqlConn
daAppts.InsertCommand.ExecuteNonQuery()

If you must continue to use string concatenation, atleast pass sql server dDate.ToString("s") as the string format. That will prevent any ambiguity of the format you are giving it. Otherwise you might be suprised when your program dies when you start playing with cultures, and SQL Server misinterprets that date.

purpose of writing dates in this format

hi Experts,
what is the purpose writing a date in the following format:

where x.event_date >= {ts '1980-01-01 00:00:00'}
versus like this:
where x.event_date >= '1980-01-01 00:00:00'

what benifit does it add to later form of writing?
Thanks in advance.
schal.On 15.01.2007 16:34, schal wrote:

Quote:

Originally Posted by

hi Experts,
what is the purpose writing a date in the following format:
>
where x.event_date >= {ts '1980-01-01 00:00:00'}
versus like this:
where x.event_date >= '1980-01-01 00:00:00'
>
what benifit does it add to later form of writing?

It's JDBC / ODBC escape syntax for timestamps.

http://java.sun.com/j2se/1.4.2/docs...ent.html#999472
http://support.microsoft.com/?scid=...142930&x=9&y=15
The former is converted by the ODBC / JDBC driver to some DB specific
binary representation of a timestamp while the latter undergoes
conversion in the DB. I'd generally use the escape syntax as it is more
portable and not affected by session parameters that affect date formatting.

Kind regards

robert|||schal (shivaramchalla@.gmail.com) writes:

Quote:

Originally Posted by

where x.event_date >= {ts '1980-01-01 00:00:00'}
versus like this:
where x.event_date >= '1980-01-01 00:00:00'
>
what benifit does it add to later form of writing?

In addition to Robert's post, Tibor Karaszi's article on date format gives
some more information:
http://www.karaszi.com/SQLServer/info_datetime.asp.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||robert
Thank you very much for clearing that
Erland
thanks for refering me to the article
the tips are very imformative and helpful
__

Erland Sommarskog wrote:

Quote:

Originally Posted by

schal (shivaramchalla@.gmail.com) writes:

Quote:

Originally Posted by

where x.event_date >= {ts '1980-01-01 00:00:00'}
versus like this:
where x.event_date >= '1980-01-01 00:00:00'

what benifit does it add to later form of writing?

>
In addition to Robert's post, Tibor Karaszi's article on date format gives
some more information:
http://www.karaszi.com/SQLServer/info_datetime.asp.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Robert,
Thank you very much for clearing that
Erland,
thanks for refering me to the article
the tips are very imformative and helpful
__

Erland Sommarskog wrote:

Quote:

Originally Posted by

schal (shivaramchalla@.gmail.com) writes:

Quote:

Originally Posted by

where x.event_date >= {ts '1980-01-01 00:00:00'}
versus like this:
where x.event_date >= '1980-01-01 00:00:00'

what benifit does it add to later form of writing?

>
In addition to Robert's post, Tibor Karaszi's article on date format gives
some more information:
http://www.karaszi.com/SQLServer/info_datetime.asp.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

sql

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnHi John
You could use SET ROWCOUNT 100 before issuing the delete statement. i.e. to
delete in blocks of 100
DECLARE @.earlierstdate datetime
SET @.earlierstdate = getdate()-90
SET ROWCOUNT 100
delete [<table>] where date < @.earlierstdate
WHILE @.@.ROWCOUNT > 0
delete [<table>] where date < @.earlierstdate
SET ROWCOUNT 0
John
"John J. Hughes II" wrote:

> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
>|||John J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.

> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...[vbcol=seagreen]
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>|||Hi John
"John J. Hughes II" wrote:

> John,
> Guess I did not see it before, thanks. By the way the BOL says to use top
> in new development since ROWCOUNT will no longer be supported. If I
> understand correctly then it should be the following:
> delete top(100) [<table>] where data < @.earlierstdate;
>
TOP is only for SQL 2005.

> I was thinking of putting this in a JOB set to when idle Do you think th
is
> would cause too much thrashing or would it be better to put it as you do b
ut
> with a waitfor delay. It would be nice to allow it to run until the serv
er
> became busy and then exit until next idle.
> Something like:
> while(@.@.rowcount > 0 and @.@.cpu < (something)
> delete top(100) [<table>] where data < @.earlierstdate;
> FROM BOL:
> Important:
> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements i
n
> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
> INSERT, and UPDATE statements in new development work, and plan to modify
> applications that currently use it. We recommend that DELETE, INSERT, and
> UPDATE statements that currently are using SET ROWCOUNT be rewritten to us
e
> TOP.
>
How you implement it will depend on how long your quiet periods are and how
many records you are deleting. I would probably start of with a single job
but delting in batches of 5000 (say) and then see if it is an issue. If you
have a policy of only keeping n days then rather than doing that as a monthy
process I would do it more gradually (say daily or weekly).
Make sure you do this deletion before you re-index.
If you do manage to get the system onto SQL 2005 then I would look at using
table partitions.
There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't think
they will be useful to you.
HTH

> regards,
> John
>
John|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>
> TOP is only for SQL 2005.
>
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>
> John
>

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnJohn J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date >
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much. I
> was hoping for something like the above but will a limit of say 100 records
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> > getdate()-90". The problem is this really puts a load on the system.
>> I would like this to be an idle job that does not load the system as
>> much. I was hoping for something like the above but will a limit of say
>> 100 records each time, is that possible?
>> Regards,
>> John
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> >
>> getdate()-90". The problem is this really puts a load on the system.
>> I
>> would like this to be an idle job that does not load the system as much.
>> I
>> was hoping for something like the above but will a limit of say 100
>> records
>> each time, is that possible?
>> Regards,
>> John
>>|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>> John,
>> Guess I did not see it before, thanks. By the way the BOL says to use
>> top
>> in new development since ROWCOUNT will no longer be supported. If I
>> understand correctly then it should be the following:
>> delete top(100) [<table>] where data < @.earlierstdate;
> TOP is only for SQL 2005.
>> I was thinking of putting this in a JOB set to when idle Do you think
>> this
>> would cause too much thrashing or would it be better to put it as you do
>> but
>> with a waitfor delay. It would be nice to allow it to run until the
>> server
>> became busy and then exit until next idle.
>> Something like:
>> while(@.@.rowcount > 0 and @.@.cpu < (something)
>> delete top(100) [<table>] where data < @.earlierstdate;
>> FROM BOL:
>> Important:
>> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements
>> in
>> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
>> INSERT, and UPDATE statements in new development work, and plan to modify
>> applications that currently use it. We recommend that DELETE, INSERT, and
>> UPDATE statements that currently are using SET ROWCOUNT be rewritten to
>> use
>> TOP.
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>> regards,
>> John
> John
>

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:

> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this da
y.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.
Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:

> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this day.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>

Pulling Dates 14 days or more past

Does anyone know a way to get Crystal to look for dates 14 days or more past a given date? I'm using Crystal reports 9 and I've tried using the DateDiff function like so.

DateDiff("d",{?StartDate},{Command.RecDate})<=14

but I'm not getting the results I want. I also tried using it in an SQL command query in my original selection with no luck any ideas?got it resolved thank you.|||In the record selection for your Crystal Report (http://www.shelko.com) you can use a formula similar to:

{myDate} >= ({?Start Date}+14)

pulling all dates within a date range

I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:

declare @.Sdate as datetime
declare @.Edate as datetime

set @.SDate = '07/01/2006'
set @.EDate = '12/31/2006'

select dateadd(dd, count(*) - 1, @.SDate)
from [atable] v
inner join [same table] v2 on v.id < v2.id
group by v.id
having count(*) < datediff(dd, @.SDate, @.EDate)+ 2
order by count(*)

this works just fine but it is dependent on the size of the table you
pull from, and is really more or less a hack job. Can anyone help me
with this?

thanks in advanceOn 6 Jul 2006 14:14:40 -0700, rugger81 wrote:

Quote:

Originally Posted by

>I am currently working in the sql server 2000 environment and I want to
>write a function to pull all dates within a given date range. I have
>created several diferent ways to do this but I am unsatisfied with
>them. Here is what I have so far:

(snip)

Hi rugger81,

http://www.aspfaq.com/show.asp?id=2519
--
Hugo Kornelis, SQL Server MVP|||rugger81 (jgilchrist@.ots.net) writes:

Quote:

Originally Posted by

I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:
>
declare @.Sdate as datetime
declare @.Edate as datetime
>
set @.SDate = '07/01/2006'
set @.EDate = '12/31/2006'
>
select dateadd(dd, count(*) - 1, @.SDate)
from [atable] v
inner join [same table] v2 on v.id < v2.id
group by v.id
having count(*) < datediff(dd, @.SDate, @.EDate)+ 2
order by count(*)
>
this works just fine but it is dependent on the size of the table you
pull from, and is really more or less a hack job. Can anyone help me
with this?

If I understand this correctly, given the sample data you want

2006-01-07, 2006-01-08, ... 2006-12-30, 2006-12-31

The best is simply to create a table of dates. Here is a script that
create our dates table:

TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)

-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'

SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks guys, I'll do just that. The idea of creating a date table
crossed my mind before, but I like to do things dynamically. Now that
I think of it however, a date table for the any time frame I would need
would still be relatively small and would save alot of time.

Erland Sommarskog wrote:

Quote:

Originally Posted by

rugger81 (jgilchrist@.ots.net) writes:

Quote:

Originally Posted by

I am currently working in the sql server 2000 environment and I want to
write a function to pull all dates within a given date range. I have
created several diferent ways to do this but I am unsatisfied with
them. Here is what I have so far:

declare @.Sdate as datetime
declare @.Edate as datetime

set @.SDate = '07/01/2006'
set @.EDate = '12/31/2006'

select dateadd(dd, count(*) - 1, @.SDate)
from [atable] v
inner join [same table] v2 on v.id < v2.id
group by v.id
having count(*) < datediff(dd, @.SDate, @.EDate)+ 2
order by count(*)

this works just fine but it is dependent on the size of the table you
pull from, and is really more or less a hack job. Can anyone help me
with this?

>
If I understand this correctly, given the sample data you want
>
2006-01-07, 2006-01-08, ... 2006-12-30, 2006-12-31
>
The best is simply to create a table of dates. Here is a script that
create our dates table:
>
>
>
TRUNCATE TABLE dates
go
-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)
>
-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'
>
SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers
>
>
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Pull Date for Last Thursday

Hi,

I am having a problem with a report. An extract runs every Thursday and on the last day of the month. I need the report to run on-demand for the most recent Thursday (so, if they run it on Fri, Sat or Sun, they get last Thursday's value).

I can't figure out how to pull data where the completedate = last Thursday's date.

Thanks,

RC

I use a function similar to this:

Code Snippet

CREATE FUNCTION dbo.LastThursday
( @.DateIn datetime,
@.WithTime bit = 0 -- 1=Yes, 0=No
)
RETURNS datetime
AS
BEGIN
DECLARE
@.DateOut datetime,
@.NumDays int

SELECT @.NumDays = CASE datepart( dw, @.DateIn )
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
WHEN 4 THEN 6
WHEN 5 THEN 7
WHEN 6 THEN 1
WHEN 7 THEN 2
END

IF ( @.WithTime = 1 )
SET @.DateOut = dateadd( day, -@.NumDays, @.DateIn )
ELSE
SET @.DateOut = cast( convert( char(10), dateadd( day, -@.NumDays, @.DateIn ), 101 ) AS datetime )

RETURN @.DateOut

END
GO

Usage:

Code Snippet

SELECT dbo.LastThursday( getdate(), 0 )
-
2007-06-07 00:00:00.000

In your Query WHERE clause:

WHERE CompleteDate = dbo.LastThursday( getdate(), 0 )

If you use this on Thursday, this week, it will return Thursday LAST week. If you want it to return Thursday, this week, change the "WHEN 5 THEN 7" to "THEN 0"