datetime in in sql query

Hi

I am trying to write a query involve parameters. For example, the query:

Select * from myTable

wheremyDateTime=@.dt;

If I run the query, I was asked to enter value for the parameter. The query can be generated, however I can't save it, the error message says: Must declare the variable @.dt. When I tried to declare it, the system doesn't support it. I am using SQL Server Managerment Studio 2005.

I also tried the query without the parameter:

Select * from myTable

wheremyDateTime=31/07/2007;

But it didn't return record for any datetime format.

Could anyone help please? I just want to get some records filtered by a certain DateTime.

Claire

Are you trying to bulit it as a view or a stored procedure? Its not possible to create a View with paramters.

Stored Proc would look like:

CREATEPROCEDURE sp_MyStoredProc
@.dtasDateTime
AS

BEGIN

SELECT
*
FROM
myTable
WHERE
myDateTime=@.dt

END

To run it you wold have to execute it:

exec sp_MyStoredProc GetDate()

|||

Hi,

You will have to check how are the dates stored in your column. If they are stored as MM/dd/yyyy hh:mm:ss AMPM then you will have to use a Convert function as shown at the end of this post

For your first query, you will need to declare your variable using this

Declare @.dt datetime

Select * from myTable

wheremyDateTime=@.dt;

For your second query, if only the date is stored then

Select * from myTable

wheremyDateTime='31/07/2007'

To understand this better, try these

selectgetdate()

SELECTDATEADD(dd, 0,DATEDIFF(dd, 0,GETDATE()))

SELECTCONVERT(VARCHAR(10),GETDATE(),111)

Check this link

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

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

DateTime error in stored procedure

Hi;

I have a stored procedure simply adds userid,logintime and status to db but I have an error when running procedure can you help me please??

Create Procedure AddLog
(
@.User char(10),
@.DLogon DateTime(8),
@.Status bit
)
As
Insert Into Log(UserID,LogInTime,Online)
Values(@.User,DLogon,Status)

ERROR:

Server: Msg 128, Level 15, State 1, Procedure AddLog, Line 9
The name 'DLogon' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.You are missing a couple of @. signs. Should be:

Create Procedure AddLog
(
@.User char(10),
@.DLogon DateTime(8),
@.Status bit
)
As
Insert Into Log(UserID,LogInTime,Online)
Values(@.User,@.DLogon,@.Status)

Dates Error

Hi:

I got the next problem, when I try to modify a record of my SQL Server Database from my Delphi application the next message error appears

"Date is less than 01/12/2003"

The record that I'm trying to modify was inserted from the same applicaition.

I'm not so sure if it's a database problem, but I don't know why it is passing. What can I do?

Thaks for your help!!

Cristopher SerratoNope,

Probably someone wrote a trigger to check to rows modified date. Someone probably updated the row since you got it last..

Your update has to supply a date if I'm not mistaken...

They basically want you to requery the data so you can work with th most current version of data...

Just a guess...

Dates & Stored Procedure

Hi
I want to know how can i Pass tow Dates to Stored Procedure in sql server
2000 by using vb6.
here below my code i used northwind database ,when i run the code i got
error in the cmd.excute .
i thing my problem in passing date, can any when tell me what is the problem
Private Sub Command2_Click()
Call connect
Call setup
Dim cmd As New ADODB.Command
cmd.ActiveConnection = Con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Employee Sales by Country"
Dim parm As New ADODB.Parameter
Dim parm2 As New ADODB.Parameter
Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
"01/01/1997")
Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
"01/01/2005")
cmd.Parameters.Append parm
cmd.Parameters.Append parm2
Dim RST As New ADODB.Recordset
Set RST = cmd.Execute
Set DataGrid1.DataSource = RST
DataGrid1.ReBind
End SubWhat does "got error" mean? Could you tell us the exact error message, and
what line it corresponds to here?
On 3/20/05 11:55 PM, in article
D8DFD5BD-926C-48EA-ACA9-ED7B1743A62D@.microsoft.com, "ayman"
<ayman@.discussions.microsoft.com> wrote:

> Hi
> I want to know how can i Pass tow Dates to Stored Procedure in sql server
> 2000 by using vb6.
> here below my code i used northwind database ,when i run the code i got
> error in the cmd.excute .
> i thing my problem in passing date, can any when tell me what is the probl
em
> Private Sub Command2_Click()
> Call connect
> Call setup
>
> Dim cmd As New ADODB.Command
> cmd.ActiveConnection = Con
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "Employee Sales by Country"
> Dim parm As New ADODB.Parameter
> Dim parm2 As New ADODB.Parameter
> Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/1997")
> Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/2005")
> cmd.Parameters.Append parm
> cmd.Parameters.Append parm2
>
> Dim RST As New ADODB.Recordset
> Set RST = cmd.Execute
> Set DataGrid1.DataSource = RST
> DataGrid1.ReBind
> End Sub
>
>|||Ayman,
The parameters for the sample procedure [Employee Sales by Country] are
named @.Beginning_Date and @.Ending_Date. You have named both of
your parameters ShippedDate. Could that be the problem?
Steve Kass
Drew University
ayman wrote:

>Hi
>I want to know how can i Pass tow Dates to Stored Procedure in sql server
>2000 by using vb6.
>here below my code i used northwind database ,when i run the code i got
>error in the cmd.excute .
>i thing my problem in passing date, can any when tell me what is the proble
m
>Private Sub Command2_Click()
>Call connect
>Call setup
>
>Dim cmd As New ADODB.Command
>cmd.ActiveConnection = Con
>cmd.CommandType = adCmdStoredProc
>cmd.CommandText = "Employee Sales by Country"
>Dim parm As New ADODB.Parameter
>Dim parm2 As New ADODB.Parameter
>Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
>"01/01/1997")
>Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
>"01/01/2005")
>cmd.Parameters.Append parm
>cmd.Parameters.Append parm2
>
>Dim RST As New ADODB.Recordset
>Set RST = cmd.Execute
>Set DataGrid1.DataSource = RST
>DataGrid1.ReBind
>End Sub
>
>
>|||ayman
Have you tried to format the dates as 'YYYYMMDD'?
"ayman" <ayman@.discussions.microsoft.com> wrote in message
news:D8DFD5BD-926C-48EA-ACA9-ED7B1743A62D@.microsoft.com...
> Hi
> I want to know how can i Pass tow Dates to Stored Procedure in sql server
> 2000 by using vb6.
> here below my code i used northwind database ,when i run the code i got
> error in the cmd.excute .
> i thing my problem in passing date, can any when tell me what is the
problem
> Private Sub Command2_Click()
> Call connect
> Call setup
>
> Dim cmd As New ADODB.Command
> cmd.ActiveConnection = Con
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "Employee Sales by Country"
> Dim parm As New ADODB.Parameter
> Dim parm2 As New ADODB.Parameter
> Set parm = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/1997")
> Set parm2 = cmd.CreateParameter("ShippedDate", adDate, adParamInput, ,
> "01/01/2005")
> cmd.Parameters.Append parm
> cmd.Parameters.Append parm2
>
> Dim RST As New ADODB.Recordset
> Set RST = cmd.Execute
> Set DataGrid1.DataSource = RST
> DataGrid1.ReBind
> End Sub
>
>

Datename gives incorrect result

Hi!
I tried to run this query:
select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
The result is:
34 33
It's worng result, why?
Right answer is 33 in both datenamn item.
I have SQL Server 2000
Best regards
Bertil MorefltSQL Server doesn't calculate ws according to the ISO standard. I.e., don'
t use datepart or
datename for w number calculation. Search Books Online for ISOW and us
e that one instead. Or
use a calendar table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt|||If you are looking for ISOWEEK, you can find one at the CREATE Function
example in BOL
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt|||Here you will find a function to calculate the iso w.
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
AMB
"Bertil Morefalt" wrote:

> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moref?lt
>|||You can use a calendar table for this, or the ISOWEEK() function in Books
Online, or the one listed here:
http://www.aspfaq.com/2519
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt

Datediff and Record navigation

Hi:

I need help to find the difference between two times within the same table as follow

For every alarm incident (alarm will repeat within the table at different times)

Alarm acknowledge time:AkTime = AkAlm – InAlm

Alarm repair time:ReTime = OutAlm – Akalm

Alarm DownTime:AdtTime = OutAlm – InAlm

The table contains a decent number of records (> 10,000,000) and is indexed by the timestamp.

The table looks like this:

Alarm

AlarmType

tsTimeStapm

3030

InAlm

1/11/05 9:11:00 AM

3030

AkAlm

1/11/05 10:48:00 AM

3030

OutAlm

1/11/05 1:32:00 PM

3032

InAlm

1/11/05 2:51:00 PM

3032

AkAlm

1/11/05 2:52:35 PM

3032

OutAlm

1/11/05 3:14:00 PM

3030

InAlm

1/11/05 3:24:00 PM

3030

AkAlm

1/11/05 3:26:30 PM

3030

OutAlm

1/11/05 4:15:15 PM

Additionally

I need to calculate the mean time between alarms i.e. the time difference from the OutAlm to the next InAlm

Ex: Data from the sample table

3030

OutAlm

1/11/05 1:32:00 PM

3032

InAlm

1/11/05 2:51:00 PM

3032

OutAlm

1/11/05 3:14:00 PM

3030

InAlm

1/11/05 3:24:00 PM

Thanks in advance

Jorge

Jorge:

Maybe something like this:

declare @.alarm table
( Alarm integer,
AlarmType varchar (7),
tsTimeStamp datetime,

primary key (Alarm, tsTimeStamp),
unique (tsTimestamp, Alarm)
)

insert into @.alarm values (3030, 'InAlm', '1/11/05 9:11:00 AM')
insert into @.alarm values (3030, 'AkAlm', '1/11/05 10:48:00 AM')
insert into @.alarm values (3030, 'OutAlm', '1/11/05 1:32:00 PM')
insert into @.alarm values (3032, 'InAlm', '1/11/05 2:51:00 PM')
insert into @.alarm values (3032, 'AkAlm', '1/11/05 2:52:35 PM')
insert into @.alarm values (3032, 'OutAlm', '1/11/05 3:14:00 PM')
insert into @.alarm values (3030, 'InAlm', '1/11/05 3:24:00 PM')
insert into @.alarm values (3030, 'AkAlm', '1/11/05 3:26:30 PM')
insert into @.alarm values (3030, 'OutAlm', '1/11/05 4:15:15 PM')
--select * from @.alarm

select Alarm,
inAlm as [Alarm Timestamp],
datediff (mi, inAlm, akAlm) as [Alarm Acknowledgment Time],
datediff (mi, akAlm, outAlm) as [Alarm Repair Time],
datediff (mi, inAlm, outAlm) as [Alarm Downtime]
from ( select a.alarm,
a.tsTimeStamp as InAlm,
( select min (b.tsTimeStamp)
from @.alarm b
where a.alarm = b.alarm
and b.tsTimeStamp > a.tsTimeStamp
and b.alarmType = 'AkAlm'
) as AkAlm,
( select min (c.tsTimeStamp)
from @.alarm c
where a.alarm = c.alarm
and c.tsTimeStamp > a.tsTimeStamp
and c.alarmType = 'OutAlm'
) as OutAlm
from @.alarm a
where alarmType = 'InAlm'
) alm

-- Alarm Alarm Timestamp Alarm Acknowledgment Time Alarm Repair Time Alarm Downtime
-- -- -- - -- --
-- 3030 2005-01-11 09:11:00.000 97 164 261
-- 3030 2005-01-11 15:24:00.000 2 49 51
-- 3032 2005-01-11 14:51:00.000 1 22 23

select avg (datediff (mi, lastOutage, nextOutage)) as [Mean Time Between Alarms]
from (
select a.Alarm,
a.alarmType,
a.tsTimeStamp as [lastOutage],
( select min (tsTimestamp)
from @.alarm b
where b.alarmType = 'InAlm'
and b.tsTimestamp > a.tsTimestamp
) as [nextOutage]
from @.alarm a
where a.alarmType = 'OutAlm'
) x
where nextOutage is not null

-- Mean Time Between Alarms
--
-- 44

Dateadd and Dynamic SQL

Hi!
I am trying to pass two variables @.date and @.days in the DATEADD
function but getting syntax error message saying "Syntax error
converting the varchar value 'select dateadd(day, ' to a column of data
type int.". I think I am not using the correct syntax. Can you please
help in correcting it?
Thanks,
declare @.date datetime
declare @.days int
set @.date = '5/19/2005'
set @.days = 60
declare @.S varchar(100)
-- hard coded works
--set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
+ ''')'
set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
convert(varchar(10), @.date, 110) + ''')'
print @.S
*** Sent via Developersdex http://www.examnotes.net ***Any reason you are doing dynamic SQL
Anyway the code below works
declare @.date datetime
declare @.days int
set @.date = '5/19/2005'
set @.days = 60
declare @.S varchar(100)
select @.S = dateadd(day, @.days ,convert(varchar(10), @.date, 110) )
print @.S
http://sqlservercode.blogspot.com/
"Test Test" wrote:

> Hi!
> I am trying to pass two variables @.date and @.days in the DATEADD
> function but getting syntax error message saying "Syntax error
> converting the varchar value 'select dateadd(day, ' to a column of data
> type int.". I think I am not using the correct syntax. Can you please
> help in correcting it?
> Thanks,
> declare @.date datetime
> declare @.days int
> set @.date = '5/19/2005'
> set @.days = 60
> declare @.S varchar(100)
> -- hard coded works
> --set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
> + ''')'
> set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
> convert(varchar(10), @.date, 110) + ''')'
> print @.S
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes I need it to be done using dynamic SQL bc. Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
set @.S = 'select dateadd(day, ' + ltrim(@.days) + ', @.date)'
AMB
"Test Test" wrote:

> Hi!
> I am trying to pass two variables @.date and @.days in the DATEADD
> function but getting syntax error message saying "Syntax error
> converting the varchar value 'select dateadd(day, ' to a column of data
> type int.". I think I am not using the correct syntax. Can you please
> help in correcting it?
> Thanks,
> declare @.date datetime
> declare @.days int
> set @.date = '5/19/2005'
> set @.days = 60
> declare @.S varchar(100)
> -- hard coded works
> --set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
> + ''')'
> set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
> convert(varchar(10), @.date, 110) + ''')'
> print @.S
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Correction,
set @.S = 'select dateadd(day, ' + ltrim(@.days) + ',''' +
convert(varchar(35), @.date, 126) + ''')'
AMB
"Alejandro Mesa" wrote:
> Try,
> set @.S = 'select dateadd(day, ' + ltrim(@.days) + ', @.date)'
>
> AMB
>
> "Test Test" wrote:
>