Re: Date/Time querry handing ??

calderara

ok dan,

here is my store procedure


========================= STORE PROC I USED ============
ALTER PROCEDURE [dbo].[sp_CurrentEventList_ProcessAlarmOccuredEvent]
-- Add the parameters for the stored procedure here
@LineId varchar(16)
,@MachineId smallint
,@EventNb smallint
,@DateTime datetime
,@Length real
,@OnEdge bit
,@Measure1 real
,@Measure2 real
,@Measure3 real

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @NumberOfCurrentActiveAlarms int;
DECLARE @ErrorDateTime datetime;

-- Retrieve the number of current active alarms
SET @NumberOfCurrentActiveAlarms =
(
SELECT Count(*)
FROM [dbo].[CurrentEventHist]
WHERE [LineId] = @LineId
AND [MachineId] = @MachineId
AND [Number] = @EventNb
AND [OffStatus] = 0
)

-- If the alarm exists
IF ( @NumberOfCurrentActiveAlarms > 0)
BEGIN
-- Udate current Alarm
-- First Check the value of OnStatus if OnEdge = 1
IF ( @OnEdge = 1)
BEGIN
-- In that case, an alarm was already active - can't make it active again
-- Add an error to log
SET @ErrorDateTime=(SELECT GetDate())


EXEC [dbo].[sp_ErrorList_Insert]
@ErrorNumber = 1,
@Source = 'sp_ProcessAlarmOccuredEvent',
@LineId = @LineId,
@AtTime=@ErrorDateTime,
@UsrMsg='sp_ProcessAlarmOccuredEvent.1',
@Type='Information',
@SysMsg=''
END
ELSE
BEGIN
-- Update the current active alarm
UPDATE [dbo].[CurrentEventHist]
SET
[OffLen] = @Length
,[OffTime] = @DateTime
,[OffMeasurevalue1] = @Measure1
,[OffMeasureValue2] = @Measure2
,[OffMeasureValue3] = @Measure3
,[OffStatus] = 1
WHERE [LineId] = @LineId
AND [MachineId] = @MachineId
AND [Number] = @EventNb
AND [OffStatus] = 0

END
END
ELSE
-- In that bloc, the alarm doesn't exist
BEGIN
-- Insert current Alarm
-- First Check the value of OnStatus if OnEdge = 0
IF ( @OnEdge = 0)
BEGIN
-- In that case, wa can't disactive an inexistant alarm (we dropped the
active event :-(
-- Add an error to log
SET @ErrorDateTime=(SELECT GetDate())

EXEC [dbo].[sp_ErrorList_Insert]
@ErrorNumber = 2,
@Source = 'sp_ProcessAlarmOccuredEvent',
@LineId = @LineId,
@AtTime=@ErrorDateTime,
@UsrMsg = 'sp_ProcessAlarmOccuredEvent.2',
@Type='Information',
@SysMsg=''
END
ELSE
BEGIN
-- Insert new alarm
INSERT INTO [dbo].[CurrentEventHist]
(
[LineId]
,[MachineId]
,[Number]
,[OnLen]
,[OnTime]
,[OnMeasureValue1]
,[OnMeasureValue2]
,[OnMeasureValue3]
)
VALUES
(
@LineId
,@MachineId
,@EventNb
,@Length
,@DateTime
,@Measure1
,@Measure2
,@Measure3
)
END
END
END

============= END STRORE PROC ==================

if you need it as sql script file with table structure let me know I can
send it to you by email

regards
serge



"Dan Guzman" wrote:

> Can you post your stored procedure code? I'm doing more research on your
> problem and need to duplicate your situation.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "calderara" <calderara@discussions.microsoft.com> wrote in message
> news:869A03EC-523C-4269-A064-0DF6005A74E2@microsoft.com...
> > the datetime parameter pass as follow :
> >
> > sqlcommand.Parameters.Add(new SqlParameter("DateTime", Alarm.StartAt));
> >
> > Alarm.StartAt return a DateTime object
> >
> > "Dan Guzman" wrote:
> >
> >> >I pass it from application as datetime not string
> >>
> >> Can you post the application code snippet? AFAIK, a SqlDateTime
> >> parameter
> >> has no format.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "calderara" <calderara@discussions.microsoft.com> wrote in message
> >> news:B517214F-B47C-472E-AD7E-DEEC06FE13A8@microsoft.com...
> >> >I pass it from application as datetime not string
> >> >
> >> > "Jim Underwood" wrote:
> >> >
> >> >> But is the application passing them mto the database as datetime types
> >> >> or
> >> >> as
> >> >> strings, which the server then interprets? If you pass them as
> >> >> strings
> >> >> then
> >> >> you can get confusion as you described in your OP. If you pass them
> >> >> as
> >> >> dates then the database should see a date and know exactly how to
> >> >> handle
> >> >> it
> >> >> instead of having to convert the string.
> >> >>
> >> >> If you need to pass strings, for any reason, you need to make sure you
> >> >> read
> >> >> Tibor's article and follow his advice to insure proper handling of the
> >> >> dates.
> >> >>
> >> >> "calderara" <calderara@discussions.microsoft.com> wrote in message
> >> >> news:3BE2372C-58F5-44B4-873A-7027A8CAF231@microsoft.com...
> >> >> > This si what I have done, all date time parameter for my store
> >> >> > procedure
> >> >> > are
> >> >> > of type DateTime..
> >> >> >
> >> >> > "Dan Guzman" wrote:
> >> >> >
> >> >> >> To add to the other responses, consider passing stored procedure
> >> >> >> parameter
> >> >> >> values as strongly-typed SQL parameter objects from your
> >> >> >> application
> >> >> >> code
> >> >> >> instead of strings. This way, you don't need to be concerned with
> >> >> >> SQL
> >> >> >> Server datetime formats, embedded quotes, etc.
> >> >> >>
> >> >> >> --
> >> >> >> Hope this helps.
> >> >> >>
> >> >> >> Dan Guzman
> >> >> >> SQL Server MVP
> >> >> >>
> >> >> >> "calderara" <calderara@discussions.microsoft.com> wrote in message
> >> >> >> news:F9934982-D610-4E85-AB73-EB65F5338E74@microsoft.com...
> >> >> >> > Dear all,
> >> >> >> >
> >> >> >> > Let say that my client application run under an operating system
> >> >> >> > (Vista)
> >> >> >> > which is set with regionnal setting in US.Then the server that
> >> >> >> > host
> >> >> >> > the
> >> >> >> > sql
> >> >> >> > server DB has regional setting set to French. So it means that
> >> >> >> > the
> >> >> >> > source
> >> >> >> > and
> >> >> >> > the destination have different date/time format .
> >> >> >> >
> >> >> >> > From the client side I need to pass a Date/Time value to the
> >> >> >> > destination
> >> >> >> > store procedure which will extract then data base on that
> >> >> >> > date/time
> >> >> >> > parameter
> >> >> >> > So if I send the querry from the source to the destination it
> >> >> >> > will
> >> >> >> > be
> >> >> >> > something like :
> >> >> >> >
> >> >> >> > SELECT * FROM TABLE1 WHERE STARTDATE= 02/01/2007
> >> >> >> >
> >> >> >> > Under french setting format it will be in a form dd/mm/yyyy
> >> >> >> >
> >> >> >> > how the SQL server located on the destination will interprate
> >> >> >> > that
> >> >> >> > data
> >> >> >> > ?
> >> >> >> > will convert it automatically to the Server date format ?
> >> >> >> >
> >> >> >> > Because for US format it will means mm/dd/yyyy and I might not
> >> >> >> > expect
> >> >> >> > the
> >> >> >> > same data range !!!
> >> >> >> >
> >> >> >> > What is the best way to be sure that whatever the reional seting
> >> >> >> > format
> >> >> >> > from
> >> >> >> > the source, I will get the correct data from the destination
> >> >> >> > server
> >> >> >> > ??
> >> >> >> >
> >> >> >> > Note that my database Date fiedl are of type DateTime
> >> >> >> >
> >> >> >> > Thnaks for your reply
> >> >> >> > serge
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
>