Database

5-Jul-10 21:56

I think you meant to post this in site bugs & suggestions... at least that way on of the CP team will actually see it.

Re: Blatant advertising

Richard MacCutchan5-Jul-10 22:02

Richard MacCutchan

5-Jul-10 22:02

I wondered where this had gone to. If I was into drugs I'd at least have an excuse for such carelessness. D'Oh! | :doh:

It's time for a new signature.

sql function

Thanusree Duth5-Jul-10 2:28

5-Jul-10 2:28

My sql function is

ALTER FUNCTION[dbo].[FN_ATTENDANCE_REPORT_DATES] ( @EMPID varchar(50) ,@FROMDATE datetime,@TODATE

datetime)RETURNS TABLE
AS RETURN(
SELECT DR_EmployeeID AS EMPID,EM_FirstName+EM_MiddleName+EM_LastName AS EMPNAME,ED_Department AS DEPARTMENT,
ISNULL(CONVERT(VARCHAR,DR_TimeIn,103),'Not checked')AS DATEOFWORK,
SUBSTRING(CONVERT(VARCHAR,ES_TimeIn,9),14,15)+'-'+SUBSTRING(CONVERT(VARCHAR,ES_TimeOut,9),14,15)AS

SHIFTTIME,
ISNULL(SUBSTRING(CONVERT(NVARCHAR,MIN(DR_TimeIn), 22), 10, 11),'Not checked')
AS FIRSTTIMEIN,
ISNULL(SUBSTRING(CONVERT(NVARCHAR,MAX(DR_TimeOut), 22), 10, 11),'Not checked')
AS LASTTIMEOUT,
ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut)/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut )%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,DR_TimeOut)%60)),'0 Blush | :O

')AS TOTALHOURS
FROM
SG_Daily_Register INNER JOIN SG_Emp_Shift ON SG_Daily_Register.DR_ShiftID=SG_Emp_Shift.ES_ShiftID
INNER JOIN SG_Emp_Master ON SG_Daily_Register.DR_EmployeeID=SG_Emp_Master.EM_EmployeeID
INNER JOIN SG_Emp_Department ON SG_Emp_Master.EM_DeptID=SG_Emp_Department.ED_DeptID
WHERE DR_EmployeeID=@EMPID AND CONVERT(VARCHAR,DR_TimeIn,101) BETWEEN @FROMDATE AND @TODATE
GROUP BY

DR_EmployeeID,EM_FirstName+EM_MiddleName+EM_LastName,ED_Department,ES_TimeIn,ES_TimeOut,DR_TimeIn,DR_TimeOut
)

I got output as
FIRSTTIMEIN LASTTIMEOUT
2833020 PreethyS Development 02/12/2010 7:00:00:000PM-3:00:00:000AM 7:10:09 PM 9:40:45 PM 2:30:36
2833020 PreethyS Development 02/12/2010 7:00:00:000PM-3:00:00:000AM 10:20:32 PM 12:15:27 AM 1:54:55
2833020 PreethyS Development 03/12/2010 7:00:00:000PM-3:00:00:000AM 12:23:25 AM 1:24:23 AM 1 Blush | :O

:58
2833020 PreethyS Development 03/12/2010 7:00:00:000PM-3:00:00:000AM 1:32:43 AM 3:45:54 AM 2:13:11

But I have to get output as
2833020 PreethyS Development 02/12/2010 7:00:00:000PM-3:00:00:000AM 7:10:09 PM 12:15:27 AM 4:26:26
2833020 PreethyS Development 03/12/2010 7:00:00:000PM-3:00:00:000AM 12:23:25 AM 3:45:54 AM 3:14:09
pls help

Re: sql function

Simon_Whale5-Jul-10 2:52

Simon_Whale

5-Jul-10 2:52

could do with knowing the following

1. what version of SQL?
2. Sample Data
3. Tables (DDL)

As barmey as a sack of badgers

Re: sql function

J4amieC5-Jul-10 3:24

5-Jul-10 3:24

This is the 3rd question on the same topic. You should stick to 1 thread for 1 topic. Ive also pointed out the forum guidlines which ask you to use descriptive subject lines . "sql function" does not qualify as descriptive in a sql forum. The forum guidlines should also include the request to put code within formatted code blocks for ease of reading (if it doesnt it should).

Furthermore, you have not given any response to the answers youve been given below, so we have no idea if were even on the right track.

Lastly, Ive already answered this question below.

sql

Thanusree Duth4-Jul-10 23:02

4-Jul-10 23:02

I required only time in select query from datetime field in format like:
2:20:55 AM.

Pls help me

J4amieC4-Jul-10 23:58

4-Jul-10 23:58

That is presentation logic, use your presentation layer to format a datetime instance appropriately.

This should start you off:

var now = DateTime.Now;
Console.WriteLine(now.ToString("h:mm:ss"));

More info: Date / Time format strings[^]

edit: read the forum guidlines for posting messages; pay specific attention to the subject line requirements.

R. Giskard Reventlov5-Jul-10 0:22

R. Giskard Reventlov

5-Jul-10 0:22

He may have a specific (and valid) reason for needing times in his sql.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

J4amieC5-Jul-10 0:41

5-Jul-10 0:41

I highly doubt it. See his post below this one.

In addition, I cannot think of a valid reason to format a date, or time, into a string in sql. can you?

Mycroft Holmes5-Jul-10 0:48

5-Jul-10 0:48

I don't have a problem with the splitting of the date/time I can think of a number of valid reasons to do that in SQL. What had I missed was that he had stored the data as text, I assumed it was datetime.

Never underestimate the power of human stupidity
RAH

SQL FUNCTION USING DATE TIME

R. Giskard Reventlov5-Jul-10 0:04

R. Giskard Reventlov

5-Jul-10 0:04

There are a number of ways you can do this.

Here is one:

select convert(nvarchar, getdate(), 8) TheTime

Here is another:

select substring(convert(nvarchar, getdate(), 22), 10, 11) TheTime

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Thanusree Duth4-Jul-10 22:10

Re: SQL FUNCTION USING DATE TIME

4-Jul-10 22:10

FIRSTTIMEIN LASTTIMEOUT
7:10:09:000PM 9:40:45:000PM
10:20:32:000PM 12:15:27:000AM
From this,i will have to get only 7:10:09:000PM as FIRSTTIMEIN and 12:15:27:000AM as LASTTIMEOUT.I used min and max function.but it was not possible.help me

J4amieC4-Jul-10 23:56

Re: SQL FUNCTION USING DATE TIME

4-Jul-10 23:56

MIN and MAX are indeed the right functions to use, however, for them to work properly the data must be stored in a DATETIME field, because using strings to store those times will obviously yield string-based results for min & max. There is no way to calculate the min and max times from strings representing dates.

Secondly, in order ot know that 12:15am is after 7:10pm you must also store the date date part along with the time part.

Here is a simple test script that shows the process (and returns the results you want). You'll notice ive used yesterdays date for the 3 values before midnight, and today's date for the one after midnight.

WITH data(firstTimeIn,lastTimeOut )
AS
(
	SELECT CAST('4 july 2010 7:10:09:000PM' AS DATETIME),CAST('4 july 2010 9:40:45:000PM' AS DATETIME)
	UNION 
	SELECT CAST('4 july 2010 10:20:32:000PM' AS DATETIME),CAST('5 july 2010 12:15:27:000AM' AS DATETIME)
)
SELECT 
	MIN(firstTimeIn) as firstTimeIn,
	MAX(lastTimeOut) as lastTimeOut 
FROM data

Result:
firstTimeIn: 2010-07-04 19:10:09.000
lastTimeOut: 2010-07-05 00:15:27.000

JasonShort5-Jul-10 6:10

JasonShort

5-Jul-10 6:10

If at all possible don't use that string format. It will break in other locales.

'2010-07-04 22:20:32:00' is the better format. It is always parsed correctly no matter what the locale.

Jason S Short, Ph.D.
VistaDB Software, Inc.

Re: SQL FUNCTION USING DATE TIME

J4amieC5-Jul-10 6:30

5-Jul-10 6:30

JasonShort wrote:
It is always parsed correctly no matter what the locale.

You don't say! Roll eyes | :rolleyes:

I was providing test data for the OP, which incidentally will parse in all locales as the month names are fully qualified.

stored procedure

Thanusree Duth4-Jul-10 18:47

4-Jul-10 18:47

SELECT DISTINCT DATEOFWORKS,EMPNAME,DEPARTMENT,SHIFTTIME,FIRSTTIMEIN,LASTTIMEOUT,WORKINGHOURS,BREAKDURATION
from [dbo].[fn_emp_Workdetails]('2833409','9/25/2010','9/26/2010')INNER JOIN
[dbo].[fn_FirstTimeIn_LasttimeOut] ('2833409','9/25/2010','9/26/2010') ON
[dbo].[fn_emp_Workdetails].EMPID=[dbo].[fn_FirstTimeIn_LasttimeOut].EMPLID
INNER JOIN [dbo].[fn__Emp_Working_Hrs]('2833409','9/25/2010','9/26/2010') ON [dbo].[fn_emp_Workdetails].EMPID=
[dbo].[fn__Emp_Working_Hrs].EMPLOYEE_ID INNER JOIN [dbo].[fn__Break_Duration]('2833409','9/25/2010','9/26/2010')
ON [dbo].[fn_emp_Workdetails].EMPID=[dbo].[fn__Break_Duration].EMPLOY_ID

In this stored procedure,the output is

25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 2:15:17 0:21:-24
25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 2:15:17 0 Blush | :O

25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 2:15:17 0:21:-24
25/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 5:12:24 0 Blush | :O

26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 2:15:17 0 Blush | :O

26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 5:12:24 0 Blush | :O

26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 7:12:09:000PM 12:45:09:000AM 5:12:24 0:21:-24
26/09/2010 NithaV.G Development 7:00:00:000PM- 3:00:00:000AM 12:59:59:000AM 3:15:16:000AM 5:12:24 0:21:-24

The same row appear several times.Please help me to select one row only one times.By using 'distinct' it wouldnt become correct..pls help

Re: stored procedure

Mycroft Holmes4-Jul-10 19:09

Dynamic SQL and the Crystal Report

4-Jul-10 19:09

Use dateparts to remove the time component of the fields with time values.

You can also do a double convert > varchar using a formater convert(varchar(20),date,103), then convert the result back to datetime format and use that in the report

Never underestimate the power of human stupidity
RAH

Linked Server

John.L.Ponratnam4-Jul-10 16:49

John.L.Ponratnam

4-Jul-10 16:49

Is there any way to link two server other than using OPENQUERY.

indian1433-Jul-10 19:10

indian143

3-Jul-10 19:10

Hi All,

I have a stored proceudure which has dynamic sql in it. This stored procedure has be accessed in a crystal report. Crystal report expert is not showing the Stored Procedures columns. Please anybody advice me how to show the columns of a stored procedure which uses the Dynamic SQL.

Thanks in advance.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Re: Dynamic SQL and the Crystal Report

Mycroft Holmes3-Jul-10 20:38

I want to know that which is better to save a graph either XAML or SQL databse.

3-Jul-10 20:38

I think you will find the MS discovery will also not get and fields either. I'm not sure (never read up on it) just what magic they use to get the columns but it cannot work with dynamic sql. This seems perfectly reasonable to me as dynamic sql allows you to change the column names - 1 dead report.

Usually dynamic sql indicates a change of underlying object, database, server, table or column structure. Unless it is columns then just dummy up a proc for design purposes.

Never underestimate the power of human stupidity
RAH

hotthoughtguy2-Jul-10 20:59

hotthoughtguy

2-Jul-10 20:59

Graph is map having rooms, house, roads etc.(i.e edges and nodes to find shortest path).

Re: I want to know that which is better to save a graph either XAML or SQL databse.

Mycroft Holmes2-Jul-10 22:39