Getting A Query Parameter from Elsewhere

I have a query on a table. This table has a datetime column.
I have another table. This table also has a datetime column. It has only one
row. It basically to store some simple configuration data.
There is no relation between the tables.
I want to be able to limit the entries returned by the query on the first
table by only returning rows that have a date that is less than the date in
the row in the second table.
I'm actually having trouble getting this to work properly. I've tried
subqueries, passing it through the report as a parameter, etc... None are
working.
How should i attack this?It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
possibility for you. I am sure there are much better ways, but this is off
the cuff:
Select
MyDate, Field1, Field2 from table1
Group by
MyDate,Field1,Field2
HAVING MyDate < (Select LookUpDate from Table2)
You could always do it in a stored procedure and call in the value of Table2
into a variable @.LokUpDate that yo could compare. I assume that the
LookUpDate in my example to match your description changes on a regular
basis?
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
> one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
> in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||You really do not have to add an aggregate function. You can always set a
variable and use that in the Where clause if you do not want to use the
GROUP BY. Something like ( and I added CREATEDATE to the WHERE clause also)
:
Declare @.CUTOFFDATE as DATETIME
SELECT @.CUTOFFDATE=CUTOFFDATE from CONFIGDATA
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID ='acm')
AND (SALESTABLE.SALESSTATUS = 1) AND CREATEDATE < @.CUTOFFDATE
You may have to make this a stored procedure if it will not work on the IDE
for reporting Services.
Rodney Landrum -Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:211E0BCB-A461-485A-B888-333A33E33162@.microsoft.com...
> If I use GROUP BY, will that not require aggregation of the results of the
> query?
> Perhaps it would be helpful to see the existing query:
> SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
> QTY,
> (SELECT SUM(MARKUPTRANS.VALUE)
> FROM MARKUPTRANS
> WHERE SALESTABLE.RECID => MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
> MARKUPTRANS.MARKUPCODE => 'Freight') AS FreightValue,
> (SELECT SUM(INVENTSUM.POSTEDVALUE)
> FROM INVENTSUM
> WHERE SALESLINE.INVENTDIMID => INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
> FROM SALESTABLE INNER JOIN
> SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
> WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID => 'acm')
> AND (SALESTABLE.SALESSTATUS = 1) >
> That is table one. I want to limit on a column called CREATEDDATE.
> The other query looks like:
> SELECT CUTOFFDATE from CONFIGDATA
> "Rodney Landrum" wrote:
>> It would be possible to use a GROUP BY / HAVING in the SELECT if that was
>> a
>> possibility for you. I am sure there are much better ways, but this is
>> off
>> the cuff:
>> Select
>> MyDate, Field1, Field2 from table1
>> Group by
>> MyDate,Field1,Field2
>> HAVING MyDate < (Select LookUpDate from Table2)
>> You could always do it in a stored procedure and call in the value of
>> Table2
>> into a variable @.LokUpDate that yo could compare. I assume that the
>> LookUpDate in my example to match your description changes on a regular
>> basis?
>> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
>> http://www.apress.com
>> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
>> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>> >I have a query on a table. This table has a datetime column.
>> >
>> > I have another table. This table also has a datetime column. It has
>> > only
>> > one
>> > row. It basically to store some simple configuration data.
>> >
>> > There is no relation between the tables.
>> >
>> > I want to be able to limit the entries returned by the query on the
>> > first
>> > table by only returning rows that have a date that is less than the
>> > date
>> > in
>> > the row in the second table.
>> >
>> > I'm actually having trouble getting this to work properly. I've tried
>> > subqueries, passing it through the report as a parameter, etc... None
>> > are
>> > working.
>> >
>> > How should i attack this?
>>|||I might be missing something but this looks like the following to me:
select a.* from maintable a, configtable b where a.datetimecolumn <
b.datetimecolumn
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?

gets inserted twice

Gets inserted twice int TBL_NOTICES??

--------------------------
DECLARE @.IDRess integer, @.FromDate dateTime, @.ToDate dateTime, @.LateCounter integer

SET @.FromDate = GETDATE() - 30
SET @.ToDate = GETDATE()

DECLARE LateCountCursor CURSOR
FOR
SELECT DISTINCT IDRess, COUNT(Late) AS LateCount
FROM TBL_EXTERNAL_ENTRY
WHERE (DateInvolved BETWEEN GETDATE() - 30 AND GETDATE()) AND (Late = 1)
GROUP BY IDRess
ORDER BY IDRess

OPEN LateCountCursor

-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM LateCountCursor
INTO @.IDRess, @.LateCounter

IF @.LateCounter >= 1
BEGIN
DECLARE @.late int, @.dept int, @.sup int, @.poste int, @.patron int
SELECT @.late = (SELECT ID_NOTICE FROM TBL_NOTICE_TYPE WHERE NoticeName = 'Late')
DECLARE RessCursor CURSOR
FOR
SELECT IDDepartement, IDContremaitre, IDPoste, IDPatron FROM TBL_RESSOURCES WHERE IDInterne = @.IDRess
OPEN RessCursor
IF @.@.FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM RessCursor
INTO @.dept, @.sup, @.poste, @.patron
END
CLOSE RessCursor
DEALLOCATE RessCursor

INSERT INTO TBL_NOTICES (IDInterne, IDDepartement, DateInfraction, DateAvis, IDMotif, NotesMotif, IDSuperieur, IDPoste, IDRedacteur, IDPatron, InscDossier, SuspSansSolde, Congediement, Autres)
VALUES (@.IDRess, @.dept, @.ToDate, @.ToDate, @.late, 'SQL Server LateCheck Job', @.sup, @.poste, 99999, @.patron, 0, 0, 0, 0)

DECLARE @.msg varchar(100)
SET @.msg = 'Infraction de retard trop frquents pour employee # ' + @.IDRess
EXEC [master].[dbo].xp_startmail
EXEC [master].[dbo].xp_sendmail @.recipients = 'gdo',
@.message = @.msg,
@.subject = 'Infraction [RETARD]'
END

END
CLOSE LateCountCursor
DEALLOCATE LateCountCursor

--------------------------

Also, does anyone know about a good T-SQL Editor with wich I could step through the execution of a function...

gdogdo

the query you have posted won't even write two records. It won't actually do anything.

Your 'FETCH NEXT FROM LateCountCursor ' line comes after your 'WHILE @.@.FETCH_STATUS = 0' line, so the @.@.FetchStatus will be -1 (try print @.@.Fetch_Status to see). Because it is -1, the compiler will not even enter the while loop, therefore none of the code will be performed. Your 'FETCH NEXT FROM LateCountCursor ' line should come before the 'WHILE @.@.FETCH_STATUS = 0' line to ensure that the code in the while loop is actually implemented.

As far as I know there is no tool that will allow you to step through TSQL code. SQL Server creates a query plan for the code before it is actually exectuted, and the sequence of events in that query plan does not neccesarily match the sequence of code.|||Lwaker,
Actually, @.@.FETCH_STATUS could be 0 depending on what
the status of the last FETCH was before gdo's code block was
started. Since @.@.FETCH_STATUS is global to all cursors in a
connection, it might be valid upon entry to tis code block.

I agree the condition checking of @.@.FETCH_STATUS needs to
be changed as you mentioned. Gdo might get two inserts performed
one call, then zero the next with the current setup.|||If I understand correctly... 'Fetch Next' is required before the while loop and also inside the while loop after the insert statement.

thanks,

By the way, about something else...

we installed outlook on the server which has SQL Server in order to use SQL Mail.

Server: Windows 2k and SQL Server 2k (They do not use Exchange Server, don't know if it has something something to do with it...)

The address book has been imported in outlook but we do not see outlook in the combobox of SQL Mail Configuration?

Any ideas?

gdo

GETDATE() to insert into a datetime field

Hi there, may seem like a really silly question, but i am new to SQL.
I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
record behaviour to upload information to the SQL Server. one of the fields
is DateRegisterred which in Dreamweaver is a hiddenfield with the value set
to GETDATE() called datereg so on the upload behaviour Dreamweaver should
upload GETDATE() from datereg to thedatetimefield DateRegisterred on the SQL
server. However on trying this i get the following error message -
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string.
/driverjobs/candregistration.asp, line 161
Please any advise as how to fix this would be really really appreciated.
Many thanks
GarethHi
It looks like your field is being taken as a string and you have issues with
the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
instead of getdate() on it's own. If you want to truncate the time use
CONVERT(char(8),GETDATE(),112)
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Thanks John, should i change it within the hidden field, or somewhere in the
code? i've tried changing it in the hidden field but still get the same erro
r
message?
thanks for your help
Gareth
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi again,
I've included the line of code - Thanks again.
<td><input name="DateRegistered" type="hidden" id="DateRegistered"
value="CONVERT(char(8),GETDATE(),112)"></td>
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi
You can't put SQL into your code like that, it should be in the query that
you call and then populate the field from this value (in the same way you
populate others!). If you can use the datetime that the insert is made,
then it can be missed out completely and the column defined as not nullable
with GETDATE() as the default.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
> Hi again,
> I've included the line of code - Thanks again.
> <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> value="CONVERT(char(8),GETDATE(),112)"></td>
> "John Bell" wrote:
>|||Ahh, I think i understand, the Date function GETDATE() needs to run
automatically on the SQL server not in the ASP code. Thank you for this, it
only leaves me with one problem.
On another page users are allowed to post information for a period of time
which they chose from a drop down list -
where the item label is forinstance 7 days & the value is
DATEADD(d,7,GETDATE())
They can chose from 7,14,21 or 28 days - their choice populates a field
called expirydate.
My theory apparently wont work now, any ideas as to how i can get around
this one?
Onced again thanks for your help
Gareth
"John Bell" wrote:

> Hi
> You can't put SQL into your code like that, it should be in the query that
> you call and then populate the field from this value (in the same way you
> populate others!). If you can use the datetime that the insert is made,
> then it can be missed out completely and the column defined as not nullabl
e
> with GETDATE() as the default.
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
>
>|||Why not have the default value of the date field equal to getdate() in SQL
Server? If you're doing an INSERT it's a new record, set it then.
Jon
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Hi
This may help with the previous issue http://www.aspfaq.com/show.asp?id=2347
Instead of giving a offset, you may want to use a date picker see
http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
option then if you are building up a string for your update statement you
can do something like (untested!):
<%
SQL = "UPDATE table SET NextDate = DATEADD(dd," &
Request.Form("DateOffset") & ",GETDATE())"
response.write sql
%>
If you are calling a stored procedure then you just need to pass the
offset value.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
> Ahh, I think i understand, the Date function GETDATE() needs to run
> automatically on the SQL server not in the ASP code. Thank you for this,
> it
> only leaves me with one problem.
> On another page users are allowed to post information for a period of time
> which they chose from a drop down list -
> where the item label is forinstance 7 days & the value is
> DATEADD(d,7,GETDATE())
> They can chose from 7,14,21 or 28 days - their choice populates a field
> called expirydate.
> My theory apparently wont work now, any ideas as to how i can get around
> this one?
> Onced again thanks for your help
> Gareth
> "John Bell" wrote:
>|||Thanks John, once again seems very useful, Just a quick question would
something like
CONVERT(char(8), DATEADD(dd," &
Request.Form("expirydate") & ",GETDATE(),112)
work? inserted into the default value?
Thanks
"John Bell" wrote:

> Hi
> This may help with the previous issue http://www.aspfaq.com/show.asp?id=2...ow.asp?id=2309, if you do go with the original
> option then if you are building up a string for your update statement you
> can do something like (untested!):
> <%
> SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> Request.Form("DateOffset") & ",GETDATE())"
> response.write sql
> %>
> If you are calling a stored procedure then you just need to pass the
> offset value.
> John
>
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
>
>|||Hi
If you mean the default for column, then you don't need dateadd e.g
CREATE TABLE MyDates ( id int, dateval datetime not null default
convert(char(8),getdate(),112))
INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
INSERT INTO MyDates (id) VALUES ( 2)
INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
SELECT * FROM MyDates
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@.microsoft.com...
> Thanks John, once again seems very useful, Just a quick question would
> something like
> CONVERT(char(8), DATEADD(dd," &
> Request.Form("expirydate") & ",GETDATE(),112)
> work? inserted into the default value?
> Thanks
> "John Bell" wrote:
>sql

getdate() rounded when runned in a job.

Hi all.
I am confusing on this matter so if anyone recognize this please let me
know.
Simple scenario.
I am using getdate() to fill datetime into a column in a table. All within a
prosedure.
Create a tmp table with datetime column.
Insert getdate()
Insert the temptable into a physical table with a datetime column.
Running the Exec prosedurename for Queryanalyzer I get the the whole
datetime value into the table. '2005-12-22 17:05:00,256'
BUT
Starting a Squeduled job running the same Exec I get only the date and it is
rounded upwards '2005-12-23 00:00:00,000'
Any ideas. I gues this is "by design" or something. I also think this
happens when running the job after 12 in the day.
thanx
geir"when running the job after 12 in the day."
That sounds misterious :-) No, something has to connvert the datetime
value to a less precise value. Does your proc perhaps use an input
paramter which is some kind of casted if you are calling it from a
scheduled job ?
DDL of the procedure and the job would be fine here. Jobs can be
scripted via Enterprise Manager into a sql file.
HTH, jens Suessmeyer.|||Hi Jens.
Sounds like some less precise value and "then I have to make a desission for
the time part and I decide to round up to the next date" issue.
I found out that if I Convert() the datetime as in Convert(varchar(25),
#tmpTable.DateTimeValue) in the moment when I insert into the pysical table
it all got inn. Right date and time.
What I don't understand is the differense between running in a job or in the
QueryAnalyzer.
So, it is solved. I have no ide why :-)
I run exact the same Exec with all hardcode values as parameters.
Exec custMPXrptFilExportSP 13, 3, 1, 17
go
CREATE PROCEDURE custXXXrptFilExportSP
@.KundeID int,
@.AvsAdresseID int,
@.LagerID int,
@.WebDataID int
AS

Getdate() overflowing datetime

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

Chris Honcoop wrote:

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

That sounds strange. Can you use an error output on the destination component to capture the erroring records?

-Jamie

|||

I checked all my other packages with Fast Load and I noticed all the others had "check constraints" turned on - so I turned it on for this package and it ran fine all night (still going). Very strange but at least now if someone else sees this happen try turning on constraint checking.

Jamie, I'd love to - however I am on a deadline to get this data processed. This process is run once a month so hopefully next month I can give it a try and see if I can get some data on the failed rows. (that said I started running this process in sept and ran it in sept and oct without issue).

|||

Chris, have you recently installed any service packs or made other changes?

Thanks
Mark

|||

Not any on SQL/SSIS. I don't have visibility/control of the OS level. I do have some nonpublic patches for SSIS installed (provided to me by msft) to combat a specific problem I encountered (memory corruption on packages with lots of sorts) but these patches have been installed since august.

FYI this package is running on a 4 proc dual core 64-bit box with 16Gb RAM. For this package everything is local (the source db, SSIS, SQL, dest db).

|||

FYI.. I found more strangeness in regards to this error... when the package errored with that error it had also inserted thousands (15,000 each failure to be exact) of rows of garbage data: All int/decimal columns were 0, all string columns were empty set. (some of those ints were lookups where 0 is not a possible lookup value, also dates converted to ints as well). The only way I could trace these records to this failure is inserttime was correctly populated - I went back through my audit history and matched the inserttime to the failure time of the job.

So if this happens to you be on the lookout for this!!!

It almost appears that SSIS somehow got ahead of itself - inserting rows before it actually completely got the data from the source, populated the dtInsertTime etc....

Honestly this is quite scary....

|||We have been getting random errors with that message too. We are copying the records from a 64-bit sql 2005 enterprise edtions to a 64 bit standard edition server and are stumped as to why it would happen. The column that it is occuring on has the same value for all rows. We can run the package again and not have any problems.

Getdate() overflowing datetime

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

Chris Honcoop wrote:

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

That sounds strange. Can you use an error output on the destination component to capture the erroring records?

-Jamie

|||

I checked all my other packages with Fast Load and I noticed all the others had "check constraints" turned on - so I turned it on for this package and it ran fine all night (still going). Very strange but at least now if someone else sees this happen try turning on constraint checking.

Jamie, I'd love to - however I am on a deadline to get this data processed. This process is run once a month so hopefully next month I can give it a try and see if I can get some data on the failed rows. (that said I started running this process in sept and ran it in sept and oct without issue).

|||

Chris, have you recently installed any service packs or made other changes?

Thanks
Mark

|||

Not any on SQL/SSIS. I don't have visibility/control of the OS level. I do have some nonpublic patches for SSIS installed (provided to me by msft) to combat a specific problem I encountered (memory corruption on packages with lots of sorts) but these patches have been installed since august.

FYI this package is running on a 4 proc dual core 64-bit box with 16Gb RAM. For this package everything is local (the source db, SSIS, SQL, dest db).

|||

FYI.. I found more strangeness in regards to this error... when the package errored with that error it had also inserted thousands (15,000 each failure to be exact) of rows of garbage data: All int/decimal columns were 0, all string columns were empty set. (some of those ints were lookups where 0 is not a possible lookup value, also dates converted to ints as well). The only way I could trace these records to this failure is inserttime was correctly populated - I went back through my audit history and matched the inserttime to the failure time of the job.

So if this happens to you be on the lookout for this!!!

It almost appears that SSIS somehow got ahead of itself - inserting rows before it actually completely got the data from the source, populated the dtInsertTime etc....

Honestly this is quite scary....

|||We have been getting random errors with that message too. We are copying the records from a 64-bit sql 2005 enterprise edtions to a 64 bit standard edition server and are stumped as to why it would happen. The column that it is occuring on has the same value for all rows. We can run the package again and not have any problems.

Getdate() overflowing datetime

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

Chris Honcoop wrote:

I have a weird error that just started showing up. This process has run many times before and just today it started erroring.

The error I get is:

There was an error with input column "dtInsertTime" (242) on input "OLE DB Destination Input" (146). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

The weird thing is that column is added to the data flow via a derived column just before the destination and its set to GETDATE(). The destination column for that field is datetime not null with the same name. I have litterally hundreds of packages that do the same thing (add a column set to getdate() of type dbtimestamp going into sql 2005 column with datetime) and have never run into this. Its frustrating.. the job will run for a half hour inserting records just fine and then BAM fails.

I'm completely out of ideas... Most destinations I don't use the fast load option so I am running it right now with that off to see if that makes a difference (other than making it slower). Previously I had it set to "keep nulls" and "table lock" but not "check integrity".

Edit: I'll have to abandon my test without fast load... my load times went from < 30 second to 2-3 minutes per set of records.

That sounds strange. Can you use an error output on the destination component to capture the erroring records?

-Jamie

|||

I checked all my other packages with Fast Load and I noticed all the others had "check constraints" turned on - so I turned it on for this package and it ran fine all night (still going). Very strange but at least now if someone else sees this happen try turning on constraint checking.

Jamie, I'd love to - however I am on a deadline to get this data processed. This process is run once a month so hopefully next month I can give it a try and see if I can get some data on the failed rows. (that said I started running this process in sept and ran it in sept and oct without issue).

|||

Chris, have you recently installed any service packs or made other changes?

Thanks
Mark

|||

Not any on SQL/SSIS. I don't have visibility/control of the OS level. I do have some nonpublic patches for SSIS installed (provided to me by msft) to combat a specific problem I encountered (memory corruption on packages with lots of sorts) but these patches have been installed since august.

FYI this package is running on a 4 proc dual core 64-bit box with 16Gb RAM. For this package everything is local (the source db, SSIS, SQL, dest db).

|||

FYI.. I found more strangeness in regards to this error... when the package errored with that error it had also inserted thousands (15,000 each failure to be exact) of rows of garbage data: All int/decimal columns were 0, all string columns were empty set. (some of those ints were lookups where 0 is not a possible lookup value, also dates converted to ints as well). The only way I could trace these records to this failure is inserttime was correctly populated - I went back through my audit history and matched the inserttime to the failure time of the job.

So if this happens to you be on the lookout for this!!!

It almost appears that SSIS somehow got ahead of itself - inserting rows before it actually completely got the data from the source, populated the dtInsertTime etc....

Honestly this is quite scary....

|||We have been getting random errors with that message too. We are copying the records from a 64-bit sql 2005 enterprise edtions to a 64 bit standard edition server and are stumped as to why it would happen. The column that it is occuring on has the same value for all rows. We can run the package again and not have any problems.sql

GetDate() in Constraint using user's system time, Stored Proc using Server's.

All,

I have a table that has a Default Constraint for setting a DateTime field. I have a stored procedure that calls data from the table where the date field is <= GetDate().

I performed the following test:

1. Called insert stored proc and checked date field for recently added entry by query window ( 2007-03-01 11:09:44.000 ). This matches my (user) system date and time.

2. Immediately call GetDate() from the query window (2007-03-01 11:07:47.727). I assume this is the Server system date and time.

*note: These servers are on separate domains and therefore could have different system times.

This causes my select stored procedure to NOT return the values I just entered.

Any ideas on why this might occur? Does GetDate() run within the context of it's call (ie Called from application, uses web server system time, but called from query window uses server)?

If more that one server is involved I would check the system time delta between them and compare that to what you see in your test. In my experience two servers in the same domain getting time from the same server can be off by minutes...depending on how often they poll.|||

Todd:

Are you saying that you are trying to have the trigger update a datetime field and then use the getdate() function to try match the inserted record? If so, that is not a very good idea; this procedure will not be "tight" enough.

Also, if you are trying to use getdate() as method of "water-marking" records so that you can dynamically process records according to whether or not they are greater than or equal to the getdate() watermark, that kind of process will also "leak" records from time to time. This is a problem that I have battled a number of times. I can put together a mock-up to demonstrate that leakage problem if you would like.

sql

getDate not working in function

Hi,
Using MSSQL 2000
How can I get hold of the current datetime inside a function?
declare @.dt datetime;
select @.dt = getdate();
gives an "Invalid use of 'getdate' within a function" error.
Any help will be greatly appreciated.
Regards,
arnoudgot it
select @.dt = dbo.GETDATE();
does the job.
"arnoud oortwijk" <abc> wrote in message
news:ONNpGsWzFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Using MSSQL 2000
> How can I get hold of the current datetime inside a function?
> declare @.dt datetime;
> select @.dt = getdate();
> gives an "Invalid use of 'getdate' within a function" error.
> Any help will be greatly appreciated.
> Regards,
> arnoud
>|||No. That would reference a user-defined function called dbo.GETDATE. I
don't think that's what you wanted.
You can't use the system function GETDATE() in a user-defined function.
Easiest method is to pass the time as a parameter.
David Portas
SQL Server MVP
--|||David
> Easiest method is to pass the time as a parameter
I think you meant
Easiest method is to pass the GETDATE() function as a parameter
:-)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128929659.370492.41730@.g14g2000cwa.googlegroups.com...
> No. That would reference a user-defined function called dbo.GETDATE. I
> don't think that's what you wanted.
> You can't use the system function GETDATE() in a user-defined function.
> Easiest method is to pass the time as a parameter.
> --
> David Portas
> SQL Server MVP
> --
>

GetDate not working if passed

I am calling a SP that is expecting a datetime.
If I pass '02/15/06/, it works fine.
If I do it like:
****************************************
**************
Declare @.DateToSend VarChar(20)
Select @.DateToSend = GetDate()
Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
****************************************
******************
This works.
But if I do
Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
I get the error:
Server: Msg 170, Level 15, State 1, Line 63
Line 63: Incorrect syntax near ')'.
Why doesn't this work?
Thanks,
TomBecause GETDATE() returns a DATETIME datatype and you are declaring the
parameter as a VARCHAR. Change it to a DATETIME and it should be fine. The
reason it works passing the string is that SQL Server will implicity convert
a proper string to a DATETIME datatype but not the other way around. And
you should get in the habit of using the ISO or ANSI format for date or
datetime strings. See here for more details:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uXws$OzMGHA.1676@.TK2MSFTNGP09.phx.gbl...
>I am calling a SP that is expecting a datetime.
> If I pass '02/15/06/, it works fine.
> If I do it like:
> ****************************************
**************
> Declare @.DateToSend VarChar(20)
> Select @.DateToSend = GetDate()
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
> ****************************************
******************
> This works.
> But if I do
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
> I get the error:
> Server: Msg 170, Level 15, State 1, Line 63
> Line 63: Incorrect syntax near ')'.
> Why doesn't this work?
> Thanks,
> Tom
>|||You can't pass a function as a parameter. You need to store it in a
variable in the interim. Or, make the parameter optional, e.g.
CREATE PROCEDURE dbo.COM_INSERT_MESSAGE_TO_QUEUE_SP
@.firstParam INT,
@.secondParam DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT @.secondParam = COALESCE(@.secondParam, GETDATE());
..
END
GO
Also, '02/15/06' is a horrible, horrible, horrible date format. I recommend
you get in the habit of using unambiguous formats.
http://www.karaszi.com/SQLServer/in...#DtFormatsInput
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uXws$OzMGHA.1676@.TK2MSFTNGP09.phx.gbl...
>I am calling a SP that is expecting a datetime.
> If I pass '02/15/06/, it works fine.
> If I do it like:
> ****************************************
**************
> Declare @.DateToSend VarChar(20)
> Select @.DateToSend = GetDate()
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
> ****************************************
******************
> This works.
> But if I do
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
> I get the error:
> Server: Msg 170, Level 15, State 1, Line 63
> Line 63: Incorrect syntax near ')'.
> Why doesn't this work?
> Thanks,
> Tom
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OnOVqTzMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Because GETDATE() returns a DATETIME datatype and you are declaring the
> parameter as a VARCHAR. Change it to a DATETIME and it should be fine.
> The reason it works passing the string is that SQL Server will implicity
> convert a proper string to a DATETIME datatype but not the other way
> around. And you should get in the habit of using the ISO or ANSI format
> for date or datetime strings. See here for more details:
I did have the datatype set as DateTime:
@.system tinyint,
@.date_to_send datetime,
@.from varchar(256),
but when I called the SP using GetDate(), I get the error.
Tom
> http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
> http://www.sqlservercentral.com/col...sqldatetime.asp
> Datetimes
> http://www.murach.com/books/sqls/article.htm Datetime Searching
>
> --
> Andrew J. Kelly SQL MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:uXws$OzMGHA.1676@.TK2MSFTNGP09.phx.gbl...
>|||Can you post the actual code for the sp and exactly how you called it? By
the way you should also get in the habit of qualifying all objects
especially sp's with the owner.
EXEC dbo.Yoursp
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e$iq650MGHA.208@.tk2msftngp13.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OnOVqTzMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> I did have the datatype set as DateTime:
> @.system tinyint,
> @.date_to_send datetime,
> @.from varchar(256),
> but when I called the SP using GetDate(), I get the error.
> Tom
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eYOYZg1MGHA.140@.TK2MSFTNGP12.phx.gbl...
> Can you post the actual code for the sp and exactly how you called it? By
> the way you should also get in the habit of qualifying all objects
> especially sp's with the owner.
>
The SP is something like:
CREATE PROCEDURE dbo.COM_INSERT_MESSAGE_TO_QUEUE_SP
(
@.system tinyint,
@.date_to_send datetime
)
Called like:
Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
Doesn't work.
Called like:
****************************************
**************
Declare @.DateToSend VarChar(20)
Select @.DateToSend = GetDate()
Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
****************************************
******************
Does work.
If GetDate() passes a DateTime, why doesn't it work in the Exec statement?
Also, all my SP are all owned by dbo and are called by my Web Server. This
seems to work fine. Why should I need to add the dbo.?
Thanks,
Tom

> EXEC dbo.Yoursp
>
> --
> Andrew J. Kelly SQL MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e$iq650MGHA.208@.tk2msftngp13.phx.gbl...
>|||> If GetDate() passes a DateTime, why doesn't it work in the Exec statement?
Did you see my reply? You can't pass a function into a parameter.|||See Aaron's reply.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7Ba9B3MGHA.1192@.TK2MSFTNGP11.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eYOYZg1MGHA.140@.TK2MSFTNGP12.phx.gbl...
> The SP is something like:
> CREATE PROCEDURE dbo.COM_INSERT_MESSAGE_TO_QUEUE_SP
> (
> @.system tinyint,
> @.date_to_send datetime
> )
> Called like:
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
> Doesn't work.
> Called like:
> ****************************************
**************
> Declare @.DateToSend VarChar(20)
> Select @.DateToSend = GetDate()
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
> ****************************************
******************
> Does work.
> If GetDate() passes a DateTime, why doesn't it work in the Exec statement?
> Also, all my SP are all owned by dbo and are called by my Web Server.
> This seems to work fine. Why should I need to add the dbo.?
> Thanks,
> Tom
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GkUpt7MGHA.516@.TK2MSFTNGP15.phx.gbl...
> Did you see my reply? You can't pass a function into a parameter.
I did see it, I was just responding to Andrew. I think he misunderstood
what I was asking as he said I had the parameter set as VarChar and I was
showing how I had it set.
BTW, if you set a parameter as optional, can you use GetDate() there?
@.secondParam DATETIME = GetDate()
I don't think you can, but just curious.
Thanks,
Tom|||> BTW, if you set a parameter as optional, can you use GetDate() there?
> @.secondParam DATETIME = GetDate()
No, did you try it?

get time in SQL server

Hi ,
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgs
max
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs
|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:

>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1-- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

get time in SQL server

Hi ,
i am trying to get the time as well after the
conversion to datetime data type but could not do it.
is it possible to do so ?
declare @.date1 as string
set date1 = '12/5/2004 23:59:59'
declare @.date2 as datetime
set @.date2 = convert(@.date1) but it always return '12-05-
2004 00:00:00:000' which i want the time to be 23:59:59
thks & rdgsmax
SELECT CONVERT(CHAR(10),GETDATE(),108)
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:04c201c493f3$38c447a0$a401280a@.phx.gbl...
> Hi ,
> i am trying to get the time as well after the
> conversion to datetime data type but could not do it.
> is it possible to do so ?
> declare @.date1 as string
> set date1 = '12/5/2004 23:59:59'
> declare @.date2 as datetime
> set @.date2 = convert(@.date1) but it always return '12-05-
> 2004 00:00:00:000' which i want the time to be 23:59:59
> thks & rdgs|||On Mon, 6 Sep 2004 02:23:51 -0700, maxzsim wrote:
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
Hi Maxzsim,
Is this is SQL Server question or an Access question? You posted in a SQL
Server group, but your statements has some syntax elements that raise
syntax errors on SQL Server (and that look familiar from an Access point
of view): "as" in a declare statement, datatype "string" and "convert"
with only one argument are all illegal in SQL Server.
When I fix the syntax for SQL Server, I get either one of the following.
All of them leave the time part unchanged (ie 23:59:59, as requested).
(1)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = cast (@.date1 as datetime)
select @.date1, @.date2
(2)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = convert (datetime, @.date1)
select @.date1, @.date2
(3)
declare @.date1 varchar(20)
set @.date1 = '12/5/2004 23:59:59'
declare @.date2 datetime
set @.date2 = @.date1 -- implicit conversion
select @.date1, @.date2
Last but not least: the format of your date/time constant is ambiguous. Is
the date part formatted as mm/dd/yyyy or dd/mm/yyyy? Both readings can be
valid. If you want to be sure that SQL Server recognises your date and
time as you intended them, use one of these formats:
* yyyymmdd (for date only; time part will be set to midnight)
* yyyy-mm-ddThh:mm:ss (date plus time; the uppercase T is a constant)
* yyyy-mm-ddThh:mm:ss.mmm (as above, but including milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>--Original Message--
>Hi ,
> i am trying to get the time as well after the
>conversion to datetime data type but could not do it.
> is it possible to do so ?
>declare @.date1 as string
>set date1 = '12/5/2004 23:59:59'
>declare @.date2 as datetime
>set @.date2 = convert(@.date1) but it always return '12-05-
>2004 00:00:00:000' which i want the time to be 23:59:59
>thks & rdgs
>.
>

Get the smallest timespan between two entries

Hi all,
we have a table with a column of type "datetime"
We want to get the smallest timespan between two entries.
Now we get this timespan with the following query (it works but it's to
slow, it runs 5 secs with 250000 entries):
select min(Datediff(minute,a.rectime,b.rectime))
from dbo.value a, dbo.value b
where b.rectime = ( select min(rectime) from dbo.value
where rectime > a.rectime )
Any idea? Thanks in advance,
Mike
On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:

>Hi all,
>we have a table with a column of type "datetime"
>We want to get the smallest timespan between two entries.
>Now we get this timespan with the following query (it works but it's to
>slow, it runs 5 secs with 250000 entries):
>select min(Datediff(minute,a.rectime,b.rectime))
>from dbo.value a, dbo.value b
>where b.rectime = ( select min(rectime) from dbo.value
> where rectime > a.rectime )
>Any idea? Thanks in advance,
>Mike
Hi Mike,
Try changing the query to
SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
FROM dbo.value AS a, dbo.value b
WHERE b.rectime > a.rectime
You might also add something like
AND b.rectime < DATEADD(minute, a.rectime, 200)
where you change the 200 to a value that you know to be higher that the
timespan you are looking for, but low enough to greatly reduce the number
of matches between the a and b version of the value table.
If that doesn't work, look at your indexes. This query would greatly
benefit from an index on rectime (or rectime plus extra columns). If the
rate of change of this table is not too high and a small performance hit
on inserts, updates and deletes is acceptable, create a nonclustered index
on only rectime - that should yield the best possible performance.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||This might be more efficient:
select top 1
-- add WITH TIES if you select additional columns and want duplicates
datediff (minute, T2.rectime, min(T1.rectime)) as timeDiffMinutes
from yourTable T1 join yourTable T2
on T1.rectime > T2.rectime
group by T2.rectime
order by min(T1.rectime) - T2.rectime
Steve Kass
Drew University
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>Hi Mike,
>Try changing the query to
>SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
>FROM dbo.value AS a, dbo.value b
>WHERE b.rectime > a.rectime
>You might also add something like
>AND b.rectime < DATEADD(minute, a.rectime, 200)
>where you change the 200 to a value that you know to be higher that the
>timespan you are looking for, but low enough to greatly reduce the number
>of matches between the a and b version of the value table.
>
>If that doesn't work, look at your indexes. This query would greatly
>benefit from an index on rectime (or rectime plus extra columns). If the
>rate of change of this table is not too high and a small performance hit
>on inserts, updates and deletes is acceptable, create a nonclustered index
>on only rectime - that should yield the best possible performance.
>Best, Hugo
>
|||Oops - the suggestion I gave doesn't give a good query plan. This is
probably much better:
select top 1
datediff(minute,rectime, Nextrectime) as TimeDiff
from (
select
T1.rectime,
(select top 1 T2.rectime
from yourTable T2
where T2.rectime> T1.rectime
order by T2.rectime) as Nextrectime
from yourTable T1
) T
where Nextrectime is not null
order by Nextrectime - rectime
[and I shouldn't have replied to your post specifically - sorry]
SK
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 03:49:07 -0700, mike wrote:
>
>
>
>Hi Mike,
>Try changing the query to
>SELECT MIN(DATEDIFF(minute, a.rectime, b.rectime))
>FROM dbo.value AS a, dbo.value b
>WHERE b.rectime > a.rectime
>You might also add something like
>AND b.rectime < DATEADD(minute, a.rectime, 200)
>where you change the 200 to a value that you know to be higher that the
>timespan you are looking for, but low enough to greatly reduce the number
>of matches between the a and b version of the value table.
>
>If that doesn't work, look at your indexes. This query would greatly
>benefit from an index on rectime (or rectime plus extra columns). If the
>rate of change of this table is not too high and a small performance hit
>on inserts, updates and deletes is acceptable, create a nonclustered index
>on only rectime - that should yield the best possible performance.
>Best, Hugo
>
|||On Thu, 28 Oct 2004 20:31:00 -0400, Steve Kass wrote:

>and I shouldn't have replied to your post specifically - sorry
Hi Steve,
De nada. As long as the original poster sees it, all's well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the recent records

i have a datetime field in the post tables.

I would like to get the records within the latest 7 days.

Are there any functions for doing something like this?

my current query is something like

select * from post where creation_time ...

Thank you

try something like this:

create

table #test(datedatetime)

insert

into #test

values

('01/01/2007')

insert

into #test

values

('02/01/2007')

insert

into #test

values

('02/04/2007')

select

*from #test

where

date>dateadd(day,-7,getdate())

drop

table #test

I think that it will point you in correct direction, or maybe it is your solution?

|||

Could you not just do...

select

*from post

where

creation_time >dateadd(day,-7,getdate())

jpazgier, i am not following the reason for creating the additional table.

|||

I just try to provide working example in my answer so I created temporary table with my test data to show that it works and for future testing.

But in this case my example only points your how you can try to solve problem, you maybe would like to take care about not only day but also minutes?
This example if you run it at 12:31 today will show records inserted after 12:31 7 days ago so records inserted at 12:30 will be not visible and maybe author of the post would like to take care about this himself, I do not know if time of the day is important for him or not.

Thanks

|||Both answers are great!|||

Both answers are great!

Thank you

get the number of reports per day

I have a table that has a DateTime field that stores something like this "2004/01/19 16:16:15" which lets me know the date and time of the report. Now I need an sql statement that would let me know how many reports are being made per day. Can someone help me out?
This is what I've been doing(see sql), but I have to change the date and then run the query for a certain date. But I want one query that will give me the count on every date. I hope someone understands what I'm talking about. Thanks.

select * from Table where
DateTime >= '2004/03/01' and DateTime < '2004/03/01 23:59:59';Replace hard-coded date reference with a function call:

select * from Table where
DateTime >= convert(char(10), getdate(), 101)
and DateTime < dateadd(day, 1, convert(char(10), getdate(), 101))

get the list of records for last registered emails

hello,
i have a table for example mytable with 2 fields
email (varchar50) regdate(datetime)
i want to have a list of emails which are more times registered - sort by
last time when registered
example of entries in the table
u1@.dom1.com 26.03.2006 15:12:02
u2@.dom1.com 24.03.2006 15:12:02
u3@.dom1.com 24.03.2006 14:12:02
u1@.dom1.com 23.03.2006 13:12:02
u2@.dom1.com 22.03.2006 12:12:02
u1@.dom1.com 21.03.2006 11:12:02
u2@.dom1.com 20.03.2006 12:12:02
u2@.dom1.com 19.03.2006 12:12:02
i want to get something like
3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
time
4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
u3 - is not listed because it is only one time registered
the information is sort desc by last registration time
Yes i know what you think about the "tabledesign..." but my customer has
such a table - and he asked me for that information:The information what
will result is then inserted in a new table...
thanksTry:
select
count (*)
, email
, max (regdatetime) regdatetime
group by
email
having
count (*) > 1
order by
regdatetime desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:B43ACCC2-9ADA-4B0B-B8E3-4DFDA2694567@.microsoft.com...
hello,
i have a table for example mytable with 2 fields
email (varchar50) regdate(datetime)
i want to have a list of emails which are more times registered - sort by
last time when registered
example of entries in the table
u1@.dom1.com 26.03.2006 15:12:02
u2@.dom1.com 24.03.2006 15:12:02
u3@.dom1.com 24.03.2006 14:12:02
u1@.dom1.com 23.03.2006 13:12:02
u2@.dom1.com 22.03.2006 12:12:02
u1@.dom1.com 21.03.2006 11:12:02
u2@.dom1.com 20.03.2006 12:12:02
u2@.dom1.com 19.03.2006 12:12:02
i want to get something like
3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
time
4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
u3 - is not listed because it is only one time registered
the information is sort desc by last registration time
Yes i know what you think about the "tabledesign..." but my customer has
such a table - and he asked me for that information:The information what
will result is then inserted in a new table...
thanks|||Tom it works perfect ...
thanks for your help
"Tom Moreau" wrote:

> Try:
> select
> count (*)
> , email
> , max (regdatetime) regdatetime
> group by
> email
> having
> count (*) > 1
> order by
> regdatetime desc
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:B43ACCC2-9ADA-4B0B-B8E3-4DFDA2694567@.microsoft.com...
> hello,
> i have a table for example mytable with 2 fields
> email (varchar50) regdate(datetime)
> i want to have a list of emails which are more times registered - sort by
> last time when registered
>
> example of entries in the table
> u1@.dom1.com 26.03.2006 15:12:02
> u2@.dom1.com 24.03.2006 15:12:02
> u3@.dom1.com 24.03.2006 14:12:02
> u1@.dom1.com 23.03.2006 13:12:02
> u2@.dom1.com 22.03.2006 12:12:02
> u1@.dom1.com 21.03.2006 11:12:02
> u2@.dom1.com 20.03.2006 12:12:02
> u2@.dom1.com 19.03.2006 12:12:02
>
> i want to get something like
> 3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
> time
> 4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
> u3 - is not listed because it is only one time registered
>
> the information is sort desc by last registration time
> Yes i know what you think about the "tabledesign..." but my customer has
> such a table - and he asked me for that information:The information what
> will result is then inserted in a new table...
> thanks
>

get the AM/PM value stored in a database

How do I just return if a datetime field is AM or PM from sql server?
thanx
weisenbrI believe you have to use DATEDIFF and get the number of seconds elapsed since the last midnight. Then, if it's greater than 12x60x60 = 43,200, it's PM|||Depending on where you are trying to do it something like this would work:

SELECT CASE WHEN DATEPART(hh,getdate()) > 11 THEN 'PM' ELSE 'AM' END

get record with earliest datetime value

Hello all,

Quick sql syntax question:

I have this table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO

CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

with these values:

insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)

I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE

I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE

not sure if this is the way to go...

pointer appreciated
thanksHow about doing this:
1: Change Row_ID from NULL to NOT NULL
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NOT NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

2: Add value for column ROW_ID in INSERT:
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 1)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)

3: Use correct table name in SELECT - from REQUEST_LOG to REQUESTS
SELECT TOP 1 ROW_ID FROM REQUESTs WHERE STATUS = 0 ORDER BY
REQUEST_DATE

On Feb 9, 9:59 am, "hharry" <paulquig...@.nyc.comwrote:

Quote:

Originally Posted by

Hello all,
>
Quick sql syntax question:
>
I have this table:
>
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO
>
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO
>
with these values:
>
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
>
I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE
>
I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE
>
not sure if this is the way to go...
>
pointer appreciated
thanks

|||apologies for the typos

what i should have asked is this:

Is TOP applied after the ORDER BY or before...can someone confirm
this ?|||Yes, TOP is applied after the result set rows are ordered with ORDER BY.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

Get Modified datetime of an DB Object in SQL-Server 2000

Hi Wizzies,
Plz help me with this one. I know that I can get the CREATION datetime of a database object from "crdate" column of sysobjects table. Same way, is there anything in SQL-Server 2000 for fetching MODIFIED datetime of a database object?Not that I know of. In the past I've run nightly jobs against the database that compare current stored procedure CHECKSUM values against previous values to see if any were changed.

blindman