how to get Time time difference in sql?

Could any one please tell me how to find time difference between two dates?

i have two fields in database as datetime data type.i need to get time difference between this two fields.how to do that?

i use this one

SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

but it giving me results as

1.00000000

1.250000

3.00000

i jus want it to be

1

1.25

3

How to do this?

Thanks for any help.

To find the time difference in days, hours, minutes. Try it, you can replace the dates with you column datetime.

selectDATEDIFF(day,2007-11-30,2007-11-20)AS NumberOfDays,DATEDIFF(hour,2007-11-30,2007-11-20)AS NumberOfHours,DATEDIFF(minute,2007-11-30,2007-11-20)AS NumberOfMinutesFROM test_table

|||

hi, thanks for the reply i dint useDATEDIFF(hour,2007-11-30,2007-11-20) this option jus because

i have one data as 10:45 and another as 12:00 it gives me time diff as 2 in hrs but actual diff is only 1:15 min thats y im convertin to min first and dividing it by 60.

but my outputs are like 1.02345,2.4567

i jus need to be like 1, 2.45

|||

Tweety@.net:

SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

simple...

just write this query as...

SELECT outdate, ROUND(cast((datediff(mi, outtime, intime) / 60.0) as FLOAT),2) AS TimeUtilized FROM breaktime

and it should work.

hope this helps./.

|||

Try casting the result to decimal data type. The syntax is cast ( <number> as decimal ( 5 , 2 ) ).

sql

How to get the system date and time into database

Hi,

I m using ASP.NET with C#.
I m having one field which should store the datetime of the system.

The datetime should be automatically stored for that entry when the user submits that record on the click event.
Then it should display the date time on the gridview from database.

I m using sqlserver 2005 and i have created the stored procedure for the insert command.

Thisis the sample sp what should be written here to insert system date timeautomatically when the user submits the asp.net form ?

Is there any code for writing directly in stored procedure or asp.net coding page...

ALTER PROCEDURE [dbo].[StoredProcedure1]
@.salesid INT OUTPUT,
@.salesdate datetime,
@.customername varchar(20)
AS
BEGIN
SET NOCOUNT ON
BEGIN
 INSERT INTO sales (customername)
 VALUES (@.customername)
 SELECT @.companyid = SCOPE_IDENTITY()
END
SET NOCOUNT OFF
END

Thanxs in advance...

Hi,

use getdate() function of sql server.

for more info look here

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

|||

You have a choice as to where to use the getdate() function (which returns the current system date and time).

You can actually define it on a column in the table itself so that you don't have to set it programmatically. Here is sample syntax:

create table sales (sales_id int identity, customer_name varchar(30) not null,sales_date datetime default getdate() not null)

Alternatively, within the stored procedure assign your variable with the function call. Here is sample syntax:

select @.SystemDate = getdate()

How to get the date part of Datetime

Is there any other way which is more simpler(shorter) to get the date
part (I don't want the time part ) of Datetime than this ?

Convert(nvarchar, DATEPART(dd,[Date]) ) + '/' + Convert(nvarchar,
DATEPART(mm,[Date])) + '/' + Convert(nvarchar, DATEPART(yy,[Date])) As
[Date]> Is there any other way which is more simpler(shorter) to get the date
> part (I don't want the time part ) of Datetime than this ?
> Convert(nvarchar, DATEPART(dd,[Date]) ) + '/' + Convert(nvarchar,
> DATEPART(mm,[Date])) + '/' + Convert(nvarchar, DATEPART(yy,[Date])) As
> [Date]

convert(char(10),getdate(),120)
http://www.karaszi.com/SQLServer/info_datetime.asp

How to get the current date in C# and how do I pass this date to SQL Server

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

you can get the current date directly in SQL using getdate()|||Doing it in C# will return you the client system date. Getting the current date in SQL will ensure all datetime recorded in your application are in sync. If you are using the date for some sort of comparison determine the sequence of event, using the date from the client might pose some timing issue. Unless what you want is really the client's local time, you should use the Server time.|||

brgdotnet:

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

to get current date in c# use DateTime.Now.ToString()......... there is also some different methods under Now... use what is appropriate for u .

if u r using a procedure it is easier.... say proc. name saveCurrentDate

String s=DateTime.Now.ToString();

String queryString="saveCurrentDate '"+s+"'"; //// or String queryString="insert int myTable(mydate) values ('"+s+"')";

hope it will hellped u

|||

Actually I am processing some records from a comma delimited .txt file. Each item seperated by a comma, will map to a field in a SQL Server database table.

The date is in the format: 20070423, that is YYMMDD

So I need to take the date value and then write it into SQL server. So this brings us to another question. Do I need to change this date format to comply with that of SQL server? If so what format does it need to be in? I need some specifics so if you could even produce a code sample for C#, that would be great.

|||You can pass in the date in format YYYYMMDD as a string. SQL Server will implicitly convert it to string. As long as you are using Universal format YYYYMMDD, it is fine. Other format like MMDDYYYY or DDMMYYYY will be depending on the language setting that you used. So stick to YYYYMMDD and you will not go wrong.|||

I am confused about one thing though? I am reading from a text file where the format is in YYMMDD. Should I store this in SQL server as a data type of datetime or smalldatetime?

If so, I definitely have a date that, I can read from the text file, but I don't have a time? What do you think? If I did use datetime, would it just append the current time to the date I entered?

|||

you should always use proper data type for the data. In this case, you should use datetime or smalldatetime to store the date.

"I can read from the text file, but I don't have a time?"
You can still use datetime data type. Just set the time to 00:00:00. For your case, as your date string is in YYYYMMDD format without time, when you insert into table, the time will be stored as 00:00:00

how to get starting datetime(monday) of the week and ending datetime of the week(sunday)

hi friends,

how to get the date of the first/last day of the week in sql...

can any one help me

Cheers,

raj

Raj,

Do clarify what would be the input.

Do u want to find the the first and last datetime of a week for an Entire Year or a month.

Or do u want to get the First and last day of a week by providing any date that falls in between.

SanDoty

|||

THANX FOR UR REPLY,

I want to get the First and last day of a week by providing CURRENT or anydate that falls in between that week

|||

This provides the beginning of the current week, and the beginning of the next week. In your query, you want rows that are >= CurrentWeek AND < NextWeek.

Declare
@.CurrentWeekStart datetime,
@.NextWeekStart datetime

SELECT
@.CurrentWeekStart = dateadd( wk, datediff( wk, 6, getdate() ), 6 ),
@.NextWeekStart = dateadd( Wk, 1, (dateadd( wk, datediff( wk, 6, getdate() ), 6 )))

SELECT @.CurrentWeekStart, @.NextWeekStart

CurrentWeek NextWeek
2007-04-29 00:00:00.000 2007-05-06 00:00:00.000

SELECT *
FROM MyTable
WHERE MyDateColumn >= @.CurrentWeekStart AND MyDateColumn < @.NextWeekStart

|||thank u Arnie..

How to get remote server datetime

Hi All ,
I have 3 sql servers located at different time zones. Say, CST,PST,EST.
Now how can I get current time at EST,PST from the SQL server located
at CST? Is there any query to do that?
I have a stored proc located in SQL server at CST zone where I need to
query for the current date/time of the other zone sql servers.
I tried below query at CST SQL server
SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
But it always gives CST datetime.
Please reply...
Thanks
RP
Haven't tested but it should work using Openquery instead of
the 4 part name. The statement passed in the openquery is
executed on the remote server.
-Sue
On Fri, 27 Apr 2007 12:24:01 -0700, Ram
<Ram@.discussions.microsoft.com> wrote:

>Hi All ,
>I have 3 sql servers located at different time zones. Say, CST,PST,EST.
>Now how can I get current time at EST,PST from the SQL server located
>at CST? Is there any query to do that?
>I have a stored proc located in SQL server at CST zone where I need to
>query for the current date/time of the other zone sql servers.
>I tried below query at CST SQL server
>SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
>But it always gives CST datetime.
>Please reply...
>Thanks
>RP
|||Hi Sue,
It works gr8...
Thanks for the help...
"Sue Hoegemeier" wrote:

> Haven't tested but it should work using Openquery instead of
> the 4 part name. The statement passed in the openquery is
> executed on the remote server.
> -Sue
> On Fri, 27 Apr 2007 12:24:01 -0700, Ram
> <Ram@.discussions.microsoft.com> wrote:
>
>
|||Hi,
Can you please write the exact syntax you used for Openquery? I also have
requirement similar to this.
Thanks for your help.
Namwar
"Ram" wrote:
[vbcol=seagreen]
> Hi Sue,
> It works gr8...
> Thanks for the help...
>
> "Sue Hoegemeier" wrote:

How to get only date component from a datetime column

Hi all,

I have a table containing a datetime column and it stores date with time ( I use GETDATE() to store the value ). For reporting purpose I need only the date component from this column without the time component. I do not want to use the datepart(yyyy, orderdt), datepart(mm, orderdt) and datepart(dd, orderdt).

Will following help

select CONVERT ( VARCHAR (10) , GetDATE(), 121 )

How to get month from a datetime type?

Hello guys,

I have datetime value as "mm/dd/yyyy h:m:s", my question is as title. help pls!

Cheers,

Elton

SUBSTRING(<your-value>, 1, 2)

-Jamie

|||use DATEPART function in expressions|||

If the source column is a date type, such as DT_DBTIMESTAMP, then you can use the date related functions, DATEPART, or there is also a more direct MONTH function.

Integration Services Expression Reference
(http://msdn2.microsoft.com/en-us/library/8b80403f-6d45-4001-8b12-25a933c663a2.aspx)

how to get first and last day (as datetimes) from a datetime value?

Hey,

In T-SQL I do this:
declare @.date datetime
set @.date = getdate()

--get first day of month
select dateadd(m, datediff(m, 0, @.date), 0)

--get last day of month
select dateadd(m, datediff(m, 0, dateadd(m, 1, @.date)), -1)

BUT when I do this is RS: for example the first day of month:

=dateadd("m", datediff("m", 0, Parameters!Date.Value), 0)

I get "#Error" displayed in the textbox. Also =datediff("m",0,Parameters!Date.Value) (the expression nested in the dateadd above) displayes the #Error message. So maybe that is the cause of failure for the whole expression.

Now my question is ... am I using the functions in RS in a wrong way? If not and it is not possible to retrieve the dates this way, is there another elegant way of doing so? (I know some ways of generating the wanted dates but they all are very messy)

Please help!

Grts

Here are some expressions that do what you are looking for. These use the DateTime methods on the actual object instead of using the VB functions.

For dermining the first day:
=Parameters!Date.Value.AddDays(-(Parameters!Date.Value.Day-1))

For dermining the last day:
=Parameters!Date.Value.AddMonths(1).AddDays(-(Parameters!Date.Value.Day))

Also, the error you were getting was most likely caused by not providing values that could be converted into DateTime objects to the DateDiff and DateAdd method. So, using these methods should work if you use DateTime objects instead 0 and -1, and convert the result of DateDiff to a DateTime.

Ian