How to change the default Dateformat for Database

Hi,

I want to change the default date format from 'mdy' to 'dmy'. I can do this for session by using

SET DATEFORMAT 'dmy'

but I want to this permanent on Database level (preferred) or SQL instance level how can I do this?

Thanks in advance

Try the link below for the correct SQL Server DateTime guide.

http://www.karaszi.com/SQLServer/info_datetime.asp

sql

How to Change Precision and Scale in MS SQL Server 2000

Hello,

My table was created by importing from an Excel spreadsheet. Typical fields in the rows are a date and various stock values like Open, High, Low, and Close. Unfortunately, many of the values have the wrong characteristics. These are my problems:

1. The date field has time in addition to the date. I don't want the
time in this field.

2. Many of the amount fields have a large precision, for example,
1.9399999999999999. I want to allow for a precision of 5 and a
scale of 2.

Can I make changes to my table at this point? I looked into Design Table but I don't see any feature allowing me to make changes 'on the fly'.

Any suggestions are welcome.

JoeHowdy,

Usually you can query a datetime column to extract just the date, so dont worry too much about that.

The column precision can be changed ( on the fly as it were ) using an alter table command ( see BOL ) that will automatically change the column precision and in the process round the values to what you want.

Cheers,

SG
( PS - theres nothing quite like a V8 Holden ute.....)|||Thank you for the reply. It occurs to me that one can end up creating a great many different queries if one is interested in comparing possible results/outputs. If DBA's want to save their queries for possible use later do they typically like to store them in a standard folder? Or should one create a special folder within the 'Databases' folder?

Thanks again. I am still new to working with Query Analyzer.

Joe|||I would also like to ask anyone if he or she could advise me as to how I can display only a date (without the time) when I do a query using Query Analyzer. I really don't want to see time displayed.

Can someone assist?

Thanks again.

Joe|||Howdy,

Well, sadly SQL doesnt handle splitting out dates from datetime fields very well.

Assuming you had a column called DATE in a table called INFO, if you want to display JUST the date, you need to extract the hour, min, seconds as characher values then reconstruct into a character format ( and later change to datetime , which by the way gives a defualt date of 01/01/1900)

Now, assuming you have a small table called INFO, with one column called DATE with one value of 2003-10-10 17:23:34

If you xxtract using time the following code -

select convert(varchar(2),datepart(hh,DATE))
+':'+convert(varchar(2),datepart(mm,DATE))
+':'+convert(varchar(2),datepart(ss,DATE))
from INFO

This gives -

17:23:34 ( but in varchar format).

Note too that single digit values WILL NOT have a '0' put in front of them unless you test for it & code for it accordingly.

Unless you get all dates into the same format - e.g. all varchar/char or all in datetime, mixing & matching will give you a headache.

Cheers,

SG.

How to change name of filter in Report Builder

Hi

Im creating a filter in Report Builder with a start date and an end date. That is I want the user to able to choose a dateinterval. How do I change the label shown in the report for the interval. I want it to say Startdate and Enddate not what I have in my Sql server

Thanks

/Stefan

This is not supported directly. However, you can create a custom field in your report that simply references the field you want to filter on, name the custom field whatever you want, and then filter on that. The name of the report parameter will be the name of the custom field.|||Would be nice if it did this for you when you rename a parameter in the editor, cant see the reason for this function otherwise?

How to change name of filter in Report Builder

Hi

Im creating a filter in Report Builder with a start date and an end date. That is I want the user to able to choose a dateinterval. How do I change the label shown in the report for the interval. I want it to say Startdate and Enddate not what I have in my Sql server

Thanks

/Stefan

This is not supported directly. However, you can create a custom field in your report that simply references the field you want to filter on, name the custom field whatever you want, and then filter on that. The name of the report parameter will be the name of the custom field.|||Would be nice if it did this for you when you rename a parameter in the editor, cant see the reason for this function otherwise?

How to change date formats in stored procedure

I need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.
thanks
mikeLook up CAST AND CONVERT in Books Online. But be aware that this changes the datatype to a string, and should be used for output formatting only. And it is preferable to let your interface or reporting tool handle formatting of output.
Why do you think you need to convert it to short date? Are you trying to truncate the value?|||I am inserting a date value into a table and I dont want the timestamp portion included.|||Thanks! I figured it out using the convert function|||A very similar question was answered yesterday.|||Heck, cascred, this is one of those questions that gets asked every WEEK.

musicmikem, this is a more efficient method of truncating a datatime value, if less intuitive: dateadd(d, datediff(d, 0, [YourDate]), 0)|||Weekly? Hell sometimes it's hourly|||Weekly? Hell sometimes it's hourly Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.|||Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.

You make things so complicated. Why didn't you just say that today the question will be asked at:

create table Numlist (num int identity(1,1) not null primary key)
go
insert Numlist default values
while scope_identity() < 24 insert numlist default values
go
select dateadd(hh, num, '10/4/2005') from numlist
go
drop table numlist

Bill|||Because as any good DBA knows, that method requires a Brain Scan instead of a Clock Seek.|||I'm actually in favor of the simpler:SELECT DateAdd(hour, o0 + o1 * 8, dateadd(d, datediff(d, 0, GetDate()), 0))
FROM (SELECT 0 AS o1 UNION SELECT 1 UNION SELECT 2) AS a
CROSS JOIN (SELECT 0 AS o0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS bBonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).

-PatP|||Bonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).
-PatP

I like it. I have never seen this approach before.

You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.

Bill|||You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.Gold star!

Old Unix machines (especially the DEC ones) used to do nearly everything in octal. Three full octets (00-27 octal is 0-23 decimal) will exactly hold all of the hours in a day.

-PatP|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?|||Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?

Hey. Be careful what you suggest. Things weren't pretty in the days before a relational DBMS came along. We used to do this stuff in COBOL ... without SQL! There are some scars, but not from hernias.|||try dis one.
select convert(varchar,datefield,101) from tablename

how to change date format in a select statement

when i use this command in a aspx file

"SELECT DISTINCT Format$([dbo.classgiven.classdate], 'mm/yyyy') AS monthyear,{.......................

'Format$' is not a recognized function name.

so how do i change date from mm/dd/yyyy to mm/yyyy

Check out the CAST and CONVERT functions in SQL BOL. They have a listing of all the possible combinations of formatting you can do for datetime values.|||

Hi~

Try this:

SELECTRIGHT(CONVERT(VARCHAR(10), Column_Name, 103), 7)AS [MM/YYYY]from Table_Name

Hope it helps.

How to change columns to rows

I have a need to change the columns in a table to rows with values
/****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Test] (
[UserID] [int] NULL ,
[NurseID] [int] NULL ,
[NurseID2] [int] NULL ,
[NurseID3] [int] NULL ,
[ReceptionID] [int] NULL ,
[OfficemanID] [int] NULL ,
[NurseTrainID] [int] NULL ,
[ResidentTrainID] [int] NULL ,
[ResidentTrainID2] [int] NULL ,
[ResidentTrainID3] [int] NULL
) ON [PRIMARY]
END
Insert into test
(UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
values
(1,3,9,4,7)
Select * from Test would give
UserID NurseID, NurseID2
1 3 9
and I need to transform to using SQL2000
Description Users
UserID 1
NurseID 3
NurseID2 9
I may not need the description column
Thanks for the help
Stephen K. MiyasatoHi Stephen,
2005 allows using UNPIVOT clause. Not sure about 2000 though..
http://msdn2.microsoft.com/en-us/library/ms177410.aspx
"Stephen K. Miyasato" wrote:

> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>
>|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||For SS2000, you can refer to the following.
- How to rotate a table in SQL Server
http://support.microsoft.com/defaul...kb;en-us;175574
Martin C K Poon
Senior Analyst Programmer
====================================
"Stephen K. Miyasato" <miyasat@.flex.com> bl
news:%23d67IiofGHA.4864@.TK2MSFTNGP05.phx.gbl g...
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>|||Thanks very much,
That is what I was looking for.
Stephen
"dterrie" <dterrie@.axiomadvisors.net> wrote in message
news:1148404941.573763.96380@.i39g2000cwa.googlegroups.com...
> If you were using SQL Server 2005, you could use an UNPIVOT statement.
> In this case, I think you'll just have to use a series of UNION ALL
> statements to transform the data, as in:
> select
> 'UserID' as Description,
> UserID as Users
> from test
> UNION ALL
> select
> 'NurseID' as Description,
> NurseID as Users
> from test
> UNION ALL
> select
> 'NurseID2' as Description,
> NurseID2 as Users
> from test
>

How to capture What time zone by DB Server is using?

I need to capture the time zone of the DB server and adjust some date columns in some of my tables to make sure all the dates are using PST.

My database is distributed into different smaller databases (example into laptops, PDAs etc..) around the world.

End of day I will sync all the databases in PST time zone. Is there a command in SQL to capture the timezone.

Thanks

The query below will give the offset:

select datediff(hour, GETUTCDATE(), CURRENT_TIMESTAMP)

You can map this to a table that contains the various timezones (pre-defined) and use it in your queries.

|||

Is there any way to know if the timezone supports daylight saving time? This gets the current offset, but not the timezone meta data.

|||

Thanks for the quick response. This will help me to start working on my code.

Thanks for your time.

|||Not without reading the registry or writing extended SP or calling some OS utility or SQLCLR code. But it is very easy to build a TimeZone dimension/table that contains all the predefined offsets and attributes. You can then use the obtained offset to query the TimeZone table to get the rest of the details.|||That's what I thought. Thanks!

How to capture the only "time" into the database

I've a textbox that displays the current time in this format "hh:mm:ss tt" but when it is save into the database it'll display the date and time together. So how do I save only the time into the database? My codes is as shown below:

txtTime.Text = DateTime.Now.ToLongTimeString()

Dim

parameterDateAs SqlParameter =New SqlParameter("@.Date_5",SqlDbType.DateTime)

parameterDate.Value = txtDate.Text

objCommand.Parameters.Add(parameterDate)

I've tried using Format() but it still get the same results. Can someone help me out? Thanks!

The SQL Datetime datatype will always store a date portion. as effectively all you are doing is storing a time difference since an initial base date/time.

Perhaps change your SQL datatype to varchar and store the time as a string, or else use a constant date along with your time value (IE 1/1/1900).

Dave

How to Calculate YTD on Calculated Measures

Hi ,

I am having to calculate the YTD and Twelve months to Date for calculated measures. For example, i have a calculated measure: Close Ratio=(X/Y) . So how i write an MDX to get YTD values for this calculated ratio.

Its not as simple as adding this calculated measure in the scope statement like the regular measures. It looks like For calculating close ratio -YTD[QTR4] = (X[qtr1] + X[qtr2]+ X[qtr3]+ X[qtr4] ) / (Y[QTR1] + Y[QTR2] + Y[QTR3] + Y[QTR4]) .

My requirement is to dynamically calculate YTD values for the calculated measures like ratios and % values. Can any one give me an idea how to go about writing an MDX for this?

If you have AS 2005 Enterprise Edition, you can use the Time Intelligence Wizard to generate calculations like YTD; otherwise, this article may help you craft them manually. The application of Aggregate() on a separate calculation hierarchy should enable YTD to work with a ratio calculated measure:

http://www.sqlmag.com/articles/index.cfm?articleid=46157&

>>

[June 2005]

Analysis Services 2005 Brings You Automated Time Intelligence

The Business Intelligence Wizard makes time analysis a snap

By: Mosha Pasumansky , Robert Zare |||

Do you create YTD member as Measure or as a member on Time dimension or on an utility dimension.

If you create an YTD not as Measure, you shouldn't have any problem.

For example

create member [Utility].[ytd] as Aggregate(PeriodsToDate([TimeDim].currentMember, [TimeDim].[YearLevel]), Measures.CurrentMember)

That is all.

How to calculate the total days between open and close date

Hi All,

I have a table call case and case_status have two fields, date and status as below:

date status

04/01/2006 open

04/05/2006 closed

04/10/2006 open

04/15/2006 closed

Whenever i open and closed the case, one record is insert into the case_status table.

Now I would need to calculate the total days of the case in storeprocedure.

Anyone can help me please.

Aung

This articledoes something similar. check if it helps.|||

One try:

CREATE

PROCEDURE [dbo].[caseDays]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;RETURN(SELECTSUM(datediff( dd, c.openDate, d.closedDate))as myCaseDateFROM(SELECT a.cDateas openDate, row_NUmber()over(ORDERBY a.cDate)as ROWNUMBERFROM case_statusAS aWHERE(a.status='open'))as c

inner

join(SELECT b.cDateas closedDate, row_NUmber()over(ORDERBY b.cDate)as ROWNUMBERFROM case_statusAS bWHERE(b.status='closed'))as dON c.ROWNUMBER=d.ROWNUMBER)

END

I hope this one will be close to your solution.

Limno

|||

Thanks for your responsed.

But my problem is total days in two date between open and closed. I still facing this problem.

Thanks

Aung

|||

Hello:

datediff( dd,openDate,closedDate)

This function will give you how many days between open and closed days.

If this is not what you want, give a little more details about your problem.

Limno

|||Wouldn't the table also need a CaseID field so you know what case was being opened and closed?

How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

http://msdn2.microsoft.com/en-us/library/ms145513.aspx

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

How to calculate the percentage?

In my cube, I've a date dimension and a time dimension. I would like to know how can I calculate the percentage of order count for a specific time?

I can get this.

Hour11/6/0611/7/069am601010am801011am6020

But how can I get this.

Hour11/6/0611/7/06

9am30%25%

10am40%25%

11am30%50%

I would like to use the calculation feature in available in the cube. How can I do this? Thanks!

Depends on whether the percentage is always based on the time hierarchy, regardless of which query axis it lies on, or is based on whichever hierarchy is on rows - Axis(1). In the former case, something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [TimeOfDay].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

Thanks for your reply but there are something that I don't understand. In your code, you use [TimeOfDay].Parent in the member calculation. However, in my own cube, the date dimension and time dimension are 2 separate dimensions. So, I don't expect my TimeOfDay.Parent will get the expected set.

The following is my Date and Time dimensions structure.
DimDate - The Date dimension is populated by extracting data from my OLTP.
Year
Month
DayOfMonth
Date
DayNameOfWeek

DimTime - The Time dimension is generated by cross join all the available hour, minute and second. (No. of records: 24 x 60 x 60)
Hour
Minute
Second

P.S. I'm quite new to BI and datawarehouse. If my Time hierarchy is not correct or using best practice, please let me know so that I can improve it.

Regards,
Alex|||

Hi Alex,

Are you using AS 2000 or AS 2005 - if it's AS 2000, then something like:

Member [Measures].[OrderFractionByTime] as

'[Measures].[Order Count]/

([Measures].[Order Count], [DimTime].Parent)',

FORMAT_STRING = "Percent"

|||Hi Deepak,

I'm using AS 2005. The problem that I don't understand what [DimTime].Parent is pointing to. The Time dimension and Date dimension doesn't have any direct relationship in my cube. Is there any design fault?

Regards,
Alex|||

Alex,

With AS 2005, the hierarchy should also be specified with [DimTime], so [DimTime].[TimeHierarchy].Parent points to the parent of the current [DimTime] member. For example, the parent of the hour "01" will be [DimTime].[TimeHierarchy].[All].

http://msdn2.microsoft.com/en-us/library/ms145513.aspx

>>

SQL Server 2005 Books Online

Parent (MDX)

Updated: 17 July 2006

Returns the parent of a member.

...

>>

How to calculate the midpoint

I have three fields date, low, high. I need to calculate the midpoint
of low and high and display it.

Date,Low,High
20071106,92.03,92.13
20071106,88.77,88.87
20071106,90.20,90.30
20071106,95.21,95.31
20071106,93.13,93.23
20071106,91.01,91.11On Wed, 07 Nov 2007 06:48:09 -0800, amj1020 wrote:

Quote:

Originally Posted by

>I have three fields date, low, high. I need to calculate the midpoint
>of low and high and display it.

Hi amj1020,

SELECT "date", (low + high) / 2.0 AS midpoint
FROM YourTable;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How to calculate the difference between two dates - aging

I have created a Model and using Report Builder to create a report. The
only thing I cannot get is the formula for the date difference. I need to
take an [Opened Date and Time] and the [Closed Date and Time] and get the
difference in DD:HH:MM.
Thanks in advance!Check this:
http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx
Cheers,
MB
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!|||Hi
Take a look at DATEDIFF system function in the BOL.
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!|||The DATEDIFF function can give you the difference in minutes.
Expressing that in the form DD:HH:MM is not so simple, as there is
nothing in SQL Server in that format. If all you need to do is
display it you could turn it into a string.
Calculating the three parts is not as simple as using datepart three
times. It requires a bit of arithmetic.
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
To put this into the DD:HH:MM format we could use something like:
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT CONVERT(varchar(6),DATEDIFF(minute,@.from, @.to) / (60 * 24))
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) / 60) %
24)+100,2)
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) %
60))+100,2)
--
119:02:30
The trick used to add the leading zeroes was to add 100 before
converting to a string and taking the two rightmost characters.
Hopefully that gives you something to start with.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 00:12:50 -0500, "Cliff Parker"
<cliff.parker@.stewart.com> wrote:
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
>Thanks in advance!|||On Wed, 21 Mar 2007 08:02:47 -0400, Roy Harvey wrote:
>The DATEDIFF function can give you the difference in minutes.
>Expressing that in the form DD:HH:MM is not so simple, as there is
>nothing in SQL Server in that format. If all you need to do is
>display it you could turn it into a string.
>Calculating the three parts is not as simple as using datepart three
>times. It requires a bit of arithmetic.
(snip)
Hi Roy (and Cliff),
Well, for the HH:MM part, there is an alternative: compute the
difference in minutes, add that to a starting date (any date will do) at
midnight, and convert that to string using a "time only" format:
DECLARE @.from datetime;
DECLARE @.to datetime;
SET @.from = '20060704 8:00';
SET @.to = '20061031 10:30';
SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
+ CONVERT(char(5),
DATEADD(minute,
DATEDIFF(minute, @.from, @.to),
'19000101'), -- Any date will do
108);
Results in
119:02:30
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
> + CONVERT(char(5),
> DATEADD(minute,
> DATEDIFF(minute, @.from, @.to),
> '19000101'), -- Any date will do
> 108);
Using DATEDIFF for calculating days is not always correct depending on
the times of day of the two datetimes. Try it for times on either
side of midnight, such as
SET @.from = '20060704 21:00';
SET @.to = '20060705 01:30';
and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
day calculation as:
DATEDIFF(minute,@.from, @.to) / (60 * 24)
Roy Harvey
Beacon Falls, CT|||On Wed, 21 Mar 2007 17:54:53 -0400, Roy Harvey wrote:
>On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
>> + CONVERT(char(5),
>> DATEADD(minute,
>> DATEDIFF(minute, @.from, @.to),
>> '19000101'), -- Any date will do
>> 108);
>Using DATEDIFF for calculating days is not always correct depending on
>the times of day of the two datetimes. Try it for times on either
>side of midnight, such as
>SET @.from = '20060704 21:00';
>SET @.to = '20060705 01:30';
>and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
>day calculation as:
> DATEDIFF(minute,@.from, @.to) / (60 * 24)
Hi Roy,
That was a stupid error - thanks for catching it!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Roy,
Thanks for the information. It was right on the money. You have helped me
out big time!
Many cudos!
Cliff Parker

How to calculate the difference between two dates - aging

I have created a Model and using Report Builder to create a report. The
only thing I cannot get is the formula for the date difference. I need to
take an [Opened Date and Time] and the [Closed Date and Time] and ge
t the
difference in DD:HH:MM.
Thanks in advance!Check this:
http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx
Cheers,
MB
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and g
et the
>difference in DD:HH:MM.
> Thanks in advance!|||Hi
Take a look at DATEDIFF system function in the BOL.
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and g
et the
>difference in DD:HH:MM.
> Thanks in advance!|||The DATEDIFF function can give you the difference in minutes.
Expressing that in the form DD:HH:MM is not so simple, as there is
nothing in SQL Server in that format. If all you need to do is
display it you could turn it into a string.
Calculating the three parts is not as simple as using datepart three
times. It requires a bit of arithmetic.
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
To put this into the DD:HH:MM format we could use something like:
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT CONVERT(varchar(6),DATEDIFF(minute,@.from
, @.to) / (60 * 24))
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minut
e,@.from, @.to) / 60) %
24)+100,2)
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minut
e,@.from, @.to) %
60))+100,2)
119:02:30
The trick used to add the leading zeroes was to add 100 before
converting to a string and taking the two rightmost characters.
Hopefully that gives you something to start with.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 00:12:50 -0500, "Cliff Parker"
<cliff.parker@.stewart.com> wrote:

>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and g
et the
>difference in DD:HH:MM.
>Thanks in advance!|||On Wed, 21 Mar 2007 08:02:47 -0400, Roy Harvey wrote:

>The DATEDIFF function can give you the difference in minutes.
>Expressing that in the form DD:HH:MM is not so simple, as there is
>nothing in SQL Server in that format. If all you need to do is
>display it you could turn it into a string.
>Calculating the three parts is not as simple as using datepart three
>times. It requires a bit of arithmetic.
(snip)
Hi Roy (and Cliff),
Well, for the HH:MM part, there is an alternative: compute the
difference in minutes, add that to a starting date (any date will do) at
midnight, and convert that to string using a "time only" format:
DECLARE @.from datetime;
DECLARE @.to datetime;
SET @.from = '20060704 8:00';
SET @.to = '20061031 10:30';
SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
+ CONVERT(char(5),
DATEADD(minute,
DATEDIFF(minute, @.from, @.to),
'19000101'), -- Any date will do
108);
Results in
119:02:30
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
> + CONVERT(char(5),
> DATEADD(minute,
> DATEDIFF(minute, @.from, @.to),
> '19000101'), -- Any date will do
> 108);
Using DATEDIFF for calculating days is not always correct depending on
the times of day of the two datetimes. Try it for times on either
side of midnight, such as
SET @.from = '20060704 21:00';
SET @.to = '20060705 01:30';
and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
day calculation as:
DATEDIFF(minute,@.from, @.to) / (60 * 24)
Roy Harvey
Beacon Falls, CT|||On Wed, 21 Mar 2007 17:54:53 -0400, Roy Harvey wrote:

>On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>
>Using DATEDIFF for calculating days is not always correct depending on
>the times of day of the two datetimes. Try it for times on either
>side of midnight, such as
>SET @.from = '20060704 21:00';
>SET @.to = '20060705 01:30';
>and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
>day calculation as:
> DATEDIFF(minute,@.from, @.to) / (60 * 24)
Hi Roy,
That was a stupid error - thanks for catching it!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Roy,
Thanks for the information. It was right on the money. You have helped me
out big time!
Many cudos!
Cliff Parker

How to calculate the difference between two dates - aging

I have created a Model and using Report Builder to create a report. The
only thing I cannot get is the formula for the date difference. I need to
take an [Opened Date and Time] and the [Closed Date and Time] and get the
difference in DD:HH:MM.
Thanks in advance!
Check this:
http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx
Cheers,
MB
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!
|||Hi
Take a look at DATEDIFF system function in the BOL.
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!
|||The DATEDIFF function can give you the difference in minutes.
Expressing that in the form DD:HH:MM is not so simple, as there is
nothing in SQL Server in that format. If all you need to do is
display it you could turn it into a string.
Calculating the three parts is not as simple as using datepart three
times. It requires a bit of arithmetic.
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
To put this into the DD:HH:MM format we could use something like:
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT CONVERT(varchar(6),DATEDIFF(minute,@.from, @.to) / (60 * 24))
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) / 60) %
24)+100,2)
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) %
60))+100,2)
119:02:30
The trick used to add the leading zeroes was to add 100 before
converting to a string and taking the two rightmost characters.
Hopefully that gives you something to start with.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 00:12:50 -0500, "Cliff Parker"
<cliff.parker@.stewart.com> wrote:

>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
>Thanks in advance!
|||On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
> + CONVERT(char(5),
> DATEADD(minute,
> DATEDIFF(minute, @.from, @.to),
> '19000101'), -- Any date will do
> 108);
Using DATEDIFF for calculating days is not always correct depending on
the times of day of the two datetimes. Try it for times on either
side of midnight, such as
SET @.from = '20060704 21:00';
SET @.to = '20060705 01:30';
and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
day calculation as:
DATEDIFF(minute,@.from, @.to) / (60 * 24)
Roy Harvey
Beacon Falls, CT
|||Roy,
Thanks for the information. It was right on the money. You have helped me
out big time!
Many cudos!
Cliff Parker

How to calculate a date difference in days

Suppose I have these two days fields
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM

How can i get the DateDifference of these two dates in days.

Use DateDiff(DateInterval.Day, Fields!Date1.Value, Fields!Date2.Value)

Where date1 is the start date and date2 is end date.

Shyam

|||

Hello Kamii,

If you're wanting to do this from your SQL query...

select datediff(d, ddold, ddnew)

If from Reporting Services, use this as your expression...

=DateDiff("d", Fields!ddold.Value, Fields!ddnew.Value)

Jarret

|||Can u please mark my post as answer?