September 22, 2009 at 6:41 am
Hi guys!
Back again!!
I have a slight issue with a join not working....
One datetime field i have is "2009-09-22 13:26:54.333" Lets Call it DT1.
The other is "2009-09-22 13:26:54.000" lets call this one DT2.
The issue i have is that i want to join T1.DT1 to T2.DT2 on the fields above which wouldn't normally be a problem if they were the same.
The issue i have is that DT2 atways records the ms as ".000" and DT1 always records them properly.
Everything up to the ms should never duplicate so what i was hoping to do was join them as follows:
JOIN inserted ON (convert(varchar(20),T1.DT1,113) = convert(varchar(20),inserted.DT2))
But the join is not working...
Any ideas??
Sam Marsden
September 22, 2009 at 1:11 pm
A couple of suggestions,
1. In the example you are using you are not specifying a format in the second convert, so you are not getting the same string returned. Fix that and you should be all set.
2. You could also do DT1 Between DT2 and DateAdd(Second, 1, DT2)
3. DT1 = DateAdd(Millsecond, 333, DT2)
4. DateAdd(millisecond, -(DatePart(millisecond, DT1)), DT1) = DT2
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2009 at 1:41 pm
Here is some code to play with as well:
declare @Date1 datetime,
@Date2 datetime;
set @Date1 = '2009-09-22 12:20:21.333';
set @Date2 = '2009-09-22 12:20:21.000';
select
@Date1,
@Date2,
dateadd(ss, datediff(ss, (dateadd(mi, datediff(mi, 0, @Date1), 0)), @Date1), (dateadd(mi, datediff(mi, 0, @Date1), 0)));
September 22, 2009 at 5:40 pm
You can also try converting dates to unix timestamp ( the number of second since midnight 1970-01-01 )/
Here is the example:
SELECT DATEDIFF(ss,'1970-01-01 00:00:00.000', )
-------------------
StarWind Software developer ( http://www.starwindsoftware.com )
September 22, 2009 at 7:09 pm
Lynn Pettis (9/22/2009)
Here is some code to play with as well:
declare @Date1 datetime,
@Date2 datetime;
set @Date1 = '2009-09-22 12:20:21.333';
set @Date2 = '2009-09-22 12:20:21.000';
select
@Date1,
@Date2,
dateadd(ss, datediff(ss, (dateadd(mi, datediff(mi, 0, @Date1), 0)), @Date1), (dateadd(mi, datediff(mi, 0, @Date1), 0)));
Good idea... but since you only really care if they match or not, you can optimize the heck out of that...
declare @Date1 datetime,
@Date2 datetime;
set @Date1 = '2009-09-22 12:20:21.333';
set @Date2 = '2009-09-22 12:20:21.000';
SELECT 'Match'
WHERE DATEDIFF(ss,@Date1,@Date2) = 0
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2009 at 1:44 am
Hi guys,
Thanks for the help, i have tried them and can't seem to get it to work.. the error i am now getting is
Msg 156, Level 15, State 1, Procedure Trig_Part_No_Nominal, Line 15
Incorrect syntax near the keyword 'SET'.
Here is my code, could someone please have a look and let me know where i am going wrong..??
USE [TESS42LIVE]
GO
/****** Object: Trigger [dbo].[Trig_Part_No_Nominal] Script Date: 09/23/2009 08:36:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Sam Marsden
-- Create date: 22/09/2009
-- Description:Trigger to fire "NET SEND" message when a part is logged / saved with no nominal code.
-- =============================================
ALTER TRIGGER [dbo].[Trig_Part_No_Nominal]
ON [dbo].[SCPart]
AFTER INSERT, UPDATE
AS
DECLARE @username varchar(10)
DECLARE @part varchar(12)
DECLARE @nominal varchar(12)
SET @username = (SELECT Connect_IP_Address FROM SCConnect JOIN inserted ON (convert(varchar(20),SCCONNECT.CONNECT_LAST_ACTION,113) = convert(varchar(20),INSERTED.PART_LAST_UPDATE,113))
SET @Part = (SELECT Part_Num FROM inserted)
SET @nominal = (SELECT Part_Nominal_Code FROM inserted)
IF @nominal IS NULL
BEGIN
SET NOCOUNT ON;
DECLARE @msg varchar(500)
SET @msg = 'net send ' + @username + ' "Part Number "'+ @part +'" Has Been Added To The Database With No Nominal Code, Please Rectify Or Escalate To Management"'
--// RUN NET SEND
exec master.dbo.xp_cmdshell @msg
END
Thanks in advance 🙂
Sam Marsden
September 23, 2009 at 3:16 am
USE [TESS42LIVE]
GO
/****** Object: Trigger [dbo].[Trig_Part_No_Nominal] Script Date: 09/23/2009 08:36:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sam Marsden
-- Create date: 22/09/2009
-- Description: Trigger to fire "NET SEND" message when a part is logged / saved with no nominal code.
-- =============================================
ALTER TRIGGER [dbo].[Trig_Part_No_Nominal]
ON [dbo].[SCPart]
AFTER INSERT, UPDATE
AS
DECLARE @username varchar(10)
DECLARE @part varchar(12)
DECLARE @nominal varchar(12)
SET @username = (SELECT Connect_IP_Address FROM SCConnect JOIN inserted ON
(
convert(varchar(20),SCCONNECT.CONNECT_LAST_ACTION,113) =
convert(varchar(20),INSERTED.PART_LAST_UPDATE,113)
)
)
SET @Part = (SELECT Part_Num FROM inserted)
SET @nominal = (SELECT Part_Nominal_Code FROM inserted)
IF @nominal IS NULL
BEGIN
SET NOCOUNT ON;
DECLARE @msg varchar(500)
SET @msg = 'net send ' + @username + ' "Part Number "'+ @part +'" Has Been Added To The Database With No Nominal Code, Please Rectify Or Escalate To Management"'
--// RUN NET SEND
exec master.dbo.xp_cmdshell @msg
END
You missed a closed bracket.
September 23, 2009 at 3:30 am
LOL i knew it would be something stupid!!
Thanks
Sam Marsden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply