DateTime variables

Hi all
Sorry if this is in the wrong place, please tell me if it is.
I've got a SQL 2000 database and I'm having problems selecting rows where
the value of a datetime column (with date and time) has to be less than
todays date with no time.
I can't work out how to do this. I've tried:
PrintedDate contains date and time
dDateOfVisit is todays date in dd/MM/yyyy format
SELECT DISTINCT Reference
FROM cdNotes
WHERE (StoreCode = 'CET719')
AND (ItemStatusId = 1)
AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
dDateOfVisit & "', 103)
but it doesn't return any rows.
As I can't figure this out I decided to try and remove the times from the
datetime columns in my database. And to change the triggers to only insert
dates with no times.
CREATE TRIGGER [stsDateInsert] ON dbo.cdNotes
AFTER INSERT
AS
update cdNotes set DownloadedDate = GETDATE() where [index] in
(
SELECT inserted.[index] FROM inserted
)
Im having a bad day as I cant figure out how to remove the times from the
trigger update either.
Can someone please tell me how. Thanks very much.
Kind Regards
Darren RhymerDarren,
An expression that is true when the datetime column dt is
less than today's date with no time is
dt < dateadd(day,datediff(day,0,getdate()),0)
The messy expression on the right is simply today at midnight, as
a datetime type, so it's just what you want if you need today's
date "with no time part" in a trigger or elsewhere. You might also
consider this as a DEFAULT value for the column, which might
avoid the need for a trigger.
To get the date-only part of a different value than getdate(), you
can use the same idea: dateadd(day,datediff(day,0,anyDateTime),
0).
I won't guess just what you need to type into your query, but I will
mention that converting dDateOfVisit to a datetime expression would
be done with
convert(datetime,dDateOfVisit,103)
if that column is a varchar value with format dd/mm/yyyy. What you
have below tries to convert the string
"& dDateOfVisit & "
(with the double quotes, ampersands, spaces, and the word dDateOfVisit
as part of the string). That's not a date, and you should get an error. If
you don't, then you haven't shown us the exact query you are executing.
More likely than not, you'll have better luck if you can change your
table structure so that dates are stored in datetime columns, not in
string columns.
Your query doesn't seem to have anything to do with today's date,
so to select rows where a column value is "less than today's date"
you'll need getdate() somewhere in an expression like the one at
the top of this reply, but I don't know which of the two date columns
you need to compare with today.
Steve Kass
Drew University
Darren Rhymer wrote:

>Hi all
>Sorry if this is in the wrong place, please tell me if it is.
>I've got a SQL 2000 database and I'm having problems selecting rows where
>the value of a datetime column (with date and time) has to be less than
>todays date with no time.
>I can't work out how to do this. I've tried:
>PrintedDate contains date and time
>dDateOfVisit is todays date in dd/MM/yyyy format
>SELECT DISTINCT Reference
>FROM cdNotes
>WHERE (StoreCode = 'CET719')
>AND (ItemStatusId = 1)
>AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
>dDateOfVisit & "', 103)
>but it doesn't return any rows.
>As I can't figure this out I decided to try and remove the times from the
>datetime columns in my database. And to change the triggers to only insert
>dates with no times.
>CREATE TRIGGER [stsDateInsert] ON dbo.cdNotes
>AFTER INSERT
>AS
>update cdNotes set DownloadedDate = GETDATE() where [index] in
>(
>SELECT inserted.[index] FROM inserted
> )
>Im having a bad day as I cant figure out how to remove the times from the
>trigger update either.
>Can someone please tell me how. Thanks very much.
>Kind Regards
>Darren Rhymer
>|||Darren Rhymer (darren_rhymer@.hotmail.com.no.spam) writes:
> Sorry if this is in the wrong place, please tell me if it is.
> I've got a SQL 2000 database and I'm having problems selecting rows where
> the value of a datetime column (with date and time) has to be less than
> todays date with no time.
> I can't work out how to do this. I've tried:
> PrintedDate contains date and time
> dDateOfVisit is todays date in dd/MM/yyyy format
> SELECT DISTINCT Reference
> FROM cdNotes
> WHERE (StoreCode = 'CET719')
> AND (ItemStatusId = 1)
> AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
> dDateOfVisit & "', 103)
I assume that this is a SELECT send an SQL statement from a client,
the apparence of & and # indicate so.
Never embed values directly into the SQL command, but use parameter
markers instead, and then use a parameter object. The client API will
then convert the date according to the regional settings and pass the
date as a binary value, then you will not have to use convert for the
date value on the SQL Server side.
As I don't know which client API you are using I cannot give the exact
details on how to do this.
You should also avoid wrapping columns into expressions, as this preclude
use of the any index on the column, or at least the best use of the index.
Why convert the date to format 113 is beyond me, as this format includes
the time portion as well. If you want to say:
WHERE (date porttion of PrintedDate) <= dDateOfVisit
this is better:
WHERE PrintedDate < dateadd(DAY,1, @.dDateofVisit)

> As I can't figure this out I decided to try and remove the times from the
> datetime columns in my database.
If you do not need the times that's an excellent idea!
The idiom to strip a datetime value of a its time is
convert(char(8), value, 112)
Format 112 is YYYYMMDD which has the property of always being interpreted
in the one and same way in SQL Server.
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|||Hi chaps
Thanks very much for the repsonses they've been most interesting. I must
now apoligise for me leading you somewhere in the wrong direction on the 1st
part.
1) This is where Im trying to select from the database all records with
databasedate <= specified date. I failed to say, sorry, that this is being
done in VB.NET. My sql query looks like:
sql = "SELECT distinct reference from CDNotes"
sql = sql & " WHERE StoreCode = '" & txtStoreCode.Text
sql = sql & "' AND ItemStatusId = " & ItemStatus.Printed
sql = sql & " AND PrintedDate < dateadd(DAY, 1, '" & dDateOfVisit & "')"
which returns THE CORRECT RESULTS. (Thanks Erland for spotting this)
2) This is where im trying to remove the times from the existing datetime
columns in the database.
I have
UPDATE cdNotes
SET DownloadedDate = CONVERT(char(8), DownloadedDate, 112)
which works, thanks very much.
3) I need to remove the time part from the datetime when I'm inserting
GETDATE into the column after the status has changed.
CREATE TRIGGER [stsDateChange] ON dbo.cdNotes
AFTER UPDATE
AS
update cdNotes set PrintedDate = CONVERT(char(8), GETDATE(), 112) where
[index] in
(
SELECT inserted.[index] FROM inserted JOIN deleted ON inserted.[index]
=deleted.[index]
WHERE inserted.itemStatusId <> deleted.itemStatusId
AND inserted.itemStatusId = 2
)
update cdNotes set ScannedDate = CONVERT(char(8), GETDATE(), 112) where
[index] in
(
SELECT inserted.[index] FROM inserted JOIN deleted ON inserted.[index]
=deleted.[index]
WHERE inserted.itemStatusId <> deleted.itemStatusId
AND (inserted.itemStatusId = 3 OR inserted.itemStatusId = 4)
)
This now works aswell.
Many thanks to you two, cheers
Darren Rhymer
"Darren Rhymer" wrote:

> Hi all
> Sorry if this is in the wrong place, please tell me if it is.
> I've got a SQL 2000 database and I'm having problems selecting rows where
> the value of a datetime column (with date and time) has to be less than
> todays date with no time.
> I can't work out how to do this. I've tried:
> PrintedDate contains date and time
> dDateOfVisit is todays date in dd/MM/yyyy format
> SELECT DISTINCT Reference
> FROM cdNotes
> WHERE (StoreCode = 'CET719')
> AND (ItemStatusId = 1)
> AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
> dDateOfVisit & "', 103)
> but it doesn't return any rows.
> As I can't figure this out I decided to try and remove the times from the
> datetime columns in my database. And to change the triggers to only inser
t
> dates with no times.
> CREATE TRIGGER [stsDateInsert] ON dbo.cdNotes
> AFTER INSERT
> AS
> update cdNotes set DownloadedDate = GETDATE() where [index] in
> (
> SELECT inserted.[index] FROM inserted
> )
> Im having a bad day as I cant figure out how to remove the times from the
> trigger update either.
> Can someone please tell me how. Thanks very much.
> Kind Regards
> Darren Rhymer|||The fastest way to strip date is:
DATEADD(DAY, DATEDIFF(DAY, <DateTimeGoesHere>, 0), <DateTimeGoesHere> )
<DateTimeGoesHere> can be a column name, variable, or getdate().
Both <DateTimeGoesHere> have to be the same. This formula calculates the
number of days from your datetime to 19000101. (Due to the parameter
ordering, this is a negative number.) Then, it adds the negative number
(i.e. subtracts it) from your datetime leaving only the time.
The fastest way to strip time is:
CAST(DATEDIFF(DAY,0, <DateTimeGoesHere> ) as datetime)
<DateTimeGoesHere> can be a column name, variable, or getdate().
If you want today at midnight:
CAST(DATEDIFF(DAY,0,getdate()) as datetime)
If you want tomorrow at midnight:
CAST(DATEDIFF(DAY,0,getdate())+1 as datetime)
(This can be used to include data that occurred anytime today, regardless of
time.)
When dealing with datetimes, always consider both portions. If the client
sends a date and you want anything that occurs on that date:
dtColumn >= @.MyDate and
dtColumn < CAST(DATEDIFF(DAY,0,@.MyDate)+1 as datetime)
Had the client sent the date and time, then:
dtColumn >= CAST(DATEDIFF(DAY,0,@.MyDate) as datetime) and
dtColumn < CAST(DATEDIFF(DAY,0,@.MyDate)+1 as datetime)
Other posters are correct, you want to avoid wrapping functions around a
column name in a WHERE clause if possible.
And, you should avoid converting datetime to a character string (i.e.
char(8)) and then converting it to datetime containing just a date or a time
.
It's not effecient.
Hope that helps,
Joe
"Darren Rhymer" wrote:

> Hi all
> Sorry if this is in the wrong place, please tell me if it is.
> I've got a SQL 2000 database and I'm having problems selecting rows where
> the value of a datetime column (with date and time) has to be less than
> todays date with no time.
> I can't work out how to do this. I've tried:
> PrintedDate contains date and time
> dDateOfVisit is todays date in dd/MM/yyyy format
> SELECT DISTINCT Reference
> FROM cdNotes
> WHERE (StoreCode = 'CET719')
> AND (ItemStatusId = 1)
> AND convert(varchar, PrintedDate, 113) <= convert(datetime, '" &
> dDateOfVisit & "', 103)
> but it doesn't return any rows.
> As I can't figure this out I decided to try and remove the times from the
> datetime columns in my database. And to change the triggers to only inser
t
> dates with no times.
> CREATE TRIGGER [stsDateInsert] ON dbo.cdNotes
> AFTER INSERT
> AS
> update cdNotes set DownloadedDate = GETDATE() where [index] in
> (
> SELECT inserted.[index] FROM inserted
> )
> Im having a bad day as I cant figure out how to remove the times from the
> trigger update either.
> Can someone please tell me how. Thanks very much.
> Kind Regards
> Darren Rhymer|||P.S. Seeing we're talking about dates and times...
DateTime of 23:59:59.997 is the highest stored time value.
23:59:59.998 is rounded down to .997.
23:59:59.999 is rounded up to the next day.
Just be careful if you concatenate a datetime range such as
>= 00:00:00.000 and <= 23:59:59.999 as the .999 will round the datetime up to the ne
xt day at midnight.|||Joe
Thanks very much for you reply. Makes interesting reading.
At the moment one of my triggers to update the scanned date column with
todays date looks like:
update cdNotes set ScannedDate = CONVERT(char(8), GETDATE(), 112) where
[index] in
(
SELECT inserted.[index] FROM inserted JOIN deleted ON inserted.[index]
=deleted.[index]
WHERE inserted.itemStatusId <> deleted.itemStatusId
AND (inserted.itemStatusId = 3 OR inserted.itemStatusId = 4)
)
Are you saying I should somehow replace the
CONVERT(char(8), GETDATE(), 112)
with
DATEDIFF(DAY,0, GETDATE()
Thanks again
Darren
Kind Regards
Darren Rhymer
"Joe from WI" wrote:
> The fastest way to strip date is:
> DATEADD(DAY, DATEDIFF(DAY, <DateTimeGoesHere>, 0), <DateTimeGoesHere> )
> <DateTimeGoesHere> can be a column name, variable, or getdate().
> Both <DateTimeGoesHere> have to be the same. This formula calculates the
> number of days from your datetime to 19000101. (Due to the parameter
> ordering, this is a negative number.) Then, it adds the negative number
> (i.e. subtracts it) from your datetime leaving only the time.
> The fastest way to strip time is:
> CAST(DATEDIFF(DAY,0, <DateTimeGoesHere> ) as datetime)
> <DateTimeGoesHere> can be a column name, variable, or getdate().
> If you want today at midnight:
> CAST(DATEDIFF(DAY,0,getdate()) as datetime)
> If you want tomorrow at midnight:
> CAST(DATEDIFF(DAY,0,getdate())+1 as datetime)
> (This can be used to include data that occurred anytime today, regardless
of
> time.)
>
> When dealing with datetimes, always consider both portions. If the client
> sends a date and you want anything that occurs on that date:
> dtColumn >= @.MyDate and
> dtColumn < CAST(DATEDIFF(DAY,0,@.MyDate)+1 as datetime)
> Had the client sent the date and time, then:
> dtColumn >= CAST(DATEDIFF(DAY,0,@.MyDate) as datetime) and
> dtColumn < CAST(DATEDIFF(DAY,0,@.MyDate)+1 as datetime)
> Other posters are correct, you want to avoid wrapping functions around a
> column name in a WHERE clause if possible.
> And, you should avoid converting datetime to a character string (i.e.
> char(8)) and then converting it to datetime containing just a date or a ti
me.
> It's not effecient.
> Hope that helps,
> Joe
> "Darren Rhymer" wrote:
>|||Darren Rhymer (darren_rhymer@.hotmail.com.no.spam) writes:
> Are you saying I should somehow replace the
> CONVERT(char(8), GETDATE(), 112)
> with
> DATEDIFF(DAY,0, GETDATE())
Both yield the same result, so it's a matter of taste which one to choose.
I prefer the first, because, well, if you need to present the date in
some output, you can use the same format. Overall, when you work with
date literals, you use character strings, whereas using numeric types
for dates feels more akward to me. But it would be difficult to say
that these are any compelling reasons.
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|||With all due respect, in this example, the datetime is not being used as
ouput. Darren wants the strip the time from a datetime data type and update
a datetime column.
Someone ran performance tests on various datetime conversions. (I cannot
remember who and I appolize to the person who did it. If you search for dat
e
or datetime, you might be able to find the post and the link to the test
results.)
Converting a datetime to a string and then to a datetime again is one the
slowest performers. (I suggest you test it on your hardware in query
analyzer using a loop and variables.) Yes, we're talking microseconds per
execution but on a heavily loaded box it all adds up.
Based on those tests, the following is the fastest because it is pure
integer arithmetic which by the way is always the fastest way to do things i
n
a computer.
CAST(DATEDIFF(DAY,0, <DateTimeGoesHere> ) as datetime)
So yes, if you want the best performing code, the trigger should use
CAST(DATEDIFF(DAY,0,getdate()) as datetime)
(P.S. You need the outer cast as datetime because datediff returns an
integer value.)
Also, I have a correction to my earlier post where the client sends just the
date. The upper limit could and probably should do a simple dateadd.
dtColumn < DATEADD(DAY,1,@.MyDate)
Just my two cents,
Joe
"Erland Sommarskog" wrote:

> Darren Rhymer (darren_rhymer@.hotmail.com.no.spam) writes:
> Both yield the same result, so it's a matter of taste which one to choose.
> I prefer the first, because, well, if you need to present the date in
> some output, you can use the same format. Overall, when you work with
> date literals, you use character strings, whereas using numeric types
> for dates feels more akward to me. But it would be difficult to say
> that these are any compelling reasons.
>
> --
> 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
>|||
Joe from WI wrote:

>With all due respect, in this example, the datetime is not being used as
>ouput. Darren wants the strip the time from a datetime data type and updat
e
>a datetime column.
>Someone ran performance tests on various datetime conversions. (I cannot
>remember who and I appolize to the person who did it. If you search for da
te
>or datetime, you might be able to find the post and the link to the test
>results.)
>
Here's the post you may be talking about :
http://groups.google.com/groups?q=b...100000+datetime
Steve Kass
Drew University
>Converting a datetime to a string and then to a datetime again is one the
>slowest performers. (I suggest you test it on your hardware in query
>analyzer using a loop and variables.) Yes, we're talking microseconds per
>execution but on a heavily loaded box it all adds up.
>Based on those tests, the following is the fastest because it is pure
>integer arithmetic which by the way is always the fastest way to do things
in
>a computer.
>CAST(DATEDIFF(DAY,0, <DateTimeGoesHere> ) as datetime)
>So yes, if you want the best performing code, the trigger should use
>CAST(DATEDIFF(DAY,0,getdate()) as datetime)
>(P.S. You need the outer cast as datetime because datediff returns an
>integer value.)
>Also, I have a correction to my earlier post where the client sends just th
e
>date. The upper limit could and probably should do a simple dateadd.
>dtColumn < DATEADD(DAY,1,@.MyDate)
>Just my two cents,
>Joe
>"Erland Sommarskog" wrote:
>
>

datetime update

I would like to change all rows in a table that have the date range between
June 1, 2005 to June 30, 2005 to the month of May. I haven't been able to
figure out the SET code that would update the rows to the the month of May
instead of June. The day and year have to remain the same. Any suggestions?
Thanking you in advance.
SherryUPDATE YourTable
SET DatCol = (CASE MONTH(DATCOL) WHEN 6 THEN DATEADD(mm,-1,DATCOL) ELSE
DATCOL END)
WHERE Datcol between '20050601' AND 20050630
HTH, Jens Suessmeyer.
"Sherry" <Sherry@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2233DF06-C29A-4DDD-A4DF-993C9AAEC42A@.microsoft.com...
>I would like to change all rows in a table that have the date range between
> June 1, 2005 to June 30, 2005 to the month of May. I haven't been able
> to
> figure out the SET code that would update the rows to the the month of May
> instead of June. The day and year have to remain the same. Any
> suggestions?
> Thanking you in advance.
> Sherry|||Thank so much. Worked perfectly.
"Jens Sü?meyer" wrote:

> UPDATE YourTable
> SET DatCol = (CASE MONTH(DATCOL) WHEN 6 THEN DATEADD(mm,-1,DATCOL) ELSE
> DATCOL END)
> WHERE Datcol between '20050601' AND 20050630
> HTH, Jens Suessmeyer.
> "Sherry" <Sherry@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:2233DF06-C29A-4DDD-A4DF-993C9AAEC42A@.microsoft.com...
>
>sql

Datetime convert fails!

Hi guys
I exported some data from a text file to sql server. Here is the sample data
.
This table has about 2 million rows.There is a date field in the table which
comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
get an error as operation timed out..
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE [Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:4
5:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float] NULL,
[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_Genera
l_CI_AS
NULL,
[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL
,
[Units dispensed] [float] NULL,
[Total days supply] [float] NULL
) ON [PRIMARY]
Hope this helpsAssume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:

> Hi guys
> I exported some data from a text file to sql server. Here is the sample da
ta..
>
> This table has about 2 million rows.There is a date field in the table whi
ch
> comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
> get an error as operation timed out..
>
> Here is the data from the text file...
> Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS fl
ag
> Patient category Units dispensed Total days supply
> 1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
>
> I have tried the bulk insert as well.
> Here is the script for the create table ..
>
> USE [Library]
> GO
> /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14
:45:59
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[NormalOutlier1](
> [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Formulation ID] [float] NULL,
> [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_Gene
ral_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NU
LL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>

Datetime convert fails!

Hi guys
I exported some data from a text file to sql server. Here is the sample data..
This table has about 2 million rows.There is a date field in the table which
comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
get an error as operation timed out..
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE [Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float] NULL,
[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
NULL,
[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Units dispensed] [float] NULL,
[Total days supply] [float] NULL
) ON [PRIMARY]
Hope this helps
Assume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:

> Hi guys
> I exported some data from a text file to sql server. Here is the sample data..
>
> This table has about 2 million rows.There is a date field in the table which
> comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
> get an error as operation timed out..
>
> Here is the data from the text file...
> Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
> Patient category Units dispensed Total days supply
> 1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
>
> I have tried the bulk insert as well.
> Here is the script for the create table ..
>
> USE [Library]
> GO
> /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[NormalOutlier1](
> [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Formulation ID] [float] NULL,
> [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>

Datetime convert fails!

Hi guys
I exported some data from a text file to sql server. Here is the sample data..
This table has about 2 million rows.There is a date field in the table which
comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
get an error as operation timed out..
Here is the data from the text file...
Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
Patient category Units dispensed Total days supply
1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
I have tried the bulk insert as well.
Here is the script for the create table ..
USE [Library]
GO
/****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NormalOutlier1](
[Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Formulation ID] [float] NULL,
[Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
NULL,
[NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Units dispensed] [float] NULL,
[Total days supply] [float] NULL
) ON [PRIMARY]
Hope this helpsAssume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:
> Hi guys
> I exported some data from a text file to sql server. Here is the sample data..
>
> This table has about 2 million rows.There is a date field in the table which
> comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i
> get an error as operation timed out..
>
> Here is the data from the text file...
> Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag
> Patient category Units dispensed Total days supply
> 1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
> 1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00
>
> I have tried the bulk insert as well.
> Here is the script for the create table ..
>
> USE [Library]
> GO
> /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[NormalOutlier1](
> [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Formulation ID] [float] NULL,
> [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>

Dateproblem

Hello
I'm trying to get data from a view and it's not going well, im trying to
"group" the rows abit so I dont end up with a million rows to display a date
range....
Hope this makes sense....
CREATE TABLE #Test (
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(Startdate,Enddate,Avalible)
SELECT
'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Testerr... use this DLL instead..
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,Avalible)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse Edsvik wrote:

> I'm trying to get data from a view and it's not going well, im trying
> to "group" the rows abit so I dont end up with a million rows to
> display a date range....
> Hope this makes sense....
I once had the same problem and question asked: http://tinyurl.com/7gnst
HTH,
Stijn Verrept.|||Or this if it makes it more simple.... :S
CREATE TABLE #Test (
ObjectID int,
Wkday tinyint,
StartTime char(5),
EndTime char(5),
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Wkday,StartTime,EndTime,Avalib
le)
SELECT
1,1,'13:00','13:30','Y'
UNION SELECT
1,1,'13:30','14:00','Y'
UNION SELECT
1,1,'14:00','14:30','N'
UNION SELECT
1,1,'14:30','15:00','N'
UNION SELECT
1,1,'15:00','15:30','Y'
UNION SELECT
1,1,'15:30','16:00','Y'
UNION SELECT
1,1,'16:00','16:30','N'
UNION SELECT
1,2,'15:00','15:30','Y'
UNION SELECT
1,2,'15:30','16:00','N'
SELECT * FROM #Test
/*
Desired result:
ObjectID Wkday StartTime EndTime
1 1 '13:00' '14:00'
1 1 '15:00' '16:00'
1 2 '15:00' '15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse,
select ObjectID,Startdate, coalesce(last_available, enddate) enddate
from
(
SELECT t.*,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='Y') prev_available,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='N')
prev_not_available,
(select max(enddate) FROM #Test t1
where t1.startdate>t.startdate and t1.available='Y'
and not exists(select 1 from #test t2
where t2.available='N' and t2.startdate between t.startdate and
t1.startdate)
) last_available
FROM #Test t
where available='Y'
) t
where prev_available<prev_not_available
or prev_available is null
ObjectID Startdate
enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
(3 row(s) affected)
Nice puzzle, thanks!|||hmm,
Server: Msg 207, Level 16, State 3, Line 30
Invalid column name 'available'.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139324776.706400.210190@.f14g2000cwb.googlegroups.com...
> Lasse,
> select ObjectID,Startdate, coalesce(last_available, enddate) enddate
> from
> (
> SELECT t.*,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='Y') prev_available,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='N')
> prev_not_available,
> (select max(enddate) FROM #Test t1
> where t1.startdate>t.startdate and t1.available='Y'
> and not exists(select 1 from #test t2
> where t2.available='N' and t2.startdate between t.startdate and
> t1.startdate)
> ) last_available
> FROM #Test t
> where available='Y'
> ) t
> where prev_available<prev_not_available
> or prev_available is null
> ObjectID Startdate
> enddate
> -- ---
> ---
> 1 2006-01-31 13:00:00.000
> 2006-01-31 14:00:00.000
> 1 2006-01-31 15:00:00.000
> 2006-01-31 16:00:00.000
> 1 2006-02-01 15:00:00.000
> 2006-02-01 15:30:00.000
> (3 row(s) affected)
> Nice puzzle, thanks!
>|||yes the spell checker replaced Avalible with Available. I did not argue
with it ;)|||Alexander,
oh k :) You have any idea why ObjectID=2 doesnt show up?
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
available char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,available)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
2,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
2,'2006-01-31 14:00','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-02 15:00','2006-02-02 15:30','Y'
UNION SELECT
1,'2006-02-02 16:00','2006-02-02 16:30','Y'
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t1.startdate>t.startdate and t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.Available='N' and t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139325716.672360.263040@.g47g2000cwa.googlegroups.com...
> yes the spell checker replaced Avalible with Available. I did not argue
> with it ;)
>|||because I did not think of ObjectId at all. It's easy to take care of
however:
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate>t.startdate and
t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.ObjectID=t1.ObjectID and t2.Available='N' and
t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
ObjectID Startdate
Enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
2 2006-01-31 13:30:00.000
2006-01-31 16:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
1 2006-02-02 15:00:00.000
2006-02-02 16:30:00.000
(5 row(s) affected)

DATEADD returning odd count of records

Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qhDATEADD(m, -18, getdate()) is giving you 26th Jan 2004. That means
everything older than or equal to 26th Jan 2004. But your second query is
looking for data between 1st and 31st of Jan, which obviously is going to
have 5 days worth of additional data.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1122371409.746888.44830@.g49g2000cwa.googlegroups.com...
Hi,
We have a table here containing over 18 million rows and is updatred to
the tune of about 50,000 rows per day. Once a month I would like to run
a script that will delete any rows older than 18 months. All easy, I
here you say. Well when I do the following:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m, -18, getdate())
this returns a count of approx. 610,000, however when I workout all the
rows for the month of January 2004 (18 months ago the month I need to
remove) I return a count of approx 812,000
select count(*) from MyTable
where [Insertion Date] >= '20040101' and [Insertion Date] <= '20040131'
Apologies for being a tad vague here (obviously you don't know the ins
and outs of the data) but I was wondering if anyone else out there had
the same issue. The table schema for the first few columns is below:
CREATE TABLE [MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Consignment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Date] [datetime] NULL ,
[Batch ID] [int] NOT NULL ,
[Insertion Date] [datetime] NULL ,
Thanks
qh|||Hello, Scott
You should notice the fact that "DATEADD(m, -18, getdate())"
returns January 26, 2004 and in your second query you are
comparing with January 31, 2004.
If you want to obtain the last day of the current month,
you can use the following:
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
So your query might be:
select count(*) from MyTable
where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
Razvan|||Hi Razvan,
Cheers for the reply, your solution sorted it. I didn't expect the
count to be over 200k for the days 26-31st Jan. I think it's myself
who doesn't know his own data!
;o)
Thanks
Scott
Razvan Socol wrote:
> Hello, Scott
> You should notice the fact that "DATEADD(m, -18, getdate())"
> returns January 26, 2004 and in your second query you are
> comparing with January 31, 2004.
> If you want to obtain the last day of the current month,
> you can use the following:
> SELECT DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0) - 1
> So your query might be:
> select count(*) from MyTable
> where [Insertion Date] <= DATEADD(m,DATEDIFF(m,0,getdate())-17,0)-1
> Razvan