sql server 2k
I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.
I am currently using the following. Isn't there a more built in way to do
this?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'Here's one way...
--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
(Table_Qualfier varchar(100),
Table_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100),
Table_Count numeric(9))
--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables
--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||See view information_schema.tables and function objectproperty.
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go
AMB
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||Thanks to the two of you.
Alejandro, that was the ticket. Gracias!!
"Alejandro Mesa" wrote:
> See view information_schema.tables and function objectproperty.
> Example:
> use northwind
> go
> select
> *
> from
> information_schema.tables
> where
> table_type = 'base table'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
> AMB
> "kevin" wrote:
>
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Thursday, March 29, 2012
getting a date in the past
Hi,
Does anyone knows the select syntax for getting a date in the past but
close the current date.
For example: i have a table of addresses with an id, startdate, street,
etc. Now what i would like to do, is get the date that is close to the
current date. The outcome of it, is the current address of a person.
Is this possible with use of the columns id and startdate or just startdate?Please post DDL if you are refering to columns in your tables
http://www.aspfaq.com/5006
Select TOP 1 <columnlist>
>From SomeTable
Where id = <Someid>
Order by Startdate desc
HTH, Jens Suessmeyer.|||select DATEDIFF(dd, StartDate, getdate()), * from YourTable
order by DATEDIFF(dd, StartDate, getdate())
dd = Days. This can be substitued for hours, minutes, seconds etc. Have a
look at DATEDIFF function in SQL Books Online
HTH. Ryan
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:%23Spc$gZIGHA.1876@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone knows the select syntax for getting a date in the past but
> close the current date.
> For example: i have a table of addresses with an id, startdate, street,
> etc. Now what i would like to do, is get the date that is close to the
> current date. The outcome of it, is the current address of a person.
> Is this possible with use of the columns id and startdate or just
> startdate?
Does anyone knows the select syntax for getting a date in the past but
close the current date.
For example: i have a table of addresses with an id, startdate, street,
etc. Now what i would like to do, is get the date that is close to the
current date. The outcome of it, is the current address of a person.
Is this possible with use of the columns id and startdate or just startdate?Please post DDL if you are refering to columns in your tables
http://www.aspfaq.com/5006
Select TOP 1 <columnlist>
>From SomeTable
Where id = <Someid>
Order by Startdate desc
HTH, Jens Suessmeyer.|||select DATEDIFF(dd, StartDate, getdate()), * from YourTable
order by DATEDIFF(dd, StartDate, getdate())
dd = Days. This can be substitued for hours, minutes, seconds etc. Have a
look at DATEDIFF function in SQL Books Online
HTH. Ryan
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:%23Spc$gZIGHA.1876@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone knows the select syntax for getting a date in the past but
> close the current date.
> For example: i have a table of addresses with an id, startdate, street,
> etc. Now what i would like to do, is get the date that is close to the
> current date. The outcome of it, is the current address of a person.
> Is this possible with use of the columns id and startdate or just
> startdate?
Friday, March 23, 2012
GetDate() in SQL Server
Hi,
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' +
convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate
()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate())
, 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (T
his happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happe
ning?
Thanks,
C.Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' +
convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate
()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate())
, 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (T
his happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happe
ning?
Thanks,
C.Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...
GetDate() in SQL Server
Hi,
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate()), 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (This happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happening?
Thanks,
C.
Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.
|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.
|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate()), 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (This happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happening?
Thanks,
C.
Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.
|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.
|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...
GETDATE() Hangs periodically
We have GETDATE() within a stored procedure.
Create Procedure SP_GetDateTime AS
Select GetDate()
GO
Periodically the stored procedure will hang if the server
has approximately 200 users and the server is busy
processing numerous transactions. The server has Windows
2000 Advanced Server with a Active\Active Cluster and SQL
Server 2000 with SP3A.
What could cause the store procedure to hang?
Thanks,
MarkDid you look at sp_lock and/or sp_who/sp_who2 while this "hanging" was
occuring?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2da1f01c46a74$4a617cb0$a501280a@.phx.gbl...
> We have GETDATE() within a stored procedure.
> Create Procedure SP_GetDateTime AS
> Select GetDate()
> GO
> Periodically the stored procedure will hang if the server
> has approximately 200 users and the server is busy
> processing numerous transactions. The server has Windows
> 2000 Advanced Server with a Active\Active Cluster and SQL
> Server 2000 with SP3A.
> What could cause the store procedure to hang?
> Thanks,
> Marksql
Create Procedure SP_GetDateTime AS
Select GetDate()
GO
Periodically the stored procedure will hang if the server
has approximately 200 users and the server is busy
processing numerous transactions. The server has Windows
2000 Advanced Server with a Active\Active Cluster and SQL
Server 2000 with SP3A.
What could cause the store procedure to hang?
Thanks,
MarkDid you look at sp_lock and/or sp_who/sp_who2 while this "hanging" was
occuring?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2da1f01c46a74$4a617cb0$a501280a@.phx.gbl...
> We have GETDATE() within a stored procedure.
> Create Procedure SP_GetDateTime AS
> Select GetDate()
> GO
> Periodically the stored procedure will hang if the server
> has approximately 200 users and the server is busy
> processing numerous transactions. The server has Windows
> 2000 Advanced Server with a Active\Active Cluster and SQL
> Server 2000 with SP3A.
> What could cause the store procedure to hang?
> Thanks,
> Marksql
Wednesday, March 21, 2012
getdate() function
im trying to use getdate to just return me the date rather than date time
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
im just after "13/07/2005"
cheers
markMark,
have a look at this:
select convert(varchar(8),getdate(),3)
Rgds,
Paul Ibison, SQL Server MVP|||Mark,
You really need to understand that SQL Server does not have a DATE or a TIME
datatype. It only has DATETIME or SMALLDATETIME. It either case it always
includes the time portion. Even if you declare a DATETIME and only specify
the date portion it will automatically add the time of midnight. The only
want to display just the date portion (without using a gui that formats it
for you) is to convert it into a string. In your case you are trying to
stuff it back into a smalldatetime datatype which will simply add the time
portion back on again. Change the datatype of the variable to varchar and
you will make life a lot easier.
--
Andrew J. Kelly SQL MVP
"mark" <mark@.remove.com> wrote in message
news:1121254148.64416.0@.despina.uk.clara.net...
> "mark" <mark@.remove.com> wrote in message
> news:1121253845.64331.0@.despina.uk.clara.net...
>> im trying to use getdate to just return me the date rather than date time
>> declare @.todaysdate smalldatetime
>> select @.todaysdate= getdate()
>> im just after "13/07/2005"
>> cheers
>> mark
> i fixed it with this crazy procedure, surely theres an easier way
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> declare @.month varchar(10)
> select @.month =datepart(mm,@.todaysdate)
> declare @.day varchar(10)
> select @.day =datepart(dd,@.todaysdate)
> declare @.year varchar(10)
> select @.year =datepart(yyyy,@.todaysdate)
> select @.todaysdate = @.day +'/' + @.month + '/' + @.year
> cheers
> mark
>|||You should believe us that there IS NO WAY getting only the date from the
getdate() function, SQL Server has no idea about only a date, thats not now
as a datetime type, the only thing would be to insert something using the
convert function like CONVERT(varchar(10), Getdate(),120) or something like
that, instead of using that you can change to IDW 3 on SQL Server 2005 where
actually was a understanding of TIME OR DATE, but they changed it in further
development, but summarized, there is now way for doing that.
HTH, Jens Suessmeyer.
"mark" wrote:
> i might not have explained it well enough,
> im trying to put the currentdate into a column in a database on an insert
> using getdate()
> currently using getdate() and getting current date and time - which is not
> what i need, i only need to record the date not the time
>
>|||"Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> You should believe us that there IS NO WAY getting only the date from the
> getdate() function, SQL Server has no idea about only a date, thats not
now
> as a datetime type, the only thing would be to insert something using the
> convert function like CONVERT(varchar(10), Getdate(),120) or something
like
> that, instead of using that you can change to IDW 3 on SQL Server 2005
where
> actually was a understanding of TIME OR DATE, but they changed it in
further
> development, but summarized, there is now way for doing that.
> HTH, Jens Suessmeyer.
>
so you would recommend passing the date from an app to the stored procedure
instead ?
(might be easier)
cheers
mark|||Mark:
Even passing the date to a stored procedure will not work. The database
will STORE your date as a datetime type which means if you pass '13/07/2005'
it will store it as '13/07/2005 00:00:00.000'. You can use an app to only
display and edit the date, but the date will always store as a datetime type
(which will add a MIDNIGHT time). You can also use the convert function to
display your datetime as just a "date string" using CONVERT(VARCHAR(10),
GETDATE(), 103) but as you can see, this actually converts your date into a
string and is treated as a string from then on (sorting is string based
then). Now if you actually want it strip out the time element of GETDATE()
you can use CAST(CONVERT(VARCHAR(10), GETDATE(), 102) AS DATETIME) which
will give you today's date with a midnight time. This will match any where
statement where you just specify just a date e.g. DateField = '2005-07-13'
because this will be converted automatically to '2005-07-13 00:00:00.000'
The question is "Why do you care so much that the database ONLY store the
date?" After all, the database never stores '13/07/2005' in that exact
format anyway. It stores it as a floating value that is calculated from a
set point in time. If you store something in a datetime field, I can get it
out in any format I desire (see the table listing under the "CAST and
CONVERT" topic in BOL). Which is the way it should be to allow for
international usage. In the UK you can display it in UK style and in the US
you can display in the US style. Same date, just displayed differently.
Scott
"mark" <mark@.remove.com> wrote in message
news:1121266783.5639.0@.lotis.uk.clara.net...
> "Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
>> You should believe us that there IS NO WAY getting only the date from the
>> getdate() function, SQL Server has no idea about only a date, thats not
> now
>> as a datetime type, the only thing would be to insert something using the
>> convert function like CONVERT(varchar(10), Getdate(),120) or something
> like
>> that, instead of using that you can change to IDW 3 on SQL Server 2005
> where
>> actually was a understanding of TIME OR DATE, but they changed it in
> further
>> development, but summarized, there is now way for doing that.
>> HTH, Jens Suessmeyer.
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>sql
declare @.todaysdate smalldatetime
select @.todaysdate= getdate()
im just after "13/07/2005"
cheers
markMark,
have a look at this:
select convert(varchar(8),getdate(),3)
Rgds,
Paul Ibison, SQL Server MVP|||Mark,
You really need to understand that SQL Server does not have a DATE or a TIME
datatype. It only has DATETIME or SMALLDATETIME. It either case it always
includes the time portion. Even if you declare a DATETIME and only specify
the date portion it will automatically add the time of midnight. The only
want to display just the date portion (without using a gui that formats it
for you) is to convert it into a string. In your case you are trying to
stuff it back into a smalldatetime datatype which will simply add the time
portion back on again. Change the datatype of the variable to varchar and
you will make life a lot easier.
--
Andrew J. Kelly SQL MVP
"mark" <mark@.remove.com> wrote in message
news:1121254148.64416.0@.despina.uk.clara.net...
> "mark" <mark@.remove.com> wrote in message
> news:1121253845.64331.0@.despina.uk.clara.net...
>> im trying to use getdate to just return me the date rather than date time
>> declare @.todaysdate smalldatetime
>> select @.todaysdate= getdate()
>> im just after "13/07/2005"
>> cheers
>> mark
> i fixed it with this crazy procedure, surely theres an easier way
> declare @.todaysdate smalldatetime
> select @.todaysdate= getdate()
> declare @.month varchar(10)
> select @.month =datepart(mm,@.todaysdate)
> declare @.day varchar(10)
> select @.day =datepart(dd,@.todaysdate)
> declare @.year varchar(10)
> select @.year =datepart(yyyy,@.todaysdate)
> select @.todaysdate = @.day +'/' + @.month + '/' + @.year
> cheers
> mark
>|||You should believe us that there IS NO WAY getting only the date from the
getdate() function, SQL Server has no idea about only a date, thats not now
as a datetime type, the only thing would be to insert something using the
convert function like CONVERT(varchar(10), Getdate(),120) or something like
that, instead of using that you can change to IDW 3 on SQL Server 2005 where
actually was a understanding of TIME OR DATE, but they changed it in further
development, but summarized, there is now way for doing that.
HTH, Jens Suessmeyer.
"mark" wrote:
> i might not have explained it well enough,
> im trying to put the currentdate into a column in a database on an insert
> using getdate()
> currently using getdate() and getting current date and time - which is not
> what i need, i only need to record the date not the time
>
>|||"Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> You should believe us that there IS NO WAY getting only the date from the
> getdate() function, SQL Server has no idea about only a date, thats not
now
> as a datetime type, the only thing would be to insert something using the
> convert function like CONVERT(varchar(10), Getdate(),120) or something
like
> that, instead of using that you can change to IDW 3 on SQL Server 2005
where
> actually was a understanding of TIME OR DATE, but they changed it in
further
> development, but summarized, there is now way for doing that.
> HTH, Jens Suessmeyer.
>
so you would recommend passing the date from an app to the stored procedure
instead ?
(might be easier)
cheers
mark|||Mark:
Even passing the date to a stored procedure will not work. The database
will STORE your date as a datetime type which means if you pass '13/07/2005'
it will store it as '13/07/2005 00:00:00.000'. You can use an app to only
display and edit the date, but the date will always store as a datetime type
(which will add a MIDNIGHT time). You can also use the convert function to
display your datetime as just a "date string" using CONVERT(VARCHAR(10),
GETDATE(), 103) but as you can see, this actually converts your date into a
string and is treated as a string from then on (sorting is string based
then). Now if you actually want it strip out the time element of GETDATE()
you can use CAST(CONVERT(VARCHAR(10), GETDATE(), 102) AS DATETIME) which
will give you today's date with a midnight time. This will match any where
statement where you just specify just a date e.g. DateField = '2005-07-13'
because this will be converted automatically to '2005-07-13 00:00:00.000'
The question is "Why do you care so much that the database ONLY store the
date?" After all, the database never stores '13/07/2005' in that exact
format anyway. It stores it as a floating value that is calculated from a
set point in time. If you store something in a datetime field, I can get it
out in any format I desire (see the table listing under the "CAST and
CONVERT" topic in BOL). Which is the way it should be to allow for
international usage. In the UK you can display it in UK style and in the US
you can display in the US style. Same date, just displayed differently.
Scott
"mark" <mark@.remove.com> wrote in message
news:1121266783.5639.0@.lotis.uk.clara.net...
> "Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
>> You should believe us that there IS NO WAY getting only the date from the
>> getdate() function, SQL Server has no idea about only a date, thats not
> now
>> as a datetime type, the only thing would be to insert something using the
>> convert function like CONVERT(varchar(10), Getdate(),120) or something
> like
>> that, instead of using that you can change to IDW 3 on SQL Server 2005
> where
>> actually was a understanding of TIME OR DATE, but they changed it in
> further
>> development, but summarized, there is now way for doing that.
>> HTH, Jens Suessmeyer.
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>sql
GetDate() Does not Return Milliseconds ?
Ladies / Gentlemen
If you run the following select statement you will find as I did
that GetDate() does no obtain Milliseconds - Why Not and can I use something
that does ?
Mark Moss
SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
CONVERT(varchar(20), GETDATE(), 109) AS Expr1Hi Mark
The getdate() function ALWAYS returns milliseconds. It's only when you
convert it to character that the milliseconds might be discarded.
In this situation, you have not provided enough space to hold the
milliseconds. Try varchar(25) instead of varchar(20)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"msnews.microsoft.com" <markmoss@.adelphia.net> wrote in message
news:%234V0y1ucGHA.4428@.TK2MSFTNGP03.phx.gbl...
> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use
> something that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>|||Mark,
GETDATE() returns a datetime, which includes a millisec part. If you do
"SELECT DATEPART(ms,GETDATE())" you'll see this. Your problem is that
you're converting to a 20 char string and the millisec bit it getting
truncated. It's a string truncation issue, not a datetime issue. Try
using a varchar(30) or something bigger.
Also, you ought to use CURRENT_TIMESTAMP rather than GETDATE() as
CURRENT_TIMESTAMP is the ANSI equivalent (GETDATE() is Microsoft
proprietary) and in the majority of cases you should leave presentation
of the data up to the presentation layer (ie. the client) rather than
the DB engine.
*mike hodgson*
http://sqlnerd.blogspot.com
msnews.microsoft.com wrote:
>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||The best way to get the format you want is through an user interface. VB
converts the SQL GETDATE() with milliseconds very well.
You can use
SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr2,
CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr1
and your milliseconds will show
Thanks Kllyj64
"msnews.microsoft.com" wrote:
> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use somethi
ng
> that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||lol..or you could just change the size of your VARCHAR()....
--
Thanks Kllyj64
"kllyj64" wrote:
> The best way to get the format you want is through an user interface. VB
> converts the SQL GETDATE() with milliseconds very well.
> You can use
> SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3)
,
> DATEPART(MS,GETDATE())) AS Expr2,
> CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
> DATEPART(MS,GETDATE())) AS Expr1
> and your milliseconds will show
>
> --
> Thanks Kllyj64
>
> "msnews.microsoft.com" wrote:
>|||Just increase VARCHAR to 40. 20 is too short and thus the reason why
msec is left off.
Mark
On Mon, 8 May 2006 16:19:10 -0600, "msnews.microsoft.com"
<markmoss@.adelphia.net> wrote:
>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
If you run the following select statement you will find as I did
that GetDate() does no obtain Milliseconds - Why Not and can I use something
that does ?
Mark Moss
SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
CONVERT(varchar(20), GETDATE(), 109) AS Expr1Hi Mark
The getdate() function ALWAYS returns milliseconds. It's only when you
convert it to character that the milliseconds might be discarded.
In this situation, you have not provided enough space to hold the
milliseconds. Try varchar(25) instead of varchar(20)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"msnews.microsoft.com" <markmoss@.adelphia.net> wrote in message
news:%234V0y1ucGHA.4428@.TK2MSFTNGP03.phx.gbl...
> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use
> something that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>|||Mark,
GETDATE() returns a datetime, which includes a millisec part. If you do
"SELECT DATEPART(ms,GETDATE())" you'll see this. Your problem is that
you're converting to a 20 char string and the millisec bit it getting
truncated. It's a string truncation issue, not a datetime issue. Try
using a varchar(30) or something bigger.
Also, you ought to use CURRENT_TIMESTAMP rather than GETDATE() as
CURRENT_TIMESTAMP is the ANSI equivalent (GETDATE() is Microsoft
proprietary) and in the majority of cases you should leave presentation
of the data up to the presentation layer (ie. the client) rather than
the DB engine.
*mike hodgson*
http://sqlnerd.blogspot.com
msnews.microsoft.com wrote:
>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||The best way to get the format you want is through an user interface. VB
converts the SQL GETDATE() with milliseconds very well.
You can use
SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr2,
CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
DATEPART(MS,GETDATE())) AS Expr1
and your milliseconds will show
Thanks Kllyj64
"msnews.microsoft.com" wrote:
> Ladies / Gentlemen
> If you run the following select statement you will find as I did
> that GetDate() does no obtain Milliseconds - Why Not and can I use somethi
ng
> that does ?
>
> Mark Moss
>
> SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
> CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
>|||lol..or you could just change the size of your VARCHAR()....
--
Thanks Kllyj64
"kllyj64" wrote:
> The best way to get the format you want is through an user interface. VB
> converts the SQL GETDATE() with milliseconds very well.
> You can use
> SELECT CONVERT(varchar(20), GETDATE(), 113) + '.' + CONVERT(VARCHAR(3)
,
> DATEPART(MS,GETDATE())) AS Expr2,
> CONVERT(varchar(20), GETDATE() , 109)+ '.' + CONVERT(VARCHAR(3),
> DATEPART(MS,GETDATE())) AS Expr1
> and your milliseconds will show
>
> --
> Thanks Kllyj64
>
> "msnews.microsoft.com" wrote:
>|||Just increase VARCHAR to 40. 20 is too short and thus the reason why
msec is left off.
Mark
On Mon, 8 May 2006 16:19:10 -0600, "msnews.microsoft.com"
<markmoss@.adelphia.net> wrote:
>Ladies / Gentlemen
> If you run the following select statement you will find as I did
>that GetDate() does no obtain Milliseconds - Why Not and can I use somethin
g
>that does ?
>
>Mark Moss
>
>SELECT CONVERT(varchar(20), GETDATE(), 113) AS Expr2,
>CONVERT(varchar(20), GETDATE(), 109) AS Expr1
>
getdate()
Nothing I do changes the format of the date.
declare @.PubDate datetime
set @.PubDate = convert(varchar,getdate(),111)
SELECT @.PubDate,*
FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
I need it to show the date like yyyy/mm/ddYou can't change the display if it is the variable is declared as a datetime
datatype with convert. Make it a varchar instead.
> declare @.PubDate VARCHAR(24)
> set @.PubDate = convert(varchar(24),getdate(),111)
Andrew J. Kelly SQL MVP
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
> Nothing I do changes the format of the date.
> declare @.PubDate datetime
> set @.PubDate = convert(varchar,getdate(),111)
> SELECT @.PubDate,*
> FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
> DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
> I need it to show the date like yyyy/mm/dd
>|||Thank you. Your answer fixed the formatting issue, but my query doesn't
return any results when it should. It returns results if I hard code the dat
e
in y/m/d format in place of the @.PubDate in my query. I tried
convert(datetime, getdate(), 111), but that, did not solve my problem. Any
other sugestions?
"Andrew J. Kelly" wrote:
> You can't change the display if it is the variable is declared as a dateti
me
> datatype with convert. Make it a varchar instead.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
>
>|||Well I have no idea what your function DATE_SUB() is doing but you should
have a look at these:
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
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:6930C3C9-2AD7-4259-A7E9-2D4A575C169D@.microsoft.com...
> Thank you. Your answer fixed the formatting issue, but my query doesn't
> return any results when it should. It returns results if I hard code the
> date
> in y/m/d format in place of the @.PubDate in my query. I tried
> convert(datetime, getdate(), 111), but that, did not solve my problem.
> Any
> other sugestions?
> "Andrew J. Kelly" wrote:
>|||Curtis,
I'm surprised this doesn't throw an error, because @.PubDate cannot
be used within the OPENQUERY statement. In addition, since you
have declared @.PubDate as datetime, it does not have a format, and
when used where a string is expected, it will be converted using the
default string format.
You have two options, unless you've hidden some secret about
how @.PubDate is working in the query:
If you are using SQL Server 2005, you can do this:
EXECUTE(
N'SELECT ?, * FROM articles
WHERE Weight = 3 AND DateInserted >= DATE_SUB(?,INTERVAL 15 DAY)',
@.PubDate, @.PubDate) at MySQL
You may or may not have to declare @.PubDate as a string and pre-convert
it--I don't know what your DATE_SUB function expects.
Alternatively, you can create the entire openquery string dynamically:
DECLARE @.sql nvarchar(1000)
DECLARE @.PubDate datetime
SET @.sql = N'SELECT ''?'', * FROM articles
WHERE Weight = 3 AND DateInserted >= DATE_SUB(''?'',INTERVAL 15 DAY)'
SET @.sql = REPLACE(@.sql,'?',CONVERT(varchar,getdate(),111)
EXEC(@.sql)
Be absolutely certain that ? is replaced by something you constructed
yourself from a datetime. Do not let the user provide the substitution
string, or you risk SQL Injection from a maliciously-formed replacement
string.
Steve Kass
Drew University
Curtis wrote:
>Nothing I do changes the format of the date.
>declare @.PubDate datetime
>set @.PubDate = convert(varchar,getdate(),111)
> SELECT @.PubDate,*
>FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
>DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
>I need it to show the date like yyyy/mm/dd
>
>
declare @.PubDate datetime
set @.PubDate = convert(varchar,getdate(),111)
SELECT @.PubDate,*
FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
I need it to show the date like yyyy/mm/ddYou can't change the display if it is the variable is declared as a datetime
datatype with convert. Make it a varchar instead.
> declare @.PubDate VARCHAR(24)
> set @.PubDate = convert(varchar(24),getdate(),111)
Andrew J. Kelly SQL MVP
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
> Nothing I do changes the format of the date.
> declare @.PubDate datetime
> set @.PubDate = convert(varchar,getdate(),111)
> SELECT @.PubDate,*
> FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
> DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
> I need it to show the date like yyyy/mm/dd
>|||Thank you. Your answer fixed the formatting issue, but my query doesn't
return any results when it should. It returns results if I hard code the dat
e
in y/m/d format in place of the @.PubDate in my query. I tried
convert(datetime, getdate(), 111), but that, did not solve my problem. Any
other sugestions?
"Andrew J. Kelly" wrote:
> You can't change the display if it is the variable is declared as a dateti
me
> datatype with convert. Make it a varchar instead.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:A05FC7F0-DADD-4678-B354-E7B2FA8E8A78@.microsoft.com...
>
>|||Well I have no idea what your function DATE_SUB() is doing but you should
have a look at these:
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
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:6930C3C9-2AD7-4259-A7E9-2D4A575C169D@.microsoft.com...
> Thank you. Your answer fixed the formatting issue, but my query doesn't
> return any results when it should. It returns results if I hard code the
> date
> in y/m/d format in place of the @.PubDate in my query. I tried
> convert(datetime, getdate(), 111), but that, did not solve my problem.
> Any
> other sugestions?
> "Andrew J. Kelly" wrote:
>|||Curtis,
I'm surprised this doesn't throw an error, because @.PubDate cannot
be used within the OPENQUERY statement. In addition, since you
have declared @.PubDate as datetime, it does not have a format, and
when used where a string is expected, it will be converted using the
default string format.
You have two options, unless you've hidden some secret about
how @.PubDate is working in the query:
If you are using SQL Server 2005, you can do this:
EXECUTE(
N'SELECT ?, * FROM articles
WHERE Weight = 3 AND DateInserted >= DATE_SUB(?,INTERVAL 15 DAY)',
@.PubDate, @.PubDate) at MySQL
You may or may not have to declare @.PubDate as a string and pre-convert
it--I don't know what your DATE_SUB function expects.
Alternatively, you can create the entire openquery string dynamically:
DECLARE @.sql nvarchar(1000)
DECLARE @.PubDate datetime
SET @.sql = N'SELECT ''?'', * FROM articles
WHERE Weight = 3 AND DateInserted >= DATE_SUB(''?'',INTERVAL 15 DAY)'
SET @.sql = REPLACE(@.sql,'?',CONVERT(varchar,getdate(),111)
EXEC(@.sql)
Be absolutely certain that ? is replaced by something you constructed
yourself from a datetime. Do not let the user provide the substitution
string, or you risk SQL Injection from a maliciously-formed replacement
string.
Steve Kass
Drew University
Curtis wrote:
>Nothing I do changes the format of the date.
>declare @.PubDate datetime
>set @.PubDate = convert(varchar,getdate(),111)
> SELECT @.PubDate,*
>FROM OPENquery(MySQL, 'SELECT * FROM articles WHERE Weight = 3 AND
>DateInserted >= DATE_SUB(@.PubDate,INTERVAL 15 DAY) ')
>I need it to show the date like yyyy/mm/dd
>
>
Getdate Function
How do I modify the statement listed below to give me the date with the time
of 12:00 AM.
declare @.Yester_day smalldatetime
set @.Yester_day = (select getdate()-1)
print @.Yester_day '
Output
2005-08-30 10:48:12.127Select DATEADD(hh,-12,CONVERT(varchar(50),getdate(),112))
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe K." wrote:
> How do I modify the statement listed below to give me the date with the ti
me
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>|||DECLARE @.Yesterday SMALLDATETIME -- why the underbar?
SET @.Yesterday = DATEDIFF(DAY,1,GETDATE())
SELECT @.Yesterday
Or more elaborately:
DECLARE @.Yesterday SMALLDATETIME
SET @.Yesterday = DATEADD(DAY, -1, DATEDIFF(DAY, 0, GETDATE()))
SELECT @.Yesterday
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7F1A26A7-E861-420C-B318-4F57A6C31425@.microsoft.com...
> How do I modify the statement listed below to give me the date with the
> time
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>|||declare @.Yester_day smalldatetime
set @.Yester_day = (select cast (floor(cast (getdate()-1 as float))as
datetime))
print @.Yester_day
"Joe K." wrote:
> How do I modify the statement listed below to give me the date with the ti
me
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>
of 12:00 AM.
declare @.Yester_day smalldatetime
set @.Yester_day = (select getdate()-1)
print @.Yester_day '
Output
2005-08-30 10:48:12.127Select DATEADD(hh,-12,CONVERT(varchar(50),getdate(),112))
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe K." wrote:
> How do I modify the statement listed below to give me the date with the ti
me
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>|||DECLARE @.Yesterday SMALLDATETIME -- why the underbar?
SET @.Yesterday = DATEDIFF(DAY,1,GETDATE())
SELECT @.Yesterday
Or more elaborately:
DECLARE @.Yesterday SMALLDATETIME
SET @.Yesterday = DATEADD(DAY, -1, DATEDIFF(DAY, 0, GETDATE()))
SELECT @.Yesterday
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7F1A26A7-E861-420C-B318-4F57A6C31425@.microsoft.com...
> How do I modify the statement listed below to give me the date with the
> time
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>|||declare @.Yester_day smalldatetime
set @.Yester_day = (select cast (floor(cast (getdate()-1 as float))as
datetime))
print @.Yester_day
"Joe K." wrote:
> How do I modify the statement listed below to give me the date with the ti
me
> of 12:00 AM.
>
> declare @.Yester_day smalldatetime
> set @.Yester_day = (select getdate()-1)
> print @.Yester_day '
> Output
> 2005-08-30 10:48:12.127
>
>
GetDate as Parameter for UDF Function returns table
Help!!
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.
Simply CAST it as a varchar..
.....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo.hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.
|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
a[vbcol=seagreen]
> GetDate(),'10/30/2004','Company')
use
>
|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
you[vbcol=seagreen]
returns
> a
> use
>
|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> you
for[vbcol=seagreen]
> returns
to
>
|||Create a view
create view v_mydate as
select getdate() mydate
Then you can reference v_mydate.mydate in your function.
But see here for a warning about non-deterministic UDFs.
http://www.insidesql.de/content/view/100/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.
Simply CAST it as a varchar..
.....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo.hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.
|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
a[vbcol=seagreen]
> GetDate(),'10/30/2004','Company')
use
>
|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
you[vbcol=seagreen]
returns
> a
> use
>
|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> you
for[vbcol=seagreen]
> returns
to
>
|||Create a view
create view v_mydate as
select getdate() mydate
Then you can reference v_mydate.mydate in your function.
But see here for a warning about non-deterministic UDFs.
http://www.insidesql.de/content/view/100/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>
GetDate as Parameter for UDF Function returns table
Help!!
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.Simply CAST it as a varchar..
....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo.hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > Help!!
> >
> > I am trying to pass GetDate() as a paramter into a function that returns
a
> > table.
> >
> > select * from
> > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> GetDate(),'10/30/2004','Company')
> >
> > This fails but it works if I pass in the date as a string. I need to
use
> > GetDate.
> >
> > I cannot create a local variable, this is a view.
>|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > Simply CAST it as a varchar..
> >
> > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> >
> > Note,
> >
> > You may wish to parse it out to get only the portions of the date that
you
> > want...
> >
> > You could try casting it as a decimal as well. That may work better for
> > you.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
> > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > Help!!
> > >
> > > I am trying to pass GetDate() as a paramter into a function that
returns
> a
> > > table.
> > >
> > > select * from
> > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > GetDate(),'10/30/2004','Company')
> > >
> > > This fails but it works if I pass in the date as a string. I need to
> use
> > > GetDate.
> > >
> > > I cannot create a local variable, this is a view.
> >
> >
>|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> > Rick
> > Your suggestion does not work
> >
> > CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> > RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> > AS
> > BEGIN
> > INSERT INTO @.Dates VALUES(@.dt)
> > RETURN
> > END
> >
> > --Doesnt work (as you suggested)
> > SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> > --Does work
> > DECLARE @.dt DATETIME
> > SET @.dt=GETDATE()
> > SELECT * from dbo.fn_dates (@.dt)
> >
> >
> >
> > "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> > news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > > Simply CAST it as a varchar..
> > >
> > > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> > >
> > > Note,
> > >
> > > You may wish to parse it out to get only the portions of the date that
> you
> > > want...
> > >
> > > You could try casting it as a decimal as well. That may work better
for
> > > you.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> > >
> > > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > > Help!!
> > > >
> > > > I am trying to pass GetDate() as a paramter into a function that
> returns
> > a
> > > > table.
> > > >
> > > > select * from
> > > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > > GetDate(),'10/30/2004','Company')
> > > >
> > > > This fails but it works if I pass in the date as a string. I need
to
> > use
> > > > GetDate.
> > > >
> > > > I cannot create a local variable, this is a view.
> > >
> > >
> >
> >
>|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>sql
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.Simply CAST it as a varchar..
....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo.hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > Help!!
> >
> > I am trying to pass GetDate() as a paramter into a function that returns
a
> > table.
> >
> > select * from
> > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> GetDate(),'10/30/2004','Company')
> >
> > This fails but it works if I pass in the date as a string. I need to
use
> > GetDate.
> >
> > I cannot create a local variable, this is a view.
>|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > Simply CAST it as a varchar..
> >
> > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> >
> > Note,
> >
> > You may wish to parse it out to get only the portions of the date that
you
> > want...
> >
> > You could try casting it as a decimal as well. That may work better for
> > you.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
> > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > Help!!
> > >
> > > I am trying to pass GetDate() as a paramter into a function that
returns
> a
> > > table.
> > >
> > > select * from
> > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > GetDate(),'10/30/2004','Company')
> > >
> > > This fails but it works if I pass in the date as a string. I need to
> use
> > > GetDate.
> > >
> > > I cannot create a local variable, this is a view.
> >
> >
>|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> > Rick
> > Your suggestion does not work
> >
> > CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> > RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> > AS
> > BEGIN
> > INSERT INTO @.Dates VALUES(@.dt)
> > RETURN
> > END
> >
> > --Doesnt work (as you suggested)
> > SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> > --Does work
> > DECLARE @.dt DATETIME
> > SET @.dt=GETDATE()
> > SELECT * from dbo.fn_dates (@.dt)
> >
> >
> >
> > "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> > news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > > Simply CAST it as a varchar..
> > >
> > > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> > >
> > > Note,
> > >
> > > You may wish to parse it out to get only the portions of the date that
> you
> > > want...
> > >
> > > You could try casting it as a decimal as well. That may work better
for
> > > you.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> > >
> > > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > > Help!!
> > > >
> > > > I am trying to pass GetDate() as a paramter into a function that
> returns
> > a
> > > > table.
> > > >
> > > > select * from
> > > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > > GetDate(),'10/30/2004','Company')
> > > >
> > > > This fails but it works if I pass in the date as a string. I need
to
> > use
> > > > GetDate.
> > > >
> > > > I cannot create a local variable, this is a view.
> > >
> > >
> >
> >
>|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>sql
GetDate as Parameter for UDF Function returns table
Help!!
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo. hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company
')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.Simply CAST it as a varchar..
....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo. hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
a[vbcol=seagreen]
> GetDate(),'10/30/2004','Company')
use[vbcol=seagreen]
>|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
you[vbcol=seagreen]
returns[vbcol=seagreen]
> a
> use
>|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> you
for[vbcol=seagreen]
> returns
to[vbcol=seagreen]
>|||Create a view
create view v_mydate as
select getdate() mydate
Then you can reference v_mydate.mydate in your function.
But see here for a warning about non-deterministic UDFs.
http://www.insidesql.de/content/view/100/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo. hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Compan
y')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo. hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company
')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.Simply CAST it as a varchar..
....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo. hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
a[vbcol=seagreen]
> GetDate(),'10/30/2004','Company')
use[vbcol=seagreen]
>|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
you[vbcol=seagreen]
returns[vbcol=seagreen]
> a
> use
>|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> you
for[vbcol=seagreen]
> returns
to[vbcol=seagreen]
>|||Create a view
create view v_mydate as
select getdate() mydate
Then you can reference v_mydate.mydate in your function.
But see here for a warning about non-deterministic UDFs.
http://www.insidesql.de/content/view/100/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo. hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Compan
y')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>
Monday, March 19, 2012
Get XML On Its Merry Way
Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lq
Hello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lq
Hello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
Get XML On Its Merry Way
Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lqHello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lqHello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
get value from SQL server 2005 select statement with datareader
I just want a simple datareader, that i can read the value returned from a select statement executed on a SQL server 2005 db.
The code below should work in, but email[calc]= rdr[0].ToString(); when i want to read some data a get a exception saying:
System.InvalidOperationException was unhandled by user code
Message="Invalid attempt to read when no data is present."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)
at _Default.Login_Click(Object sender, EventArgs e) in d:\My Documents\Visual Studio 2005\WebSites\WebSite1\Default.aspx.cs:line 47
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
If anybody could advise me where my stupid mistake is then i would highly appreciate it!
SqlConnection conn = new SqlConnection(getConnection());
SqlDataReader rdr = null;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Customer";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
int calc = 0;
Boolean login = false;
string[] email = new string[100];
object[] password = new object[100];
while (rdr.HasRows) // or rdr.Read();
{
rdr.Read();
email[calc]= rdr[0].ToString();
password[calc] = rdr["Password"].ToString();
if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc]))
{
login = true;
}
calc++;
}
}
finally
{
rdr.Close();
}
thanks...
Try this:
if (rdr.HasRows)// or rdr.Read(); {while(rdr.Read()) { email[calc]= rdr[0].ToString(); password[calc] = rdr["Password"].ToString();if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc])) { login =true; } calc++; } } Hope this helps.
Get value from datasource in codebehind
Lets say I have a Sqldatasource that uses the following SelectCommand="SELECT category,name FROM table". How do I get the value on category from my datasource in code behind if I know that my selectcommand always will return one row? Can I write something like datasource.items["category"].Value?
Thanks for your help!
You can retrieve the value from your datasource from either dataview or datareader. Here is a sample for your reference:
You can access your SqlDataSouce from code behind through a dataview or datareader by calling select() of the SqlDatasource. If theDataSourceMode property of the SqlDatasource is set to DataSet and you get the dataview(this is default), or a DataReader if it is set to DataReader.
'Programmatically access the SqlDataSource - get back a DataView
Dim dview As DataView = CType(yourSqlDataSource.Select(DataSourceSelectArguments.Empty), DataView)
Dim str1 as string = String.Empty
For Each drow As DataRow In dview.Table.Rows
str1 &= drow("yourcol1").ToString() & "<br />"
NEXT
Or through a datareader ( don't forget to set DataSourceMode property to DataReader)
Dim myreader as SqlDataReader=CType(rndProductsDataSource.Select(DataSourceSelectArguments.Empty), SqlDataReader)
Dim str1 as string=String.Empty
if myreader.Read()
str1= reader(0) ' or your first column name
else
end if
myreader.Close()
Monday, March 12, 2012
Get the XML out of sql server 2005 in c#
Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it?
Pls reply as soon as possible.
Cheers
Hi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it?
> Pls reply as soon as possible.
> Cheers
>
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it?
Pls reply as soon as possible.
Cheers
Hi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it?
> Pls reply as soon as possible.
> Cheers
>
Get the XML out of sql server 2005 in c#
Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>
Get the XML out of sql server 2005 in c#
Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/...ortSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/...ortSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>
Friday, March 9, 2012
Get The Last Record by Grouping
I have a view listing tickets and reports for those tickets. I want to query LAST REPORT's OPERATOR
SELECT OPERATOR AS EXPR2, NUMBERPRGN, IS_BITIS AS EXPR1
FROM SCADMIN.V_ESKALASYON_2
WHERE (NUMBERPRGN = 'IM1289657')
ORDER BY NUMBERPRGN, IS_BITIS DESC
That query brings the resultset
Can you help to recover the query sentence above to return only the red marked record (LAST REPORT info written)
Thanks :)
SELECT TOP (1)...|||Hoops, I have forgotten to say that I use that sentence to query from Oracle (SSIS). And there are lots of ticket numbers. I want to query only the red bold ones from Oracle (the criteria of red bold records is that they are last report for the ticket)
Could you help me?
I don't know Oracle very well... I hope that this works...
SELECT NUMBERPRGN, OPERATOR, IS_BITIS
FROM SCADMIN.V_ESKALASYON_2 AS Main INNER JOIN
(SELECT NUMBERPRGN, MAX(IS_BITIS) AS Date
FROM SCADMIN.V_ESKALASYON_2
GROUP BY NUMBERPRGN) AS Sub ON Main.NUMBERPRGN = Sub.NUMBERPRGN AND Main.IS_BITIS = Sub.Date
ORDER BY NUMBERPRGN
Subscribe to:
Posts (Atom)