Hi
I have a table with dates stored in a nvarchar(75), I would like to move
this over to datetime (for obvious reasons)
the problem is the field has some 'bad data'
how can i query the nvarchar field to return me anything that isnt a date?
so i can fix and convert
Thanks in advance
MikeMike
Take a look at ISDATE () function
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
> Hi
> I have a table with dates stored in a nvarchar(75), I would like to move
> this over to datetime (for obvious reasons)
> the problem is the field has some 'bad data'
> how can i query the nvarchar field to return me anything that isnt a date?
> so i can fix and convert
> Thanks in advance
> Mike
>|||Uri,
I have been looking at that all morning and trying to understand it fully.
using the following query
SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
this returns a 1 or 0
the problem with ISDATE() is that it seems to rely on an american date
format
so 13/02/2003 is invalid whereas 02/13/2003 is valid
being in the UK the function appears to be as much use as a chocolate fire
guard :)
Thanks
Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
> Mike
> Take a look at ISDATE () function
>
> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>|||the UK problem can be fixed by using
SET DATEFORMAT dmy;
- got it working now
Thanks again
Mike
"Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
news:UpudnVw4esuFQG3anZ2dnUVZ8v6dnZ2d@.giganews.com...
> Uri,
> I have been looking at that all morning and trying to understand it fully.
> using the following query
> SELECT MyDate, ISDATE(CommissionDate) AS Expr1 FROM MyTable
> this returns a 1 or 0
> the problem with ISDATE() is that it seems to rely on an american date
> format
> so 13/02/2003 is invalid whereas 02/13/2003 is valid
> being in the UK the function appears to be as much use as a chocolate fire
> guard :)
> Thanks
> Mike
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OTLvllykIHA.5660@.TK2MSFTNGP02.phx.gbl...
>> Mike
>> Take a look at ISDATE () function
>>
>> "Mike Fellows" <mike.fellows@.equityhouse.NO.SPAM.co.uk> wrote in message
>> news:KeadnZwJPKHNSW3anZ2dneKdnZydnZ2d@.giganews.com...
>> Hi
>> I have a table with dates stored in a nvarchar(75), I would like to move
>> this over to datetime (for obvious reasons)
>> the problem is the field has some 'bad data'
>> how can i query the nvarchar field to return me anything that isnt a
>> date? so i can fix and convert
>> Thanks in advance
>> Mike
>>
>
Showing posts with label dates. Show all posts
Showing posts with label dates. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Queries with Dates
New to SQL.
I'm trying to include 2 dates in a query. They query works, but not
logically. I'm sure I'm missing something small.
When the query runs, it doesn't include dates that have times later than
12:00:00AM. How do I get rid of that?
SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate,
dbo.Products.ProductName
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID =
dbo.Products.ProductID
WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
@.ordDate2)This example should demonstrate.
DECLARE @.ordDate datetime
DECLARE @.ordDate2 datetime
SET @.ordDate '20060531' -- As no time is specified this will be interpreted
as '20060531 00:00'
SET @.ordDate2 '20060531 23:59:59'
To capture all data on the 31-May-2006 your WHERE clause would be :-
WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2
HTH. Ryan
"Tony K" <king-tony2@.comcast.nospam.net> wrote in message
news:O%23IUoRFhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> New to SQL.
> I'm trying to include 2 dates in a query. They query works, but not
> logically. I'm sure I'm missing something small.
> When the query runs, it doesn't include dates that have times later than
> 12:00:00AM. How do I get rid of that?
> SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID,
> dbo.Orders.OrderDate, dbo.Products.ProductName
> FROM dbo.Orders INNER JOIN
> dbo.[Order Details] ON dbo.Orders.OrderID =
> dbo.[Order Details].OrderID INNER JOIN
> dbo.Products ON dbo.[Order Details].ProductID =
> dbo.Products.ProductID
> WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
> @.ordDate2)
>|||>SET @.ordDate2 '20060531 23:59:59'
To be strict about it there is a very small chance this will not work
because it ignores the milliseconds. But you can not simply add .999
to the end. Consider this query and results:
select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
select 999, convert(datetime,'20060531 23:59:59.999')
order by 1
-- --
990 2006-05-31 23:59:59.990
991 2006-05-31 23:59:59.990
992 2006-05-31 23:59:59.993
993 2006-05-31 23:59:59.993
994 2006-05-31 23:59:59.993
995 2006-05-31 23:59:59.997
996 2006-05-31 23:59:59.997
997 2006-05-31 23:59:59.997
998 2006-05-31 23:59:59.997
999 2006-06-01 00:00:00.000
As you can see, SQL Server doesn't quite work in milliseconds, and
23:59:59.999 resolves to the NEXT DAY. However, you have four choices
for the largest value for the same day, .995 through .998.
Roy Harvey
Beacon Falls, CT
On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
<Ryan_Waight@.nospam.hotmail.com> wrote:
>This example should demonstrate.
>DECLARE @.ordDate datetime
>DECLARE @.ordDate2 datetime
>SET @.ordDate '20060531' -- As no time is specified this will be interpreted
>as '20060531 00:00'
>SET @.ordDate2 '20060531 23:59:59'
>To capture all data on the 31-May-2006 your WHERE clause would be :-
>WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2|||Valid point, well made.
HTH. Ryan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
> To be strict about it there is a very small chance this will not work
> because it ignores the milliseconds. But you can not simply add .999
> to the end. Consider this query and results:
> select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
> select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
> select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
> select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
> select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
> select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
> select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
> select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
> select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
> select 999, convert(datetime,'20060531 23:59:59.999')
> order by 1
> -- --
> 990 2006-05-31 23:59:59.990
> 991 2006-05-31 23:59:59.990
> 992 2006-05-31 23:59:59.993
> 993 2006-05-31 23:59:59.993
> 994 2006-05-31 23:59:59.993
> 995 2006-05-31 23:59:59.997
> 996 2006-05-31 23:59:59.997
> 997 2006-05-31 23:59:59.997
> 998 2006-05-31 23:59:59.997
> 999 2006-06-01 00:00:00.000
> As you can see, SQL Server doesn't quite work in milliseconds, and
> 23:59:59.999 resolves to the NEXT DAY. However, you have four choices
> for the largest value for the same day, .995 through .998.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
> <Ryan_Waight@.nospam.hotmail.com> wrote:
>|||All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...
ime.asp
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eiAcoeKhGHA.896@.TK2MSFTNGP02.phx.gbl...
> Valid point, well made.
> --
> HTH. Ryan
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
>|||On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...br />
time.asp
>
>:-)
Very nice. I've got to catch up on all the articles you guys wrote
since I was last around!
Roy|||Thank you everybody. I'll look at the website and write back if I have
other issues.
Thank you everybody!
Tony
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6t2s729hu1bdn521lbur5qea0ia4ct55a8@.
4ax.com...
> On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Very nice. I've got to catch up on all the articles you guys wrote
> since I was last around!
> Roy
I'm trying to include 2 dates in a query. They query works, but not
logically. I'm sure I'm missing something small.
When the query runs, it doesn't include dates that have times later than
12:00:00AM. How do I get rid of that?
SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate,
dbo.Products.ProductName
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID =
dbo.Products.ProductID
WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
@.ordDate2)This example should demonstrate.
DECLARE @.ordDate datetime
DECLARE @.ordDate2 datetime
SET @.ordDate '20060531' -- As no time is specified this will be interpreted
as '20060531 00:00'
SET @.ordDate2 '20060531 23:59:59'
To capture all data on the 31-May-2006 your WHERE clause would be :-
WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2
HTH. Ryan
"Tony K" <king-tony2@.comcast.nospam.net> wrote in message
news:O%23IUoRFhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> New to SQL.
> I'm trying to include 2 dates in a query. They query works, but not
> logically. I'm sure I'm missing something small.
> When the query runs, it doesn't include dates that have times later than
> 12:00:00AM. How do I get rid of that?
> SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID,
> dbo.Orders.OrderDate, dbo.Products.ProductName
> FROM dbo.Orders INNER JOIN
> dbo.[Order Details] ON dbo.Orders.OrderID =
> dbo.[Order Details].OrderID INNER JOIN
> dbo.Products ON dbo.[Order Details].ProductID =
> dbo.Products.ProductID
> WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
> @.ordDate2)
>|||>SET @.ordDate2 '20060531 23:59:59'
To be strict about it there is a very small chance this will not work
because it ignores the milliseconds. But you can not simply add .999
to the end. Consider this query and results:
select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
select 999, convert(datetime,'20060531 23:59:59.999')
order by 1
-- --
990 2006-05-31 23:59:59.990
991 2006-05-31 23:59:59.990
992 2006-05-31 23:59:59.993
993 2006-05-31 23:59:59.993
994 2006-05-31 23:59:59.993
995 2006-05-31 23:59:59.997
996 2006-05-31 23:59:59.997
997 2006-05-31 23:59:59.997
998 2006-05-31 23:59:59.997
999 2006-06-01 00:00:00.000
As you can see, SQL Server doesn't quite work in milliseconds, and
23:59:59.999 resolves to the NEXT DAY. However, you have four choices
for the largest value for the same day, .995 through .998.
Roy Harvey
Beacon Falls, CT
On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
<Ryan_Waight@.nospam.hotmail.com> wrote:
>This example should demonstrate.
>DECLARE @.ordDate datetime
>DECLARE @.ordDate2 datetime
>SET @.ordDate '20060531' -- As no time is specified this will be interpreted
>as '20060531 00:00'
>SET @.ordDate2 '20060531 23:59:59'
>To capture all data on the 31-May-2006 your WHERE clause would be :-
>WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2|||Valid point, well made.
HTH. Ryan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
> To be strict about it there is a very small chance this will not work
> because it ignores the milliseconds. But you can not simply add .999
> to the end. Consider this query and results:
> select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
> select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
> select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
> select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
> select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
> select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
> select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
> select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
> select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
> select 999, convert(datetime,'20060531 23:59:59.999')
> order by 1
> -- --
> 990 2006-05-31 23:59:59.990
> 991 2006-05-31 23:59:59.990
> 992 2006-05-31 23:59:59.993
> 993 2006-05-31 23:59:59.993
> 994 2006-05-31 23:59:59.993
> 995 2006-05-31 23:59:59.997
> 996 2006-05-31 23:59:59.997
> 997 2006-05-31 23:59:59.997
> 998 2006-05-31 23:59:59.997
> 999 2006-06-01 00:00:00.000
> As you can see, SQL Server doesn't quite work in milliseconds, and
> 23:59:59.999 resolves to the NEXT DAY. However, you have four choices
> for the largest value for the same day, .995 through .998.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
> <Ryan_Waight@.nospam.hotmail.com> wrote:
>|||All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...
ime.asp
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eiAcoeKhGHA.896@.TK2MSFTNGP02.phx.gbl...
> Valid point, well made.
> --
> HTH. Ryan
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
>|||On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...br />
time.asp
>
>:-)
Very nice. I've got to catch up on all the articles you guys wrote
since I was last around!
Roy|||Thank you everybody. I'll look at the website and write back if I have
other issues.
Thank you everybody!
Tony
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6t2s729hu1bdn521lbur5qea0ia4ct55a8@.
4ax.com...
> On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Very nice. I've got to catch up on all the articles you guys wrote
> since I was last around!
> Roy
Wednesday, March 7, 2012
q; past and future date
How should I find the dates for 3 days past and 5 days future. Such as
TodayDate-3 and TodayDate+5 base in the date only, discarding the time?One method:
SELECT SomeData
FROM dbo.MyTable
WHERE
MyDate >= DATEADD(dd, -3, DATEDIFF(dd, 0, GETDATE())) AND
MyDate < DATEADD(dd, 6, DATEDIFF(dd, 0, GETDATE()))
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0A94FA08-3A91-4F0C-9117-0721F782E7E3@.microsoft.com...
> How should I find the dates for 3 days past and 5 days future. Such as
> TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
TodayDate-3 and TodayDate+5 base in the date only, discarding the time?One method:
SELECT SomeData
FROM dbo.MyTable
WHERE
MyDate >= DATEADD(dd, -3, DATEDIFF(dd, 0, GETDATE())) AND
MyDate < DATEADD(dd, 6, DATEDIFF(dd, 0, GETDATE()))
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0A94FA08-3A91-4F0C-9117-0721F782E7E3@.microsoft.com...
> How should I find the dates for 3 days past and 5 days future. Such as
> TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
q; past and future date
How should I find the dates for 3 days past and 5 days future. Such as
TodayDate-3 and TodayDate+5 base in the date only, discarding the time?One method:
SELECT SomeData
FROM dbo.MyTable
WHERE
MyDate >= DATEADD(dd, -3, DATEDIFF(dd, 0, GETDATE())) AND
MyDate < DATEADD(dd, 6, DATEDIFF(dd, 0, GETDATE()))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0A94FA08-3A91-4F0C-9117-0721F782E7E3@.microsoft.com...
> How should I find the dates for 3 days past and 5 days future. Such as
> TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
TodayDate-3 and TodayDate+5 base in the date only, discarding the time?One method:
SELECT SomeData
FROM dbo.MyTable
WHERE
MyDate >= DATEADD(dd, -3, DATEDIFF(dd, 0, GETDATE())) AND
MyDate < DATEADD(dd, 6, DATEDIFF(dd, 0, GETDATE()))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0A94FA08-3A91-4F0C-9117-0721F782E7E3@.microsoft.com...
> How should I find the dates for 3 days past and 5 days future. Such as
> TodayDate-3 and TodayDate+5 base in the date only, discarding the time?
Saturday, February 25, 2012
q; Compare Two Dates
I need to compare tow dates DateField1 and DateField2 and find number of
hours between these two dates. Then I need to deduct non-business days and
hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from
this and find net hours. How can I do this?
Hello,
Take a look into DATEDIFF function
Thanks
Hari
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:37184CE4-A112-4034-B464-59909C0FAB2A@.microsoft.com...
>I need to compare tow dates DateField1 and DateField2 and find number of
> hours between these two dates. Then I need to deduct non-business days and
> hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm)
> from
> this and find net hours. How can I do this?
hours between these two dates. Then I need to deduct non-business days and
hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from
this and find net hours. How can I do this?
Hello,
Take a look into DATEDIFF function
Thanks
Hari
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:37184CE4-A112-4034-B464-59909C0FAB2A@.microsoft.com...
>I need to compare tow dates DateField1 and DateField2 and find number of
> hours between these two dates. Then I need to deduct non-business days and
> hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm)
> from
> this and find net hours. How can I do this?
q; Compare Two Dates
I need to compare tow dates DateField1 and DateField2 and find number of
hours between these two dates. Then I need to deduct non-business days and
hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from
this and find net hours. How can I do this?Hello,
Take a look into DATEDIFF function
Thanks
Hari
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:37184CE4-A112-4034-B464-59909C0FAB2A@.microsoft.com...
>I need to compare tow dates DateField1 and DateField2 and find number of
> hours between these two dates. Then I need to deduct non-business days and
> hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm)
> from
> this and find net hours. How can I do this?
hours between these two dates. Then I need to deduct non-business days and
hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from
this and find net hours. How can I do this?Hello,
Take a look into DATEDIFF function
Thanks
Hari
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:37184CE4-A112-4034-B464-59909C0FAB2A@.microsoft.com...
>I need to compare tow dates DateField1 and DateField2 and find number of
> hours between these two dates. Then I need to deduct non-business days and
> hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm)
> from
> this and find net hours. How can I do this?
q; Compare Two Dates
I need to compare tow dates DateField1 and DateField2 and find number of
hours between these two dates. Then I need to deduct non-business days and
hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from
this and find net hours. How can I do this?Hello,
Take a look into DATEDIFF function
Thanks
Hari
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:37184CE4-A112-4034-B464-59909C0FAB2A@.microsoft.com...
>I need to compare tow dates DateField1 and DateField2 and find number of
> hours between these two dates. Then I need to deduct non-business days and
> hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm)
> from
> this and find net hours. How can I do this?
hours between these two dates. Then I need to deduct non-business days and
hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from
this and find net hours. How can I do this?Hello,
Take a look into DATEDIFF function
Thanks
Hari
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:37184CE4-A112-4034-B464-59909C0FAB2A@.microsoft.com...
>I need to compare tow dates DateField1 and DateField2 and find number of
> hours between these two dates. Then I need to deduct non-business days and
> hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm)
> from
> this and find net hours. How can I do this?
Q:Data file/log file growth
Two questions:
1. How can I know how many times and what dates etc a database file with
automatic growth either in percent or in megabyte has grown?
2. If I specify to automaticly grow by 10 percent. Will the file then each
time grow with 10 percent of the original file size (when created) or with
10 percent of the current size?
/Peter| Two questions:
|
| 1. How can I know how many times and what dates etc a database file with
| automatic growth either in percent or in megabyte has grown?
--
You can capture a profiler trace which includes the Data file autogrow
event.
|
| 2. If I specify to automaticly grow by 10 percent. Will the file then each
| time grow with 10 percent of the original file size (when created) or with
| 10 percent of the current size?
--
From SQL Server Books Online:
"When % is specified, the growth increment size is the specified percentage
of the size of the file at the time the increment occurs. If FILEGROWTH is
not specified, the default value is 10 percent and the minimum value is 64
KB. The size specified is rounded to the nearest 64 KB."
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Ok, but I cant "go back" to look for a specific database for how many times
etc the database file has expanded?
E.g. if i don´t would like to run profiler all the time?
/Peter
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:9nx2KpTxDHA.424@.cpmsftngxa07.phx.gbl...
> | Two questions:
> |
> | 1. How can I know how many times and what dates etc a database file with
> | automatic growth either in percent or in megabyte has grown?
> --
> You can capture a profiler trace which includes the Data file autogrow
> event.
> |
> | 2. If I specify to automaticly grow by 10 percent. Will the file then
each
> | time grow with 10 percent of the original file size (when created) or
with
> | 10 percent of the current size?
> --
> From SQL Server Books Online:
> "When % is specified, the growth increment size is the specified
percentage
> of the size of the file at the time the increment occurs. If FILEGROWTH is
> not specified, the default value is 10 percent and the minimum value is 64
> KB. The size specified is rounded to the nearest 64 KB."
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>|||> Ok, but I cant "go back" to look for a specific database for how many times
> etc the database file has expanded?
No, that information is not recorded and stored automatically. If you want to do this at a periodic
bases, I recommend that you run a job with a certain interval which check if size has increased and
run that job periodically.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter" <Peter.nospam@.news.com> wrote in message news:OQidQnWxDHA.2000@.TK2MSFTNGP11.phx.gbl...
> Ok, but I cant "go back" to look for a specific database for how many times
> etc the database file has expanded?
> E.g. if i don´t would like to run profiler all the time?
> /Peter
>
> "Eric Cardenas" <ecard@.anonymous.com> wrote in message
> news:9nx2KpTxDHA.424@.cpmsftngxa07.phx.gbl...
> > | Two questions:
> > |
> > | 1. How can I know how many times and what dates etc a database file with
> > | automatic growth either in percent or in megabyte has grown?
> > --
> > You can capture a profiler trace which includes the Data file autogrow
> > event.
> >
> > |
> > | 2. If I specify to automaticly grow by 10 percent. Will the file then
> each
> > | time grow with 10 percent of the original file size (when created) or
> with
> > | 10 percent of the current size?
> > --
> > From SQL Server Books Online:
> >
> > "When % is specified, the growth increment size is the specified
> percentage
> > of the size of the file at the time the increment occurs. If FILEGROWTH is
> > not specified, the default value is 10 percent and the minimum value is 64
> > KB. The size specified is rounded to the nearest 64 KB."
> >
> > Hope this helps,
> >
> > --
> > Eric Cárdenas
> > SQL Server support
> >
>|||> Ok, but I cant "go back" to look for a specific database for how many
times
> etc the database file has expanded?
No, as others have mentioned, SQL Server doesn't track this information.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
1. How can I know how many times and what dates etc a database file with
automatic growth either in percent or in megabyte has grown?
2. If I specify to automaticly grow by 10 percent. Will the file then each
time grow with 10 percent of the original file size (when created) or with
10 percent of the current size?
/Peter| Two questions:
|
| 1. How can I know how many times and what dates etc a database file with
| automatic growth either in percent or in megabyte has grown?
--
You can capture a profiler trace which includes the Data file autogrow
event.
|
| 2. If I specify to automaticly grow by 10 percent. Will the file then each
| time grow with 10 percent of the original file size (when created) or with
| 10 percent of the current size?
--
From SQL Server Books Online:
"When % is specified, the growth increment size is the specified percentage
of the size of the file at the time the increment occurs. If FILEGROWTH is
not specified, the default value is 10 percent and the minimum value is 64
KB. The size specified is rounded to the nearest 64 KB."
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Ok, but I cant "go back" to look for a specific database for how many times
etc the database file has expanded?
E.g. if i don´t would like to run profiler all the time?
/Peter
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:9nx2KpTxDHA.424@.cpmsftngxa07.phx.gbl...
> | Two questions:
> |
> | 1. How can I know how many times and what dates etc a database file with
> | automatic growth either in percent or in megabyte has grown?
> --
> You can capture a profiler trace which includes the Data file autogrow
> event.
> |
> | 2. If I specify to automaticly grow by 10 percent. Will the file then
each
> | time grow with 10 percent of the original file size (when created) or
with
> | 10 percent of the current size?
> --
> From SQL Server Books Online:
> "When % is specified, the growth increment size is the specified
percentage
> of the size of the file at the time the increment occurs. If FILEGROWTH is
> not specified, the default value is 10 percent and the minimum value is 64
> KB. The size specified is rounded to the nearest 64 KB."
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>|||> Ok, but I cant "go back" to look for a specific database for how many times
> etc the database file has expanded?
No, that information is not recorded and stored automatically. If you want to do this at a periodic
bases, I recommend that you run a job with a certain interval which check if size has increased and
run that job periodically.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter" <Peter.nospam@.news.com> wrote in message news:OQidQnWxDHA.2000@.TK2MSFTNGP11.phx.gbl...
> Ok, but I cant "go back" to look for a specific database for how many times
> etc the database file has expanded?
> E.g. if i don´t would like to run profiler all the time?
> /Peter
>
> "Eric Cardenas" <ecard@.anonymous.com> wrote in message
> news:9nx2KpTxDHA.424@.cpmsftngxa07.phx.gbl...
> > | Two questions:
> > |
> > | 1. How can I know how many times and what dates etc a database file with
> > | automatic growth either in percent or in megabyte has grown?
> > --
> > You can capture a profiler trace which includes the Data file autogrow
> > event.
> >
> > |
> > | 2. If I specify to automaticly grow by 10 percent. Will the file then
> each
> > | time grow with 10 percent of the original file size (when created) or
> with
> > | 10 percent of the current size?
> > --
> > From SQL Server Books Online:
> >
> > "When % is specified, the growth increment size is the specified
> percentage
> > of the size of the file at the time the increment occurs. If FILEGROWTH is
> > not specified, the default value is 10 percent and the minimum value is 64
> > KB. The size specified is rounded to the nearest 64 KB."
> >
> > Hope this helps,
> >
> > --
> > Eric Cárdenas
> > SQL Server support
> >
>|||> Ok, but I cant "go back" to look for a specific database for how many
times
> etc the database file has expanded?
No, as others have mentioned, SQL Server doesn't track this information.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Subscribe to:
Posts (Atom)