multiple a number with time dataType

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » multiple a number with time dataType

multiple a number with time dataType

Rate Topic

Display Mode

Topic Options

Author

Message

vahid.arr

Posted Friday, March 22, 2013 5:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 04, 2013 12:11 PM
Points: 42, Visits: 130

i want to minus two time(time(7) datattype in sql 2008) datatype and multiple(hour and minute) it to 30.how can i do that?this just work for hour

SELECT datediff(HOUR,[EnterTime],[ExitTime] )*30 from Karmand

Post #1434201

ChrisM@Work

Posted Friday, March 22, 2013 5:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 5,608, Visits: 10,970

vahid.arr (3/22/2013)

i want to minus two time(time(7) datattype in sql 2008) datatype and multiple(hour and minute) it to 30.how can i do that?this just work for hour

SELECT datediff(HOUR,[EnterTime],[ExitTime] )*30 from Karmand

Can you post some representative sample data and the expected output from it, please? The spec is baffling.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1434211

Cadavre

Posted Friday, March 22, 2013 6:12 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

Taking a stab in the dark. . .

DECLARE @EnterTime TIME, @ExitTime TIME;

SELECT @EnterTime = '08:40:18.000', @ExitTime = '09:52:48.000'

SELECT CONVERT(VARCHAR(5), diffSeconds / 3600) + ':' + 
       RIGHT('0' + CONVERT(VARCHAR(5), diffSeconds % 3600 / 60),2) + ':' + 
       RIGHT('0' + CONVERT(VARCHAR(5), diffSeconds % 60),2)
FROM (SELECT DateDiff(s, @EnterTime, @ExitTime)
     )a(diffSeconds);

Returns "1:12:30".

Multiplying by 30 could be done like this: -

DECLARE @EnterTime TIME, @ExitTime TIME;

SELECT @EnterTime = '08:40:18.000', @ExitTime = '09:52:48.000'

SELECT CONVERT(VARCHAR(5), diffSeconds / 3600) + ':' + 
       RIGHT('0' + CONVERT(VARCHAR(5), diffSeconds % 3600 / 60),2) + ':' + 
       RIGHT('0' + CONVERT(VARCHAR(5), diffSeconds % 60),2)
FROM (SELECT DateDiff(s, @EnterTime, @ExitTime) * 30
     )a(diffSeconds);

Which returns "36:15:00".

If that doesn't help, follow Chris' advise and supply sample data and expected results based on the sample data.

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1434218

vahid.arr

Posted Friday, March 22, 2013 6:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 04, 2013 12:11 PM
Points: 42, Visits: 130

this my table with test data

CREATE TABLE [dbo].[Karmand](
	[KarmandID] [smallint] IDENTITY(100,1) NOT NULL,
	[SematID] [tinyint] NULL,
	[FName] [nvarchar](50) NOT NULL,
	[LName] [nvarchar](50) NOT NULL,
	[Sh_Sh] [decimal](11, 0) NULL,
	[FatherName] [nvarchar](50) NULL,
	[Birthday] [varchar](10) NULL,
	[Tel] [varchar](11) NULL,
	[Mobile] [varchar](50) NULL,
	[Married] [bit] NULL,
	[CodeMeli] [decimal](11, 0) NULL,
	[CodePosti] [varchar](50) NULL,
	[MoarefName] [nvarchar](50) NULL,
	[TelMoaref] [varchar](11) NULL,
	[HoghoghPaye] [int] NOT NULL,
	[SabegheKar] [smallint] NULL,
	[EstekhdamDate] [datetime] NULL,
	[ShomareBime] [nvarchar](50) NULL,
	[SabegheBime] [smallint] NULL,
	[DarsadBime] [tinyint] NULL,
	[DarsadMaliat] [tinyint] NULL,
	[EnterTime] [time](7) NULL,
	[ExitTime] [time](7) NULL,
	[Address] [nvarchar](250) NOT NULL,
	[Describtion] [nvarchar](250) NULL,
	[KarmandPic] [varbinary](max) NULL,
 CONSTRAINT [PK_Karmand] PRIMARY KEY CLUSTERED 
(
	[KarmandID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Karmand] ON
INSERT [dbo].[Karmand] ([KarmandID], [SematID], [FName], [LName], [Sh_Sh], [FatherName], [Birthday], [Tel], [Mobile], [Married], 
[CodeMeli], [CodePosti], [MoarefName], [TelMoaref], [HoghoghPaye], [SabegheKar], [EstekhdamDate], [ShomareBime], [SabegheBime], 
[DarsadBime], [DarsadMaliat], [EnterTime], [ExitTime], [Address], [Describtion], [KarmandPic]) 
VALUES (103, 2, N'وحید', N'اکبری', CAST(0 AS Decimal(11, 0)), N'شکرالله', N'1369/03/07', N'8201140', N'0', 0, CAST(0 AS Decimal(11, 0)), N'716888888', N'محمود', N'8371311', 2500000, 0, CAST(0x00009F8B00000000 AS DateTime), N'123456789', 0, 10, 3, CAST(0x0700C258884D0000 AS Time), CAST(0x0700B4284D8A0000 AS Time), N'بلوار عدالت کوچه 19 شهید آقایی پلاک 109
', N'دانشجو می باشد', NULL)
INSERT [dbo].[Karmand] ([KarmandID], [SematID], [FName], [LName], [Sh_Sh], [FatherName], [Birthday], [Tel], [Mobile], [Married], [CodeMeli], [CodePosti], [MoarefName], [TelMoaref], [HoghoghPaye], [SabegheKar], [EstekhdamDate], [ShomareBime], [SabegheBime], [DarsadBime], [DarsadMaliat], [EnterTime], [ExitTime], [Address], [Describtion], [KarmandPic]) VALUES (104, 2, N'محمود', N'رحمانی', CAST(0 AS Decimal(11, 0)), N'شکرالله', N'1391/05/18', N'8201140', N'0', 0, CAST(0 AS Decimal(11, 0)), N'716888888', N'محمود', N'8371311', 1500000, 0, CAST(0x0000A05E00000000 AS DateTime), N'123456789', 0, 10, 3, CAST(0x0700F2CFC43C0000 AS Time), CAST(0x07000E8E0B760000 AS Time), N'بلوار عدالت کوچه 19 شهید آقایی پلاک 109
', N'دانشجو می باشد', NULL)
SET IDENTITY_INSERT [dbo].[Karmand] OFF

Post #1434245

Cadavre

Posted Friday, March 22, 2013 7:31 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486

vahid.arr (3/22/2013)

this my table with test data

You've forgotten the expected results based on your sample data that was requested. You've not mentioned whether or not you tried the code I posted.

So converting my code to use your table, you'd do something like this: -

SELECT KarmandID, EnterTime, ExitTime,
CONVERT(VARCHAR(5), diffSeconds / 3600) + ':' + 
RIGHT('0' + CONVERT(VARCHAR(5), diffSeconds % 3600 / 60),2) + ':' + 
RIGHT('0' + CONVERT(VARCHAR(5), diffSeconds % 60),2) AS [Time Difference],
CONVERT(VARCHAR(5), [diffSeconds multiplied 30] / 3600) + ':' + 
RIGHT('0' + CONVERT(VARCHAR(5), [diffSeconds multiplied 30] % 3600 / 60),2) + ':' + 
RIGHT('0' + CONVERT(VARCHAR(5), [diffSeconds multiplied 30] % 60),2) AS [Time Difference Multiplied 30]
FROM (SELECT KarmandID, EnterTime, ExitTime,
      DATEDIFF(s, EnterTime, ExitTime) AS diffSeconds, 
      DATEDIFF(s, EnterTime, ExitTime) * 30 AS [diffSeconds multiplied 30]
      FROM [Karmand]
      ) a;

Which returns: -

KarmandID EnterTime        ExitTime         Time Difference Time Difference Multiplied 30
--------- ---------------- ---------------- --------------- -----------------------------
103       09:15:00.0000000 16:30:00.0000000 7:15:00         217:30:00
104       07:15:00.0000000 14:05:00.0000000 6:50:00         205:00:00

LinkedIn | Blog coming soon (for sufficiently large values of "soon" Wink

Post #1434266

« Prev Topic | Next Topic »

Permissions