DB - DDL - how much time each operation takes - a doc needed

Is there a doc describing how much time each DDL can take,
e.g. (n is the number of records in the table):
add nullable column = 0(1)
add column with default falue = o(n)
etc'...


Tal Olier
otal@.mercury.co.ilI haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?|||Originally posted by Paul Young
I haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?

No it hasn't, I am looking for something like:

# Operation DB Type Example Time
1 Rename a table Oracle Alter table x rename .. o(1)
MS-SQL Sp_rename o(1)
2 Rename a index Oracle Alter index x rename.. o(1)
MS-SQL Sp_rename t.x.. o(1)
3 Add column Oracle Alter table x add y NULL o(1)
Oracle Alter table x add y default (10)/default(null) o(n)
MS-SQL Alter table x add o(1)

Daylite saving time problem

Hello
I am using SQL Server 2000, SP4
I am calculating number of hours passed between two dates. Both dates have
time set to 00:00:00. I use datediff function it works ok unless the time
interval I pass includes date when time is changed due to Daylite Saving Tim
e
(DST) issue. Instead of one hour more or one hour less datediff keeps
returning constant number of hours.
Does SQL Server 2000 internally support DST depending on a regional settings
in OS?
Thanks in advance.we don't that feature in SQL Server to my knowledge. You can write a UDF to
do the conversion.
Check out this link
http://www.planet-source-code.com/U...cripts/ShowCode!asp/txtCodeId!9
11/lngWid!5/anyname.htm|||Thanks a lot|||Some ideas here maybe:
http://www.aspfaq.com/2218
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||Oh, and also the calendar table.
http://www.aspfaq.com/2519
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||or how about rather than using getdate() to get the two dates in the
first place, use getutcdate() function?
GETUTCDATE
Returns the datetime value representing the current UTC time (Universal
Time Coordinate or Greenwich Mean Time). The current UTC time is
derived from the current local time and the time zone setting in the
operating system of the computer on which SQL Server is running.
Mel|||> or how about rather than using getdate() to get the two dates in the
> first place, use getutcdate() function?
> GETUTCDATE
> Returns the datetime value representing the current UTC time (Universal
> Time Coordinate or Greenwich Mean Time). The current UTC time is
> derived from the current local time and the time zone setting in the
> operating system of the computer on which SQL Server is running.
Well, if you're comparing two datetime values:
2005-12-31
2006-06-01
If you're in a timezone that observes daylight savings time, your
calculation is going to be an hour off (which way depends on what is
currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
an hour off in the other direction the next time the daylight savings time
goes on or off.
The calendar table can help solve this problem by giving you the offset on
each of the dates in question, allowing you to adjust each date accordingly.|||You also have to take into account that different areas change their clocks
on different dates, so you may need to create a second table with each time
zone and the date/time that they change their clocks.
That, and some areas (Arizona for example) do not use daylight savings time
at all.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OES60M7YGHA.4144@.TK2MSFTNGP04.phx.gbl...
> Well, if you're comparing two datetime values:
> 2005-12-31
> 2006-06-01
> If you're in a timezone that observes daylight savings time, your
> calculation is going to be an hour off (which way depends on what is
> currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
> an hour off in the other direction the next time the daylight savings time
> goes on or off.
> The calendar table can help solve this problem by giving you the offset on
> each of the dates in question, allowing you to adjust each date
accordingly.
>|||> You also have to take into account that different areas change their
> clocks
> on different dates, so you may need to create a second table with each
> time
> zone and the date/time that they change their clocks.
Or an extra column for each timezone (reproduce the tinyints instead of the
wider date values).

> That, and some areas (Arizona for example) do not use daylight savings
> time
> at all.
Right, Indiana just changed. Next year, the formula for determining the
dates changed in the US, so I think a lot of people who hav used an inline
calculation for this are either already working on fixing it or have plenty
of work to do over the winter. Since we used a calendar table in all of our
implementations, we don't have to worry about it... a simple update
statement corrects all future data until they waffle again.|||a column for each timezone seems much more complex than a single table with
one row each.
However, the benefit to doing it with columns is that you don't run into
problems when the timezone rules change. In the case of Indiana, you would
update the Indiana column in the calendar table for those date ranges. With
a separate table you would need to store the date that the rules changed and
always make sure you are joining to the correct row. I think I like your
idea of multiple columns better.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O%23NxvE8YGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Or an extra column for each timezone (reproduce the tinyints instead of
the
> wider date values).
>
> Right, Indiana just changed. Next year, the formula for determining the
> dates changed in the US, so I think a lot of people who hav used an inline
> calculation for this are either already working on fixing it or have
plenty
> of work to do over the winter. Since we used a calendar table in all of
our
> implementations, we don't have to worry about it... a simple update
> statement corrects all future data until they waffle again.
>

Day of the week

I have a table whcih contains order Id (orderid_c), and order date
(orderdate_d).
Is there anywhere I can program to count the number of order from Monday to
the day the report is run, for example, when I run the report on Wednesday,
the report will cover from Monday to Wednesday and when I run the report on
Thursday, the report will cover from Monday to Thursday. I will have to run
the report several time during the business hour.
Thanks,set datefirst 1
select count(orderid_c) from table
where datepart(wk,orderdate_d) = datepart(wk,getdate())
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:9FCC02A9-29B8-48B1-B888-091BBC502CFD@.microsoft.com...
> I have a table whcih contains order Id (orderid_c), and order date
> (orderdate_d).
> Is there anywhere I can program to count the number of order from Monday
to
> the day the report is run, for example, when I run the report on
Wednesday,
> the report will cover from Monday to Wednesday and when I run the report
on
> Thursday, the report will cover from Monday to Thursday. I will have to
run
> the report several time during the business hour.
>
> Thanks,
>|||sp_who will tell you who and what database
"qjlee" wrote:

> I have a table whcih contains order Id (orderid_c), and order date
> (orderdate_d).
> Is there anywhere I can program to count the number of order from Monday t
o
> the day the report is run, for example, when I run the report on Wednesday
,
> the report will cover from Monday to Wednesday and when I run the report o
n
> Thursday, the report will cover from Monday to Thursday. I will have to r
un
> the report several time during the business hour.
>
> Thanks,
>|||On Thu, 18 Aug 2005 10:31:01 -0700, qjlee wrote:

>I have a table whcih contains order Id (orderid_c), and order date
>(orderdate_d).
>Is there anywhere I can program to count the number of order from Monday to
>the day the report is run, for example, when I run the report on Wednesday,
>the report will cover from Monday to Wednesday and when I run the report on
>Thursday, the report will cover from Monday to Thursday. I will have to ru
n
>the report several time during the business hour.
Hi qjlee,
Here's how to select data between "last monday" and "now":
SELECT ...
FROM ...
WHERE TheDate >= DATEADD(day, DATEDIFF(day, '20050103',
CURRENT_TIMESTAMP) / 7 * 7, '20050103')
AND TheDate <= CURRENT_TIMESTAMP
AMD ...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Day of the month

I am trying to code a proceedure that will run every weekend. This process will run for a number of hours beginning at 0900 Saturday and ending at 2100 Sunday. However, on the 3rd weekend of the month, I need it run for a shorter time and to also skip some tables. What I have now to find the day of the month I need is

Begin
SET @.3rdSaturday = @.1stDayMonth
/*Loops until the date of the first Saturday(DayofWeek #7)
of the current month is found */
WHILE DATEPART(dw,@.3rdSaturday) <> 7

/*Adds 1 day to the first day of the month until it
reaches the date of the first Friday of the month */
SET @.3rdSaturday = DATEADD(d,1,@.3rdSaturday)
/*Adds 14 days to the first Friday of the month.
The end result is the 3rd Fridays date for the current month*/
SET @.3rdSaturday = DATEADD(d,14,@.3rdSaturday)
End

I am trying to find a better way to find the 3rd Saturday (or similar) of the month. I have also investigated DATENAME function but ran into the same problem. Also if I were using SQLDMOFreq_Monthly I could do it but I am trying to write all of this in T-SQL.

Any and all help is appreciated.

Akinja

Akinja-Earl:

Take a look at this article about establishing a calendar table:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Also, are you running on SQL Server 2005 or SQL Server 2000?

Dave

|||Thanks, I will.|||

If you prefer not to use a calendar table you can try something like:

set nocount on

declare @.dateOfMonth datetime
set @.dateOfMonth = '10/13/6'

declare @.monthString varchar (2)
set @.monthString = convert (varchar (2), month (@.dateOfMonth))
declare @.yearString varchar (4)
set @.yearString = convert (varchar (4), year (@.dateOfMonth))

/*
select sampleDate,
datepart (dw, sampleDate) as dayOfWeek,
datename (dw, sampleDate) as nameOfDay
from ( select @.monthString + '/' + convert (char(2), 14 + iter) + '/' + @.yearString as sampleDate
from small_iterator (nolock)
where iter <= 7
) a
where datepart (dw, sampleDate) = 7
*/

-- --
-- create an iterator if you don't already have one.
-- --
declare @.iterator table (iter integer not null)
insert into @.iterator values (1)
insert into @.iterator values (2)
insert into @.iterator values (3)
insert into @.iterator values (4)
insert into @.iterator values (5)
insert into @.iterator values (6)
insert into @.iterator values (7)

select sampleDate,
datepart (dw, sampleDate) as dayOfWeek,
datename (dw, sampleDate) as nameOfDay
from ( select @.monthString + '/' + convert (char(2), 14 + iter) + '/' + @.yearString as sampleDate
from @.iterator
) a
where datepart (dw, sampleDate) = 7

-- -
-- S A M P L E O U T P U T :
-- -

-- sampleDate dayOfWeek nameOfDay
-- - --
-- 10/21/2006 7 Saturday

|||

Thanks you for both suggestions. I like the calendar creation method since I can reuse it for other purposes. The iteration method will work with what I am working now since I am trying to get it done quickly.

Akinja

DateTime function question

Greetings all,

Is there a function that exists that returns the last day of each month?

I need to allow payments to a job number if the import date is before the cancel dates following month end.

So I can't dateadd or datediff as far as I can see with any accuracy.

Any suggestions?

Adamus

how about this Adamus

Code Snippet

CREATE function [dbo].[Last_Day_Of_Month](@.monthIn varchar(2), @.yearIn char(4))

returns datetime

as

begin

DECLARE @.wrkMonth char(2),

@.wrkDay char(2),

@.wrkDate DATETIME

select @.wrkMonth = right('00' + @.monthIn, 2)

if isdate(convert(char(02), @.wrkMonth) + '-01-' + convert(char(04), @.yearIn))=0

begin

return null

end

SET @.wrkDate = @.yearIn + '/' + @.monthIn + '/01'

return DATEADD(DD, -1, DATEADD(M, 1, @.wrkDate))

end

|||

Another way is something like:

Code Snippet

select dateadd(day, -1, dateadd(mm, 1, dateadd(mm, datediff (mm, 0, getdate()), 0)))

2007-07-31 00:00:00.000

You're welcome Adam.

|||

Both work beautifully.

Thanks guys...I just couldn't wrap my head around it this morning.

Adamus

DateTime confusion.

Hi All,

I'm stumped. I've got a stored procedure with a number of input parameters, and working fine.

I added two extra input parameters, FromDate datetime, ToDate datetime. I have not even included these in the SQL yet and just tried to execute the stored proc alone with manually inputting paramters, but I keep getting error: @.FromDate: this input parameter cannot be converted.

That surely means a formatting issue, so I copy and pasted a value directly from the database into this parameter field, and still get the same error. I've tried various formats, with single and double quotes, and without. But just dont know?

And even when I populate these parameters in my code and call the stored proc, it returns no results either, even though I haven't included these new date paramters in the SQL select, so that means it was in error and no doubt a formatting issue on those date fields.

format I used was: 17/07/2007 00:00:00

I tried to populate the parameters via the code as follows:

Dim dt As DateTime
DateFromTB1.Text = DateTime.Now
DateTime.TryParse(DateFromTB1.Text, dt)
SqlDataSource1.SelectParameters("FromDate").DefaultValue = dt

I know there is an extra step for now (DateFromTB1.Text = DateTime.Now), this step will fall away and just parse the textbox.text field as entered by the user.

Any help appreciated, thanks.

Try this:

string strDate = Convert.ToDateTime(txtDate.Text).ToString("MM/dd/yyyy");

if you want to pass it as string or

DateTime dtDate = Convert.ToDateTime(txtDate.Text).ToUniversalTime();

or

DateTimeFormatInfo myDTFI =newCultureInfo("en-GB",false).DateTimeFormat;

DateTime Dtime =Convert.ToDateTime("31/12/2007", myDTFI);

or

DateTime dtDate = Convert.ToDateTime(txtDate.Text).ToShortDateStrin(); if you are using smalldatatime in your databse.

Also there is a .ToLocalTime() function if that might be any help to you too.

Hope this would help.

|||

Hi Mehdi,

thanks for the quick response, I've since discovered that even though the SQL Server Express date format, as displayed in my Show Table Data windows, is dd/mm/yyyy, it is actually expecting mm/dd/yyyy format via my stored proc.

My computer regional settings are dd/mm/yyy, but somehow SQL server settings, or perhaps it's my VWD Express settings are the American version, and I cannot find where to change it. any ideas? thanks.

|||

You can either use SET DATEFORMAT or be more efficient and send the data in YYYYMMDD format irrespective of client's location and not have to worry about converting mdy to dmy to viceversa.

|||

thanks for the help, that did the trick.

Dates wrecking my head!

Anyone got a script to get the start-dates and end-dates for x number of wee
ks?
The tricky thing is that the script needs to account for ws where the
monday date is in say April and the Friday date is in May. In this case the
start-date is the monday but the end-date may be a wednesday or whenever the
last date of the month is. In other cases the start-date wont be the monday
date but could be the wednesday date. Does this make sense?
I need a result set like this...
Start-date End-date
2006-01-30 2006-01-31
2006-02-01 2006-02-03
2006-02-06 2006-02-10
2006-02-13 2006-02-17
2006-02-20 2006-02-24
2006-02-27 2006-02-28
2006-03-01 2006-03-03
2006-03-06 2006-03-10
2006-03-13 2006-03-17NH
Can you provide DDL+ sample data + expected result?
CREATE TABLE bbb
(
dt DATETIME,
...
....
)
"NH" <NH@.discussions.microsoft.com> wrote in message
news:43D6F63F-D1B3-454C-970E-ED83CDB4480A@.microsoft.com...
> Anyone got a script to get the start-dates and end-dates for x number of
> ws?
> The tricky thing is that the script needs to account for ws where the
> monday date is in say April and the Friday date is in May. In this case
> the
> start-date is the monday but the end-date may be a wednesday or whenever
> the
> last date of the month is. In other cases the start-date wont be the
> monday
> date but could be the wednesday date. Does this make sense?
> I need a result set like this...
> Start-date End-date
> 2006-01-30 2006-01-31
> 2006-02-01 2006-02-03
> 2006-02-06 2006-02-10
> 2006-02-13 2006-02-17
> 2006-02-20 2006-02-24
> 2006-02-27 2006-02-28
> 2006-03-01 2006-03-03
> 2006-03-06 2006-03-10
> 2006-03-13 2006-03-17|||What criterion/criteria determine whether your start-date is a Monday
or Wednesday?
If the end-date is a Wednesday, is the start-date then a Thursday?
Etc.
Andrew Watt [MVP]
On Mon, 24 Apr 2006 04:57:02 -0700, NH <NH@.discussions.microsoft.com>
wrote:

>Anyone got a script to get the start-dates and end-dates for x number of we
eks?
>The tricky thing is that the script needs to account for ws where the
>monday date is in say April and the Friday date is in May. In this case the
>start-date is the monday but the end-date may be a wednesday or whenever th
e
>last date of the month is. In other cases the start-date wont be the monday
>date but could be the wednesday date. Does this make sense?
>I need a result set like this...
>Start-date End-date
>2006-01-30 2006-01-31
>2006-02-01 2006-02-03
>2006-02-06 2006-02-10
>2006-02-13 2006-02-17
>2006-02-20 2006-02-24
>2006-02-27 2006-02-28
>2006-03-01 2006-03-03
>2006-03-06 2006-03-10
>2006-03-13 2006-03-17|||this is the table that the results will go into...
CREATE TABLE [SYSDBA].[CAP_CalendarWs] (
[key] [int] IDENTITY (1, 1) NOT NULL ,
[startdate] [datetime] NULL ,
[enddate] [datetime] NULL
) ON [PRIMARY]
I want to fill it with dates between 2006 and 2050.
Basically the start date will always be the monday, unless the any months
start date is some other day of the w. Same for end dates, they are
usually friday dates but a month end date could be a tuesday etc and this
needs to be recorded.
If you look at the sample data in my first post you should be able to see
the way it should work...? Does this make sense?
"Uri Dimant" wrote:

> NH
> Can you provide DDL+ sample data + expected result?
> CREATE TABLE bbb
> (
> dt DATETIME,
> ...
> .....
> )
>
>
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:43D6F63F-D1B3-454C-970E-ED83CDB4480A@.microsoft.com...
>
>|||try this and let me know if this was what you wanted.
select identity(int,0,1) as id into #temp from sysobjects
declare @.startdate datetime, @.enddate datetime
set @.startdate = '2006-01-30'
set @.enddate = '2006-03-17'
select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) as
eow from #temp a, #temp b
where
datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
between 1 and 5
and (
(datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
dateadd(dd, b.id,@.startdate)) = 6 )
or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
datepart(dd,dateadd(dd, b.id,@.startdate)) =
datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-'
+
cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
datetime))))
or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
dateadd(dd, a.id,@.startdate)) = 1 ))
and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd,
b.id,@.startdate))
and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
a.id,@.startdate) <= @.enddate
drop table #temp|||A technical bug in my solution. date difference between 1 and 5 changed to 1
and 4.Use this.. updated.
select identity(int,0,1) as id into #temp from sysobjects
declare @.startdate datetime, @.enddate datetime
set @.startdate = '2006-01-01'
set @.enddate = '2050-01-01'
select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) as
eow from #temp a, #temp b
where
datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
between 0 and 4
and (
(datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
dateadd(dd, b.id,@.startdate)) = 6 )
or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
datepart(dd,dateadd(dd, b.id,@.startdate)) =
datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-'
+
cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
datetime))))
or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
dateadd(dd, a.id,@.startdate)) = 1 ))
and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd,
b.id,@.startdate))
and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
a.id,@.startdate) <= @.enddate
order by dateadd(dd, a.id,@.startdate)
drop table #temp|||NH
Read please this article helps you to get an idea
http://www.aspfaq.com/show.asp?id=2519
"NH" <NH@.discussions.microsoft.com> wrote in message
news:0CAF9FBF-E162-48FF-9661-971551CE2D1A@.microsoft.com...
> this is the table that the results will go into...
> CREATE TABLE [SYSDBA].[CAP_CalendarWs] (
> [key] [int] IDENTITY (1, 1) NOT NULL ,
> [startdate] [datetime] NULL ,
> [enddate] [datetime] NULL
> ) ON [PRIMARY]
> I want to fill it with dates between 2006 and 2050.
> Basically the start date will always be the monday, unless the any months
> start date is some other day of the w. Same for end dates, they are
> usually friday dates but a month end date could be a tuesday etc and this
> needs to be recorded.
> If you look at the sample data in my first post you should be able to see
> the way it should work...? Does this make sense?
> "Uri Dimant" wrote:
>|||Hi all
Omnibuzz - there are some duplicates in that for me (e.g. 2006-04-03), but
I'm not sure what the problem is :(
Here's my stab... :)
--inputs
declare @.startdate datetime, @.enddate datetime
set @.startdate = '20060130'
set @.enddate = '20500101'
set datefirst 7
--calculation
declare @.NumberOfDays int
set @.NumberOfDays = datediff(d, @.startdate, @.enddate) + 1
set rowcount @.NumberOfDays
declare @.numbers table (i int identity(0,1), x bit)
insert into @.numbers select null from master.dbo.sysobjects a,
master.dbo.sysobjects b, master.dbo.sysobjects c
set rowcount 0
select d as StartDate,
case when datepart(day, d) = 1 --start of month
then dateadd(day, 6-datepart(dw, d), d)
when datepart(month, d) != datepart(month, d+4) --end of month
then dateadd(month, datediff(month, 0, d+4), 0)-1
else --normal w
d+4
end as EndDate
from
(select dateadd(dd, i, @.startdate) d from @.numbers) dates
where d = @.startdate --don't miss start date
or datepart(dw, d) = 2 --monday
or (datepart(day, d) = 1 and datepart(dw, d) between 3 and 6) --1st of
month and tue-fri
"Omnibuzz" wrote:

> A technical bug in my solution. date difference between 1 and 5 changed to
1
> and 4.Use this.. updated.
> select identity(int,0,1) as id into #temp from sysobjects
> declare @.startdate datetime, @.enddate datetime
> set @.startdate = '2006-01-01'
> set @.enddate = '2050-01-01'
> select dateadd(dd, a.id,@.startdate) as sow, dateadd(dd, b.id,@.startdate) a
s
> eow from #temp a, #temp b
> where
> datediff(day,dateadd(dd, a.id,@.startdate),dateadd(dd, b.id,@.startdate))
> between 0 and 4
> and (
> (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and datepart(dw,
> dateadd(dd, b.id,@.startdate)) = 6 )
> or (datepart(dw, dateadd(dd, a.id,@.startdate)) = 2 and
> datepart(dd,dateadd(dd, b.id,@.startdate)) =
> datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd,
> b.id,@.startdate))+ month(dateadd(dd, b.id,@.startdate))/12 as varchar) + '-
' +
> cast(month(dateadd(dd, b.id,@.startdate))%12 + 1 as varchar) + '-01' as
> datetime))))
> or (datepart(dw, dateadd(dd, b.id,@.startdate)) = 6 and datepart(dd,
> dateadd(dd, a.id,@.startdate)) = 1 ))
> and datepart(mm, dateadd(dd, a.id,@.startdate)) = datepart(mm, dateadd(dd
,
> b.id,@.startdate))
> and dateadd(dd, b.id,@.startdate) <= @.enddate and dateadd(dd,
> a.id,@.startdate) <= @.enddate
> order by dateadd(dd, a.id,@.startdate)
> drop table #temp|||Hi Ryan,
Thanks for pointing it out, though I haven't checked it yet. I am back
home now. Will check it and post the update, if necessary, tomorrow.|||On Mon, 24 Apr 2006 04:57:02 -0700, NH wrote:

>Anyone got a script to get the start-dates and end-dates for x number of we
eks?
>The tricky thing is that the script needs to account for ws where the
>monday date is in say April and the Friday date is in May. In this case the
>start-date is the monday but the end-date may be a wednesday or whenever th
e
>last date of the month is. In other cases the start-date wont be the monday
>date but could be the wednesday date. Does this make sense?
(snip)
Hi NH,
Assuming that you already have a table of numbers in your database,
here's how you could fill your table quickly:
DECLARE @.StartOfPeriod smalldatetime
DECLARE @.EndOfPeriod smalldatetime
SET @.StartOfPeriod = '20060101'
SET @.EndOfPeriod = '20081231'
CREATE TABLE Ws
(StartDate smalldatetime NOT NULL PRIMARY KEY,
EndDate smalldatetime NOT NULL
)
-- Step 1: Generate full ws (mon-fri)
INSERT INTO Ws (StartDate, EndDate)
SELECT DATEADD(w, Number, '20050103'),
DATEADD(w, Number, '20050107')
FROM Numbers
WHERE DATEADD(w, Number, '20050103') >= @.StartOfPeriod
AND DATEADD(w, Number, '20050107') <= @.EndOfPeriod
-- Step 2a: For ws that span a month, add second partial w
INSERT INTO Ws (StartDate, EndDate)
SELECT DATEADD(day, 1 - DAY(EndDate), EndDate), EndDate
FROM Ws
WHERE MONTH(StartDate) <> MONTH(EndDate)
-- Step 2b: For ws that span a month, shorten first partial w
UPDATE Ws
SET EndDate = DATEADD(day, - DAY(EndDate), EndDate)
WHERE MONTH(StartDate) <> MONTH(EndDate)
SELECT * FROM Ws
go
Hugo Kornelis, SQL Server MVP

dates wrecking my head!

Anyone got a script to get the start-dates and end-dates for x number of weeks?
The tricky thing is that the script needs to account for weeks where the
monday date is in say April and the Friday date is in May. In this case the
start-date is the monday but the end-date may be a wednesday or whenever the
last date of the month is. In other cases the start-date wont be the monday
date but could be the wednesday date. Does this make sense?
I need a result set like this...
Start-date End-date
2006-01-30 2006-01-31
2006-02-01 2006-02-03
2006-02-06 2006-02-10
2006-02-13 2006-02-17
2006-02-20 2006-02-24
2006-02-27 2006-02-28
2006-03-01 2006-03-03
2006-03-06 2006-03-10
2006-03-13 2006-03-17Wrong forum, ignore this.
"NH" wrote:
> Anyone got a script to get the start-dates and end-dates for x number of weeks?
> The tricky thing is that the script needs to account for weeks where the
> monday date is in say April and the Friday date is in May. In this case the
> start-date is the monday but the end-date may be a wednesday or whenever the
> last date of the month is. In other cases the start-date wont be the monday
> date but could be the wednesday date. Does this make sense?
> I need a result set like this...
> Start-date End-date
> 2006-01-30 2006-01-31
> 2006-02-01 2006-02-03
> 2006-02-06 2006-02-10
> 2006-02-13 2006-02-17
> 2006-02-20 2006-02-24
> 2006-02-27 2006-02-28
> 2006-03-01 2006-03-03
> 2006-03-06 2006-03-10
> 2006-03-13 2006-03-17
>

Dates of a week

Hi! I have the week number and the year. I want get all the dates that fall
in that week.
Is anyone who has idea to get this?
Barentry this
CREATE FUNCTION [dbo].[fnStartDayOfWeek](
@.date datetime )
RETURNS datetime
BEGIN
SET @.date = CONVERT(varchar(10), @.date, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @.date), @.date)
END
GO
CREATE FUNCTION [dbo].[fnLastDayOfWeek](
@.date datetime )
RETURNS datetime
BEGIN
SET @.date = CONVERT(varchar(10), @.date, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @.date)+6, @.date)
END
GO
DECLARE @.StartOfYear varchar(10)
DECLARE @.year varchar(4)
DECLARE @.WeekNo int
SET @.WeekNo = 2
SET @.Year = '2005'
SET @.StartOfYear = @.Year+'0101'
SELECT dbo. fnLastDayOfWeek(DATEADD(dd,7*@.WeekNo,@.St
artOfYear) )
SELECT dbo. fnStartDayOfWeek(DATEADD(dd,7*@.WeekNo,@.S
tartOfYear) )
Aneessh R
"Baren" <Baren@.discussions.microsoft.com> wrote in message
news:2216A3EB-5EA3-4BA3-9119-0758F4A055F2@.microsoft.com...
> Hi! I have the week number and the year. I want get all the dates that
> fall
> in that week.
> Is anyone who has idea to get this?
> Baren|||There are many good reasons to have a 'calendar' table in your database.
This is one of them.
See:
http://www.aspfaq.com/show.asp?id=2519
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Baren" <Baren@.discussions.microsoft.com> wrote in message
news:2216A3EB-5EA3-4BA3-9119-0758F4A055F2@.microsoft.com...
> Hi! I have the week number and the year. I want get all the dates that
> fall
> in that week.
> Is anyone who has idea to get this?
> Baren

Dates of a week

Hi! I have the week number and the year. I want get all the dates that fall
in that week.
Is anyone who has idea to get this?
Barentry this
CREATE FUNCTION [dbo].[fnStartDayOfWeek](
@.date datetime )
RETURNS datetime
BEGIN
SET @.date = CONVERT(varchar(10), @.date, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @.date), @.date)
END
GO
CREATE FUNCTION [dbo].[fnLastDayOfWeek](
@.date datetime )
RETURNS datetime
BEGIN
SET @.date = CONVERT(varchar(10), @.date, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @.date)+6, @.date)
END
GO
DECLARE @.StartOfYear varchar(10)
DECLARE @.year varchar(4)
DECLARE @.WeekNo int
SET @.WeekNo = 2
SET @.Year = '2005'
SET @.StartOfYear = @.Year+'0101'
SELECT dbo.fnLastDayOfWeek(DATEADD(dd,7*@.WeekNo,@.StartOfYear) )
SELECT dbo.fnStartDayOfWeek(DATEADD(dd,7*@.WeekNo,@.StartOfYear) )
Aneessh R
"Baren" <Baren@.discussions.microsoft.com> wrote in message
news:2216A3EB-5EA3-4BA3-9119-0758F4A055F2@.microsoft.com...
> Hi! I have the week number and the year. I want get all the dates that
> fall
> in that week.
> Is anyone who has idea to get this?
> Baren|||There are many good reasons to have a 'calendar' table in your database.
This is one of them.
See:
http://www.aspfaq.com/show.asp?id=2519
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Baren" <Baren@.discussions.microsoft.com> wrote in message
news:2216A3EB-5EA3-4BA3-9119-0758F4A055F2@.microsoft.com...
> Hi! I have the week number and the year. I want get all the dates that
> fall
> in that week.
> Is anyone who has idea to get this?
> Baren

Dates and Loops question

I have a table called Months_Days and I want to fill it with the months 1 thru 12 in the months column, and fill in the corresponding number of days in the days column. i only want to use 1 loop and 1 Insert Into statement. Here's what i have so far. i can get the months inserted, but it inserts 31 for the number of days for each month. can anybody see what i'm doing wrong?
thanks in advance!

Drop Table Month_Days;
Create Table Month_Days(
Month Number(2),
Days Number(2));

Declare
LoopM Binary_Integer;
LoopD Binary_Integer;
Begin_Date Date;
End_Date Date;
Begin
LoopM:= 0;
LoopD:= 0;

Loop
Begin_Date:= To_Date('01-Jan-2008', 'DD, Mon, YYYY');
End_Date:= Last_Day(Begin_Date);
LoopM:=LoopM+1;
LoopD:=End_Date-Begin_Date+1;
IF LoopM=13 Then
Exit;
End IF;
End_Date:= Add_Months(Begin_Date, 1);
Insert Into Month_Days Values (LoopM, LoopD);
End Loop;
End;Yes: every time you go round the loop you reset Begin_Date to 01-Jan-2008, so you always set LoopD to the number of days in January.

Your code is way over-complicated, and doesn't make use of basic PL/SQL constructs like the FOR loop, e.g.

FOR LoopM IN 1..12 LOOP
...
END LOOP;

Here is a working version of your code:

Declare
LoopD Binary_Integer;
Begin_Date Date;
End_Date Date;
Begin
Begin_Date:= To_Date('01-Jan-2008', 'DD, Mon, YYYY');
FOR LoopM IN 1..12
Loop
End_Date:= Last_Day(Begin_Date);
LoopD:=End_Date-Begin_Date+1;
Insert Into Month_Days Values (LoopM, LoopD);
Begin_Date:= Add_Months(Begin_Date, 1);
End Loop;
End;
/

dates

I have a view that shows me how many visits i have had on my website.
UniqueVisits(number), TheYear(2005 (using datepart)), TheMonth(April (using
datename)), TheDay (Sunday (using datename),TheDate (10 (using datepart)).
The result is like this:
.......
23, 2005, April, Sunday, 10
So for April i so far has 10 records since it is April 10.
The table is like this:
CREATE TABLE [dbo].[T_PageStat] (
[IDStat] [int] IDENTITY (1, 1) NOT NULL ,
[DateRegistered] [datetime] NULL ,
[Counter] [numeric](18, 0) NULL ,
[IPAddress] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
[BrowserData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL ,
[LanguageData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL
) ON [PRIMARY]
As you see i am collecting the date the visitor entered, a counter telling
me how many times this visitor entered, the IP, what kind of browser, and
finally the language the user has set in browser language. I use SPROC to
populate the table
I want to update my view so that i can get a record for every day in the
month even tho it is only April 10. The rest of the days will be 0
(11,12....)
Not sure how to do that so i was hoping for some help.
Any tip will be appreciated. I am using a SQL 2000 server
Best regards, Trond
The code for the view:
CREATE VIEW dbo.statUniquePrMonth
AS
SELECT TOP 100 PERCENT COUNT(IDStat) AS UniqueVisits, DATEPART(YYYY,
DateRegistered) AS TheYear, DATENAME(month, DateRegistered) AS TheMonth,
DATENAME(dw, DateRegistered) AS TheDay, DATEPART(dd,
DateRegistered) AS TheDate
FROM dbo.T_PageStat
GROUP BY DATEPART(YYYY, DateRegistered), DATENAME(month, DateRegistered),
DATENAME(dw, DateRegistered), DATEPART(dd, DateRegistered)
HAVING (DATEPART(YYYY, DateRegistered) = DATEPART(YYYY, GETDATE())) AND
(DATENAME(month, DateRegistered) = DATENAME(month, GETDATE()))
ORDER BY DATEPART(dd, DateRegistered)Hi
This may be easiest with a calander table e.g
http://www.aspfaq.com/show.asp?id=2519
You can then use an outer join to get all the days in the given month.
John
"Trond" <thoiberg@.broadpark.no> wrote in message
news:4258d99c$1@.news.broadpark.no...
>I have a view that shows me how many visits i have had on my website.
> UniqueVisits(number), TheYear(2005 (using datepart)), TheMonth(April
> (using datename)), TheDay (Sunday (using datename),TheDate (10 (using
> datepart)).
> The result is like this:
> .......
> 23, 2005, April, Sunday, 10
> So for April i so far has 10 records since it is April 10.
> The table is like this:
> CREATE TABLE [dbo].[T_PageStat] (
> [IDStat] [int] IDENTITY (1, 1) NOT NULL ,
> [DateRegistered] [datetime] NULL ,
> [Counter] [numeric](18, 0) NULL ,
> [IPAddress] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
> [BrowserData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL ,
> [LanguageData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL
> ) ON [PRIMARY]
> As you see i am collecting the date the visitor entered, a counter telling
> me how many times this visitor entered, the IP, what kind of browser, and
> finally the language the user has set in browser language. I use SPROC to
> populate the table
>
> I want to update my view so that i can get a record for every day in the
> month even tho it is only April 10. The rest of the days will be 0
> (11,12....)
> Not sure how to do that so i was hoping for some help.
> Any tip will be appreciated. I am using a SQL 2000 server
> Best regards, Trond
> The code for the view:
> CREATE VIEW dbo.statUniquePrMonth
> AS
> SELECT TOP 100 PERCENT COUNT(IDStat) AS UniqueVisits, DATEPART(YYYY,
> DateRegistered) AS TheYear, DATENAME(month, DateRegistered) AS TheMonth,
> DATENAME(dw, DateRegistered) AS TheDay, DATEPART(dd,
> DateRegistered) AS TheDate
> FROM dbo.T_PageStat
> GROUP BY DATEPART(YYYY, DateRegistered), DATENAME(month, DateRegistered),
> DATENAME(dw, DateRegistered), DATEPART(dd, DateRegistered)
> HAVING (DATEPART(YYYY, DateRegistered) = DATEPART(YYYY, GETDATE()))
> AND (DATENAME(month, DateRegistered) = DATENAME(month, GETDATE()))
> ORDER BY DATEPART(dd, DateRegistered)
>

Dates

I have a field where the date is in a number string, for example, 20020731. I need to convert this into a date string. Any ideas?
Thanks.Look up convert in the Holy Book (SQL Server Books Online)|||Several possibilities:
1. Write a scalar UDF that returns a date time from a set string format.

2. USe the following T-SQL (though it may be slow):

declare @.DateString varchar(8)

select @.DateString = '20030731'

select
cast(substring(@.DateString, 5, 2) + '/' + substring(@.DateString, 7, 2) + '/' + substring(@.DateString, 1, 4) as DateTime)

3. If you are importing this date into your database from another data source using DTS, you can use on of the Copy options to specify that the source is a date/time string (and then specify the precise format).

Regards,

Hugh Scott
Originally posted by exdter
I have a field where the date is in a number string, for example, 20020731. I need to convert this into a date string. Any ideas?
Thanks.|||Usually, CONVERT is used to transform a date/time into a char or varchar data type. Looking at it, I don't see anything that would immediately allow you to take a string an convert it to date/time.

Regards,

hmscott

Originally posted by Enigma
Look up convert in the Holy Book (SQL Server Books Online)|||Thats what I was afraid of. Orqacle makes it so easy.
Thanks for your time.|||I just re-read your sig line. I nearly spit coffee all over the keyboard. Thanks for starting my day off with a laugh.

:-)

Originally posted by Enigma
Look up convert in the Holy Book (SQL Server Books Online)|||Why? And I'm glad you were amused.|||Originally posted by hmscott
Usually, CONVERT is used to transform a date/time into a char or varchar data type. Looking at it, I don't see anything that would immediately allow you to take a string an convert it to date/time.

Regards,

hmscott

how about

select convert(varchar,convert(datetime,'20031201'),101)|||I need to put a column name in there. If I put select convert(char(10),column_name,101) from table_name, I just get the same string I had before.|||Originally posted by exdter
I need to put a column name in there. If I put select convert(char(10),column_name,101) from table_name, I just get the same string I had before.

Use

select convert(varchar(10),convert(datetime,column_name), 101)|||This is what I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.|||Enigma:
I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

Regards,

hmscott|||Try this:

declare @.DateString varchar(8)

select @.DateString = '20030731'

select convert(datetime, @.DateString)

Originally posted by hmscott
Enigma:
I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

Regards,

hmscott|||I'm not sure I follow you. The string I have is in number format and is in the 'yyyymmdd' format.|||I need to put a column name there.|||This is what I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

In case you are receiving that error , there is surely some value which does not fit in into the yyyymmdd format. You will need to correct tahat first.|||Sorry, try this:

/* begin DDL */
CREATE TABLE DateNumbers (
DateNumber int
)
GO

INSERT INTO DateNumbers VALUES (20030731)
GO
INSERT INTO DateNumbers VALUES (20030801)
GO
INSERT INTO DateNumbers VALUES (20030802)
GO

SELECT Cast(Cast(DateNumber as Varchar(8)) as datetime) FROM DateNumbers

I did not understand that the field was numeric.

Regards,

hmscott
Originally posted by exdter
I need to put a column name there.|||I have 15000 dates in the table. I need to use a column name.
Thanks|||Originally posted by hmscott
Enigma:
I stand corrected. I had never seen that before. It seems to only work if the data is formatted YYYYMMDD (or YYMMDD). Is that correct, or is there an option to specify the order of characters in the date string?

As for your sig, there's a classic definition of humor, that I can't recall right now, something to do with continuity and perception and cognition. Anyway, it met that definition.

Regards,

hmscott
from the Holy book again

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

the style values used converting datetime to varchar work the other way round too
eg : select convert(datetime,'12/01/2003',103)
style 103 : dd/mm/yy (British/French)|||If I use this select convert(varchar(10),convert(datetime,column_name), 101)
and put in the actual string, it works. If I try to put in the column name, I get the arithmetic error. I can't see why I could get this error. There are zeros and nulls in the table, but I do where column>0 and column is not like null|||It's because I mis-read your post the first time. You should use this function here (which casts the numeric to a string before passing it in to be cast as a datetime).

SELECT Cast(Cast(DateNumber as Varchar(8)) as datetime) FROM DateNumbers

Regards,
hmscott

Originally posted by exdter
If I use this select convert(varchar(10),convert(datetime,column_name), 101)
and put in the actual string, it works. If I try to put in the column name, I get the arithmetic error. I can't see why I could get this error. There are zeros and nulls in the table, but I do where column>0 and column is not like null|||Originally posted by exdter
If I use this select convert(varchar(10),convert(datetime,column_name), 101)
and put in the actual string, it works. If I try to put in the column name, I get the arithmetic error. I can't see why I could get this error. There are zeros and nulls in the table, but I do where column>0 and column is not like null

Well .. as i said before

quote:
------------------------

This is what I get:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

------------------------

In case you are receiving that error , there is surely some value which does not fit in into the yyyymmdd format. You will need to correct tahat first.|||I get this
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Type column_name is not a defined system type.
I used this SELECT Cast(Cast(column_name as Varchar(8)) as datetime) FROM table_name|||Originally posted by exdter
I get this
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Type column_name is not a defined system type.
I used this SELECT Cast(Cast(column_name as Varchar(8)) as datetime) FROM table_name

well lets see ...
try this

select * from your_table where ((substring(your_column,1,4) < '1753' or substring(your_column,1,4) < '9999' or substring(your_column,5,2) <'01' or substring(your_column,5,2) > '12' or substring(your_column,7,2) <'01' or substring(your_column,5,2) >'31' )

and see if you get any rows|||The column is in number form and substring works for char from.|||see if you can take a bcp out for the particular column and post it here so we can work on it|||Sorry, I don't know what bcp is.|||Run " select column_name from table_name" in Query analyzer.
Select the results ...
copy into text file and post here ..|||This is just a sample of the column. Is this ok?
20020731
19990423
19990607
19960903
19980402
20010718
19930419
20000101
19960329
19950109
20000630
19970815
20010118
20001205
19960306
19991116
19960313
19930719
19910502
20000509
20010926
20011106
20000517
19950525
19981029|||select convert(datetime, convert(varchar,20031120)) as xxx

You can replace 20031120 by the actual column name.

If you want it to be a string, you can further convert datetime into char.|||This works for this instance, but i need to do this for a whole column. If I put the column name, I get an error.
Thanks.|||exdter ...
we would need the complete data to point out where the error is ..|||I checked the data. I put a clause where column_name>0 and column_name is not null. The data that appears is all in the same format as what I posted. It goes 'yyyy/mm/dd'|||select * from your_table
where (
(substring(convert(varchar(8),your_column),1,4) < '1753'
or substring(convert(varchar(8),your_column),1,4) < '9999'
or substring(convert(varchar(8),your_column),5,2) <'01'
or substring(convert(varchar(8),your_column),5,2) > '12'
or substring(convert(varchar(8),your_column),7,2) <'01'
or substring(convert(varchar(8),your_column),7,2) >'31' )

Does this return any row ?|||Try this:

Select * from tablename where Isdate(cast(columnname as char(8))) = 0

That should help identify bad data.

blindman|||thanks blindman ...
that was exactly what i was searching for

time to get back to the holy book :)|||I put in my query 'where column_name>0'
Thanks for your help.|||If I put
set dateformat ymd
go
select cast(column_name as smalldatetime)
from table_name
where column_name =0
go

It works. But it only works on the zeros. Otherwise, if I put
and column_name>0 then I get the error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smalldatetime.|||Select * from tablename where Isdate(cast(columnname as char(8))) = 0

does this return any results...?|||It does. Thats why in my convert query I added
where column_name>0|||OK, so what about

Select *
from tablename
where Isdate(cast(column_name as char(8))) = 0
and column_name>0

blindman|||I get no results. I did:

column_name=0
column_name is null
len(column_name) !=8

and got no results for any of them
I even physically went through and looked at the dates and they were all fine.|||Originally posted by exdter
I get no results. I did:

column_name=0
column_name is null
len(column_name) !=8

and got no results for any of them
I even physically went through and looked at the dates and they were all fine.

I also ran

select isdate(column_name)
from table_name

and got results that the column can be converted to a date.|||Wow .. this has become the biggest thread of all times ...43 posts !!!

exdter ... can you post the exact query you are running on your machine that is returning an error. Also can you give the result of the query

Select * from tablename where Isdate(cast(columnname as char(8))) = 0|||For the query:

Select * from tablename where Isdate(cast(columnname as char(8))) = 0 , I get no results. Meaning there are no zeros. I picked a different table where there are no zeros.|||Originally posted by exdter
For the query:

Select * from tablename where Isdate(cast(columnname as char(8))) = 0 , I get no results. Meaning there are no zeros. I picked a different table where there are no zeros.

When I run

select convert(char(10),column_name,101)
from table_name

I just get the same string back as what is already there. The numeric string. For example 20030705
I really appreciate your help on this.

I am also looking at

select left(column_name,4) + '/' + right(column_name,2) + '/' --+ right(column_name,3)
from table_name.

I got it to look like 2002/24/ so far.|||select convert(varchar(10),convert(datetime,column_name), 101)
from table_name where isdate(cast(columnname as char(8))) = 1|||Syntax error near 'as'|||Originally posted by exdter
Syntax error near 'as'
OOPS ...

select convert(varchar(10),convert(datetime,column_name), 101)
from table_name where isdate(convert(varchar(8),columnname)) = 1|||Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

And I know that all the strings can be converted because I ran
isdate(column_name) and got all 1's.|||Can you post the ddl for the table ?

Am running out of ideas :(|||Sorry, whats the ddl?|||I mean the SQL script for creating the table.|||Ya know...

CREATE TABLE myTable99 (Col1 int, ect...

AND Sample Data...

INSERT INTO myTable99(Col, ect..
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect

Would help us a lot...|||CREATE TABLE datestimes (firmfile varchar(20),orddate int(4))
AND Sample Data...

INSERT INTO datestimes(firmfile '03000004',orddate 20030724)

Thats all it is. There are about 15000 rows and all the firmfiles are just our folder numbers. And then there is the orddate which is the date the order was put in. All the orddate data is in the format 'yyyymmdd'
I checked this.
Is this enough?
Thanks alot.|||Try zeroing in on the problem:

select convert(datetime,cast(column_name as varchar(8)))
from table_name
where column_name between 19000101 and 20040101

This will give an error. So then try:
select convert(datetime,cast(column_name as varchar(8)))
from table_name
where column_name between 19900101 and 20040101

Still get the error? Try:
select convert(datetime,cast(column_name as varchar(8)))
from table_name
where column_name between 19950101 and 20040101

Get the idea?

blindman|||I get an arithmetic error all the way up to today.|||What if you hardcode a sample value from your recordset?:

select convert(datetime,cast(20031011 as varchar(8)))
from table_name

blindman|||It works.|||How about doing a

bcp yourdatabase.ownername.datestimes out c:\datestimes.txt -c -T -a 65535

on your server at command prompt and posting the file over here.|||I can't. Its stuff that can't leave here.|||No problems mate

USE Northwind
GO

CREATE TABLE datestimes (firmfile varchar(20),orddate int)
GO

INSERT INTO datestimes(firmfile,orddate )
SELECT '03000004', 20030724
GO

Can you cut and paste that in to QA and see if it runs?

Did s/he sday that this sql server...if it's mySQL...I ougtta...

bang...zoom..|||Right to the moon, Alice...

Ok the table is made.
I still get the same results.

select convert(char(10),column_name,101)
from table_name

For this, it returns the original string.|||Is this SQL Server ? If it is , can you tell us the version

select @.@.version|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)|||Originally posted by exdter
Right to the moon, Alice...

Ok the table is made.
I still get the same results.

select convert(char(10),column_name,101)
from table_name

For this, it returns the original string.

But that's not what I posted...

Did you cut and paste what I posted in to Query Analyser?

Did it fail?

I don't believe it...

The other thing is what does DBCC CHECHTABLE(datestimes)

Tell you?|||I cut and pasted it to pubs. There were no error messages on the DBCC CHECKTABLE(datestimes). I assume you meant checKtable as you put checHtable|||Originally posted by exdter
I cut and pasted it to pubs. There were no error messages on the DBCC CHECKTABLE(datestimes). I assume you meant checKtable as you put checHtable

damn hangover...

You cut and pasted it in to Pubs...and..it worked/didn't work?

works for me...

And you did the DBCC against the table you're having the problem with correct?|||Yes to all.|||Originally posted by Brett Kaiser
You cut and pasted it in to Pubs...and..it worked/didn't work?


[beating dead horse repeatedly]
But it's not a yes or no question...
[/beating dead horse repeatedly]

[:-)]|||I cut and pasted into pubs and the table was made. When I try to do the conversions, I get the same replies as on the tables I am trying to do the conversion.|||Ok, wait...and this code...sorry

select convert(datetime,cast(orddate as varchar(8)))
from datestimes
where orddate between 19950101 and 20040101

Does that run in Pubs?|||Works like a charm. The date appears as I want it to.|||I put that on my original table and it WORKED!!! THANKS!!!!!

I see. I used this:
select convert(cast(orddate as varchar(8)))
from datestimes

You gave me this:
select convert(datetime,cast(orddate as varchar(8)))
from datestimes
I didn't have DATETIME,cast in mine.|||[smacking head with hand]
That's what blindman gave a couple of hours ago
[/smacking head with hand]

I was trying to give a whole snippet of code to run, and left off the select

Look up BETWEEN in BOL, but it basically does what it says, inclusively.

NEXT!|||...because you have bad date values either less than 19950101 or greater than 20040101. You need to find them. Try the zeroing in method again.

blindman|||Now I'm totally lost. I put the queries in that you gave me to try and zero in and they all work now. I do it without the 'between'. I'm not making this up. It doesn't matter. At least its worked out. Thanks to all.|||Or you can say in the WHERE Clause

select convert(datetime,cast(orddate as varchar(8)))
from datestimes
where orddate between 19950101 and 20040101
and ISDATE(OrdDate) = 1

NEXT?|||It works without the between now. I have no idea why. Anyway, thanks!!!|||Brett... Still need another one ?|||Voodoo and dead chickens.

DatePart Question

I have a chart that displays the number of downloads by product by week by year. I am using sql to group by DATEPART(WK, DownloadDate) and DATEPART(YY, DownloadDate). This however shows the week number (ex: this week is 49) on the x axis. I would like to show the tick marks as the Sunday and Month of that week. Example: 12/3. The year is shown under these so that part is fine. Any ideas how I might accomplish this?

I had been running into this same issue and was not able to find a solution, however I was able to work something out that does the job for me. Try the code below. Change "1" to use a day other than Sunday for the first day of the week.

Code Snippet

=Format(DateAdd("d", -(Weekday(Now()))+1, DateAdd("ww", -(DatePart("ww", Now())-DatePart("ww",DownloadDate)), Now())), "M/d/yy")

There are probably better and easier ways to do it, but it works for me. Hope this helps (even though it's a little late)!

Scott

DatePart Question

I have a chart that displays the number of downloads by product by week by year. I am using sql to group by DATEPART(WK, DownloadDate) and DATEPART(YY, DownloadDate). This however shows the week number (ex: this week is 49) on the x axis. I would like to show the tick marks as the Sunday and Month of that week. Example: 12/3. The year is shown under these so that part is fine. Any ideas how I might accomplish this?

I had been running into this same issue and was not able to find a solution, however I was able to work something out that does the job for me. Try the code below. Change "1" to use a day other than Sunday for the first day of the week.

Code Snippet

=Format(DateAdd("d", -(Weekday(Now()))+1, DateAdd("ww", -(DatePart("ww", Now())-DatePart("ww",DownloadDate)), Now())), "M/d/yy")

There are probably better and easier ways to do it, but it works for me. Hope this helps (even though it's a little late)!

Scott

datepart problem with week extraction (T-SQL)

I'm using datepart combined with a count aggregate to count the number of
ws in a certain time period. Problem is, my employer starts each w on
Saturday. The T-SQL version of Datepart does not support a StartOfW
parameter. This defect is screwing up my reports.
Does anyone have a workaround?
Thanks,
Randall ArnoldLook up SET DATEFIRST in BOL to control the first day of the w for date
functions
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
> I'm using datepart combined with a count aggregate to count the number of
> ws in a certain time period. Problem is, my employer starts each w
> on Saturday. The T-SQL version of Datepart does not support a StartOfW
> parameter. This defect is screwing up my reports.
> Does anyone have a workaround?
> Thanks,
> Randall Arnold
>|||Another alternative is to use a calendar table, whcih gives you the
flexibility of using multiple calendars associated with the same date.
Stu|||But be aware that SQL Server doesn't calculate w number the way that the
majority of the world
does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl..
.
> Look up SET DATEFIRST in BOL to control the first day of the w for date
functions
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
>|||Apparently not.
First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a stored procedure didn't result in an error, but it
had no effect, either. No matter what value I set Datefirst to, the w is
still calculated wrong. For my purposes, 4/2/2005 needs to show as w 15
(first day of w = Saturday), but it always shows as w 14.
I don't see how a date lookup table will solve this... so, any other ideas?
This shortcoming in T-SQL is producing invalid results in my datasets...
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
> But be aware that SQL Server doesn't calculate w number the way that
> the majority of the world does.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dave Frommer" <anti@.spam.com> wrote in message
> news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl...
>|||> I don't see how a date lookup table will solve this...
You have a table with one row per day. One of the columns in this table is t
he w correct number.
Or, read in Books Online about the ISOW function, install and use that in
stead of DATEPART().
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
> Apparently not.
> First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a
> stored procedure didn't result in an error, but it had no effect, either.
No matter what value I
> set Datefirst to, the w is still calculated wrong. For my purposes, 4/
2/2005 needs to show as
> w 15 (first day of w = Saturday), but it always shows as w 14.
> I don't see how a date lookup table will solve this... so, any other ideas
? This shortcoming in
> T-SQL is producing invalid results in my datasets...
> Randall Arnold
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
>|||Ok, thanks Tibor. I should have realized how the date (w) lookup would
work (I've done a similar thing for Periods), my bad. I'm just already
linking to so many tables on this query it's become a nightmare (due to bad
database design by the original dba). I'll get familiar with with ISOWEEK
and if that doesn't work, I have a Period lookup table already and I'll just
add a w column to it.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>|||I decided just to add the W column to my existing Period table. It meant
changing several queries, as well as more manual maintenance, but it works.
Thanks again.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>