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?

Getting a database on one server to match another

Sorry wasn't too clear was I.
The second server is our disaster recovery server, so we
want our second server database to be as up to date as
possible with the production database.
ta

>--Original Message--
>Hello,
>We have two servers and want out production db on server
1
>to match the production server on server 2.
>Whats the best way of doing this?
>.
>Sarah,
Hari already answered this with:
1) Log Shipping
2) Transactional replication
If the disaster recovery server does not need to be operational, then log
shipping would be good.You may also want to consider Double-Take from NSI
Software. This replicates the SQL Server data files at the block level on
disk and ensures transactional consistency.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...[vbcol=seagreen]
> Sorry wasn't too clear was I.
> The second server is our disaster recovery server, so we
> want our second server database to be as up to date as
> possible with the production database.
> ta
>
> 1|||Thanks Mark,
I'll have a look at transactional replication as we backup
the Production db by log files and to be honest I'm not
too sure on what will happen if we change it so it updates
asap.
Thanks for your time

>--Original Message--
>Sarah,
>Hari already answered this with:
>1) Log Shipping
>2) Transactional replication
>If the disaster recovery server does not need to be
operational, then log
>shipping would be good.You may also want to consider
Double-Take from NSI
>Software. This replicates the SQL Server data files at
the block level on
>disk and ensures transactional consistency.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>
>
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
server[vbcol=seagreen]
>
>.
>|||There's a web site at MS called something like "Pattern and practices". Ther
e you find a two boos on SQL
Server and high availability. You really need to go through that if you inte
nd to use replication for fail
over. There are some issues you need to plan for.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4ae801c42c41$337182f0$a4
01280a@.phx.gbl...[vbcol=seagreen]
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> operational, then log
> Double-Take from NSI
> the block level on
> message
> server|||Sarah,
I really don't think transactional replication is a good high availability
solution. There are many drawbacks to using this. Do a search on the web for
high availability sql server and you should find some good information.
Kimberley Tripp did a great presentation on high availability, perhaps
search for that.
Log shipping might be a good way to go, you can have a latency of 5 minutes
with this solution.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4ae801c42c41$337182f0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> operational, then log
> Double-Take from NSI
> the block level on
> message
> server|||Thanks for your help guys

>--Original Message--
>Thanks Mark,
>I'll have a look at transactional replication as we
backup
>the Production db by log files and to be honest I'm not
>too sure on what will happen if we change it so it
updates
>asap.
>Thanks for your time
>
>
>operational, then log
>Double-Take from NSI
>the block level on
>message
we[vbcol=seagreen]
>server
>.
>

Getting a database on one server to match another

Sorry wasn't too clear was I.
The second server is our disaster recovery server, so we
want our second server database to be as up to date as
possible with the production database.
ta

>--Original Message--
>Hello,
>We have two servers and want out production db on server
1
>to match the production server on server 2.
>Whats the best way of doing this?
>.
>
Sarah,
Hari already answered this with:
1) Log Shipping
2) Transactional replication
If the disaster recovery server does not need to be operational, then log
shipping would be good.You may also want to consider Double-Take from NSI
Software. This replicates the SQL Server data files at the block level on
disk and ensures transactional consistency.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...[vbcol=seagreen]
> Sorry wasn't too clear was I.
> The second server is our disaster recovery server, so we
> want our second server database to be as up to date as
> possible with the production database.
> ta
>
> 1
|||Thanks Mark,
I'll have a look at transactional replication as we backup
the Production db by log files and to be honest I'm not
too sure on what will happen if we change it so it updates
asap.
Thanks for your time

>--Original Message--
>Sarah,
>Hari already answered this with:
>1) Log Shipping
>2) Transactional replication
>If the disaster recovery server does not need to be
operational, then log
>shipping would be good.You may also want to consider
Double-Take from NSI
>Software. This replicates the SQL Server data files at
the block level on
>disk and ensures transactional consistency.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>
>
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
server
>
>.
>
|||There's a web site at MS called something like "Pattern and practices". There you find a two boos on SQL
Server and high availability. You really need to go through that if you intend to use replication for fail
over. There are some issues you need to plan for.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4ae801c42c41$337182f0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> operational, then log
> Double-Take from NSI
> the block level on
> message
> server
|||Sarah,
I really don't think transactional replication is a good high availability
solution. There are many drawbacks to using this. Do a search on the web for
high availability sql server and you should find some good information.
Kimberley Tripp did a great presentation on high availability, perhaps
search for that.
Log shipping might be a good way to go, you can have a latency of 5 minutes
with this solution.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4ae801c42c41$337182f0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> operational, then log
> Double-Take from NSI
> the block level on
> message
> server
|||Thanks for your help guys

>--Original Message--
>Thanks Mark,
>I'll have a look at transactional replication as we
backup
>the Production db by log files and to be honest I'm not
>too sure on what will happen if we change it so it
updates[vbcol=seagreen]
>asap.
>Thanks for your time
>
>
>operational, then log
>Double-Take from NSI
>the block level on
>message
we
>server
>.
>
sql

Getting a 404/401 on a long running report

Greetings.
I have a report that I am running through ASP.Net with the rs.render method.
When I have a longer date range, the execution time of the query gets longer
and I start to get funky errors. Namely, at around 3 minutes, I get
prompted for network credentials by the standard IE prompt. It doesn't
accept what they are, and after a few times, it takes me to a 404 saying that
the page can't be found. If I try to cancel the prompt, I get a 401 that I
don't have access. I'm not sure what else to look at here. This report has
worked fine in the past, and there are no problems if I just scale back the
date range.
I have the render make a pdf file in a virtual directory on my web server
and then my postback page has a client script inserted to open a window
showing that pdf file.
Anyone have any ideas?
Dale.I fixed it.
It turns out that the HTTP connection timeout settings needed to be
increased a little bit to allow the report to complete. The default of 120
seconds had worked fine in IIS 5 on Windows 2000 Server, but the same value
in IIS 6 on Windows 2003 Server didn't fare too well.
Also, I wound up re-writing the stored proc for the report. The original
author of it had cursors and correlated subqueries doing things they were
never intended to do. Execution time is now 33s instead of 10m15s.
:-)
"Dale M." wrote:
> Greetings.
> I have a report that I am running through ASP.Net with the rs.render method.
> When I have a longer date range, the execution time of the query gets longer
> and I start to get funky errors. Namely, at around 3 minutes, I get
> prompted for network credentials by the standard IE prompt. It doesn't
> accept what they are, and after a few times, it takes me to a 404 saying that
> the page can't be found. If I try to cancel the prompt, I get a 401 that I
> don't have access. I'm not sure what else to look at here. This report has
> worked fine in the past, and there are no problems if I just scale back the
> date range.
> I have the render make a pdf file in a virtual directory on my web server
> and then my postback page has a client script inserted to open a window
> showing that pdf file.
> Anyone have any ideas?
> Dale.

GetReportParameters method not updating ReportParameters array.

I have developed a report that uses two 'datatime' parameters ('start date'
and 'end date'). I then introduced a third parameter ('Range') which
provides a difinitive list of integer values to select from.
On selecting one of the integer values from the 'Range' parameter, the
'start date' and 'end date' are updated accordingly (by applying
"=Code.GetStartDate(Parameters!Range.Value)" as the non-queried Default
Values of the 'start date' parameter properties, and similar for the 'end
date'). This works perfectly when run from the Report Manager (i.e. If I
select the third option (last month) from the 'Range' parameter, the code
will update the 'start date' value to the beginning of last month and the
'end date' value to the last day of last month).
I have also developed a C# user control that acts as a client side utility
for viewing reports on a specific server (using the Reporting Services
WebService provided). This applicatiuon works perfectly for all reports
with the exception of that described above. The problem lies in obtaining
the updated dates when selecting a 'Range' using the 'GetReportParameters'
method. I have no problem returning updated parameter values from Query
Dependant parameters, which causes me to believe that I am calling the
Method correctly, however the 'start date' and 'end date' default values do
not seem to be updated by the webservice despite passing the updated value
of the 'Range' parameter to the method.
Any information as to why the parameter values are not being updated
correctly would be helpful.
Below is a code snippet:
Thanks in advance.
----
ReportServer.ReportParameter[] parameters;
try
{
parameters = rs.GetReportParameters("/" +
reportPath,null,true,currentParamValues,null);
}
catch(System.Web.Services.Protocols.SoapException sex)
{
SoapException(sex);
return null;
}
----I am seeing something similar, only with a cascading parameter. I've
stripped it down to the simplest example I can think of... the report has 2
parameters, A and B. B has a valid values list based on a dataset that
requires A.
I call GetReportParameters( ReportName, false, null, null, null ) and both
parameters come back with parameter 2 dependent upon parameter 1. I then
provide a ParameterValue array with 1 entry for parameter A. I call
GetReportParameters( ReportName, true, null, parameterValues, null ) and I
get both parameters back but B has no valid values and the parameter's state
is HasOutstandingDependencies.
Have you determined what your parameter's state is after the
GetReportParameters call?
"Paul B" wrote:
> I have developed a report that uses two 'datatime' parameters ('start date'
> and 'end date'). I then introduced a third parameter ('Range') which
> provides a difinitive list of integer values to select from.
> On selecting one of the integer values from the 'Range' parameter, the
> 'start date' and 'end date' are updated accordingly (by applying
> "=Code.GetStartDate(Parameters!Range.Value)" as the non-queried Default
> Values of the 'start date' parameter properties, and similar for the 'end
> date'). This works perfectly when run from the Report Manager (i.e. If I
> select the third option (last month) from the 'Range' parameter, the code
> will update the 'start date' value to the beginning of last month and the
> 'end date' value to the last day of last month).
> I have also developed a C# user control that acts as a client side utility
> for viewing reports on a specific server (using the Reporting Services
> WebService provided). This applicatiuon works perfectly for all reports
> with the exception of that described above. The problem lies in obtaining
> the updated dates when selecting a 'Range' using the 'GetReportParameters'
> method. I have no problem returning updated parameter values from Query
> Dependant parameters, which causes me to believe that I am calling the
> Method correctly, however the 'start date' and 'end date' default values do
> not seem to be updated by the webservice despite passing the updated value
> of the 'Range' parameter to the method.
> Any information as to why the parameter values are not being updated
> correctly would be helpful.
> Below is a code snippet:
> Thanks in advance.
> ----
> ReportServer.ReportParameter[] parameters;
> try
> {
> parameters = rs.GetReportParameters("/" +
> reportPath,null,true,currentParamValues,null);
> }
> catch(System.Web.Services.Protocols.SoapException sex)
> {
> SoapException(sex);
> return null;
> }
> ----
>
>sql

geting the UNIQUENAME fom a string

Does anyone have an idea how to get the UNIQUENAME of a member from ther dimesion date given a determined value.

For example in Adventure Works

Val: 2004

UNIQUENAME: [Date].[Calendar].[Calendar Year].&[2004]

Thanks!!

Here is an example showing how to retrieve the unique member name or the key value associated with the current member:

WITH

MEMBER MEASURES.[UniqueName] AS

[Date].Calendar.CurrentMember.UniqueName

MEMBER MEASURES.[KeyValue] AS

[Date].Calendar.CurrentMember.Properties("Key")

SELECT

{[Date].[Calendar].[Calendar Year].&[2004]} ON COLUMNS,

{MEASURES.[UniqueName],MEASURES.[KeyValue]} ON ROWS

FROM [Adventure Works]

HTH,

- Steve

Getdate()??

I declared a @.Start and @.End, and now I want to bring in todays date @.End and 30 days pevious as start... These two commands give me those 2 pieces of data but how do I use(assign) Start and End to these??

SELECT CONVERT(CHAR(10), GETDATE(), 101)
SELECT CONVERT(CHAR(10), GETDATE()-30, 101)DECLARE @.Start char(10), @.End char (10)

SELECT @.Start = CONVERT(CHAR(10), GETDATE(), 101)
SELECT @.End = CONVERT(CHAR(10), GETDATE()-30, 101)

SELECT @.Start, @.End|||how about:
DECLARE @.Start char(10), @.End char (10)
select @.start=getdate()
select @.end = dateadd(m,-1,getdate())
SELECT @.Start as 'start', @.End as 'end'

getdate() within a function

Im really new to SQL SProcs. I have a function that I wrote that I am trying to compare a date within a record to today's date. The problem is that you cant call getdate from within a function... So, I was thinking that I could create a temp table that had a a date column with a default date of today and select that out. However, I cant find any documentation on how you would create a temp table with a default value, or if this would even work. I dont want to have to pass todays date into the function, nor do I want to have to create a permanent table just to hold this data.

Any help, or other ideas?

Thanks.

You would create a temp table with a default value the same way you would create a non-temp table with a default value, except start the table name with a #.

Why can't you select the date from within the function?

I can't help but feel that you are doing something more basic incorrectly, or with a bad approach to your problem, but without any code, it's hard to tell what you are trying to do.

|||

Originally, I was trying to do something like this:

SET @.resDate = (SELECT ...)

IF @.resDate > getdate()
...

That didnt work because you cant call getdate from a function. Then I tried:

CREATE TABLE #tempDate
(
today datetime
DEFAULT(getdate())
)

Which also did not work for the same reason. So... other than passing getdate into the function, is there any other way to do it?

Josh

|||

You could also write a view that returns a datetime.

This is no more an issue in SQL 2005 though. So if you are using 2005, you are lucky.

|||what about this function(time1,getdate()). This is what I have ahad to use in the past.

GETDATE() with a user defined function

Hi,
I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.
Regards,
Samcute.Nope sorry...

Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?

Anyone know how to create an external sproc?

USE Northwind
GO

CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO

DECLARE @.myDate99 datetime

EXEC mySproc99 @.myDate99 OUTPUT

SELECT @.myDate99

CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO

SELECT dbo.udf_myFunction99(0)
GO

DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO|||Nope sorry...

Why not just use GetDate() instead of a Function? Or pass it in as a parameter to a function?

Anyone know how to create an external sproc?

USE Northwind
GO

CREATE PROC mySproc99 @.myDate99 datetime OUTPUT AS SELECT @.myDate99 = GetDate()
GO

DECLARE @.myDate99 datetime

EXEC mySproc99 @.myDate99 OUTPUT

SELECT @.myDate99

CREATE FUNCTION udf_myFunction99(@.x datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.myDate99 datetime
EXEC mySproc99 @.myDate99 OUTPUT
RETURN @.myDate99
END
GO

SELECT dbo.udf_myFunction99(0)
GO

DROP FUNCTION udf_myFunction99
DROP PROC mySproc99
GO

Pass GetDate() as a function argument.

Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

-PatP|||Pass GetDate() as a function argument.
-PatP

Did I already say that?

Yes, I can write extended stored procedures. No, it isn't worth it for the average user, since it is a lot of work and you can break nearly all of the rules in an xp. If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

oooo scary...

Did you see Hendersons work for Arrays in SQL Server?

And you're right...I chickened out...was going to build them...but I figured why bother...a tables an array, and with the table variable is was even easier...|||If you don't know how/why you're breaking the rules, that can be a REALLY bad thing!

Yeah...I called that "midlife crisis", and a few thousands of dollars and several handcuff burns later...I tend to agree ;) Although..."that which does not kill us..." ;)|||Thousands of dollars ?!?! Was she worth it?

-PatP|||*LOL* Nope...not even a "she" dammit (though, it's probably best to assume the "she" connection first in any such situations)...what a wasted midlife crisis...just partyin' too much with m'homeboys Jose' C and Jack D|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...|||Picture I'm getting includes 4 wheels...a tree...flashing lights and MASSIVE amounts of alcohol...No, no, no! He didn't say it was a normal Tuesday afternoon. This was something special!

I figured with thousands of dollars and handcuff burns, there just HAD to be a "she" in there somewhere!

You do have to be wary when playing with Jack and Jose. Those fellas play kinda rough sometimes. Glad to know that you survived it anyway!

-PatP|||Thanks...and no, no trees or blood involved, but Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it ;)

Perhaps needless to say, I lost quite a few brain cells that would come in handy now trying to figure out how to debug in SQL Server, and how to put non-deterministic functions into user-defined functions!

(hey, how's THAT for coming back on-topic ;) )|||Gov. Davis was nice enough to send me on a 14-month vacation clearing brush and fighting fires for the state over it

Did you get a room with the view of the lake?

This is good Yak Corral stuff...

And Pat do you use debugger or not?|||On my own code, I've only used the debugger once or twice in order to show other people how my code worked. I've never needed it for actually debugging code that I've written.

The debugger has come in handy more than once trying to finger out what in blazes some of the code that I've inherited actually does. Some of that stuff can be most charitably described as bizarre.

-PatP

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!sql

getdate() not returning a value

I have a strange problem occurring when I am using getdate() in a stored proc. I want to get some data from a table within a date range using getdate.
I have a begin and end dates on a table and want to retrieve a guid and some other information based on the current date. So, wherever today's date falls between the begin date and the end date, I want the information from that row.

For example,

select * from polldates
where (pollbegindate >= getdate() and pollenddate <= getdate())

This works fine Monday through Saturday. I get a value returned from getdate() correctly and am able to retrieve the information that I need. However, on Sunday, getdate returns nothing when I run the stored procedure. Any clues? Am I just crazy or has anyone else seen this type of thing happen?

Any help would be greatly appreciated!I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

-PatP|||If you are sure that get date is returning a correct value but I am not getting anything back from my query can you suggest how to improve the query?

For example, the begin date is 9/5/04 and the end date is 9/11/04.

Thanks!|||Is it safe to assume pollbegindate and pollenddate are datetime datatypes in the table? Please post the enitre proc. There may be another problem.|||I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

-PatP

Well that was CERTAINLY helpful...

Dude

Do SELECT GetDate()...what do you see?

Ahh microseconds...

USE DATEDIFF

But the logic doesn't make sense...

You want all begin dates that are today and greater but all end dates that are less that or equal today...which means...

And day where the start and end are equal and it's TODAY

Johhny...tell him what he's won.....|||Maybe we all need to read. Now I feel like an idiot (well, I almost always feel like an idiot, but that's another matter).SELECT *
FROM polldates
WHERE pollbegindate <= getdate()
AND pollenddate >= getdate()The previous code was looking for rows where the begindate was greater than the enddate!

-PatP|||Even with the screwed up logic why does it return records everyday but Sunday?|||Me no know.

Without seeing the real query and the underlying data, I can offer a gazillion guesses, but no hard facts.

-PatP

GETDATE() in a Function

Hi again.
I'm trying to write a user-defined function that accepts only one parameter, a date.
The function then calculates the amount of days elapsed between the specified date,
and the current system-date. I'm using DATEDIFF together with GETDATE() to try and
calculate the difference, but GETDATE() keeps on causing an error.
Can one use GETDATE() in a function?
I tried to call a stored procedure from the same function, and an error states that
only extended stored procedures or functions can be called from within the function...
Any way to bypass this?
Hi,
No, You cannot use getDate() inside a function. Non deterministic values can
not be used inside a function.(Te value og getdate changes every milli
second)
Solution1 :
The solution is Create view as select getDate() as currdate
and then use the view inside the function.
Thanks
Hari
MCDBA
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?
|||Another option is to add a datetime parameter to your
function and pass GetDate() as the value for the parameter
when calling the function.
-Sue
On Wed, 12 May 2004 02:21:04 -0700, "Rival"
<anonymous@.discussions.microsoft.com> wrote:

>Hi again.
>I'm trying to write a user-defined function that accepts only one parameter, a date.
>The function then calculates the amount of days elapsed between the specified date,
>and the current system-date. I'm using DATEDIFF together with GETDATE() to try and
>calculate the difference, but GETDATE() keeps on causing an error.
>Can one use GETDATE() in a function?
>I tried to call a stored procedure from the same function, and an error states that
>only extended stored procedures or functions can be called from within the function...
>Any way to bypass this?
|||Rival
Here is another approach
CREATE FUNCTION dbo.My_Fn(@.dt AS DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
GO
SELECT dbo.My_Fn (GETDATE())
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?

GETDATE() in a Function

Hi again
I'm trying to write a user-defined function that accepts only one parameter, a date.
The function then calculates the amount of days elapsed between the specified date,
and the current system-date. I'm using DATEDIFF together with GETDATE() to try an
calculate the difference, but GETDATE() keeps on causing an error
Can one use GETDATE() in a function
I tried to call a stored procedure from the same function, and an error states that
only extended stored procedures or functions can be called from within the function..
Any way to bypass this?Hi,
No, You cannot use getDate() inside a function. Non deterministic values can
not be used inside a function.(Te value og getdate changes every milli
second)
Solution1 :
The solution is Create view as select getDate() as currdate
and then use the view inside the function.
Thanks
Hari
MCDBA
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?|||Another option is to add a datetime parameter to your
function and pass GetDate() as the value for the parameter
when calling the function.
-Sue
On Wed, 12 May 2004 02:21:04 -0700, "Rival"
<anonymous@.discussions.microsoft.com> wrote:
>Hi again.
>I'm trying to write a user-defined function that accepts only one parameter, a date.
>The function then calculates the amount of days elapsed between the specified date,
>and the current system-date. I'm using DATEDIFF together with GETDATE() to try and
>calculate the difference, but GETDATE() keeps on causing an error.
>Can one use GETDATE() in a function?
>I tried to call a stored procedure from the same function, and an error states that
>only extended stored procedures or functions can be called from within the function...
>Any way to bypass this?|||Rival
Here is another approach
CREATE FUNCTION dbo.My_Fn(@.dt AS DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
GO
SELECT dbo.My_Fn (GETDATE())
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?

GETDATE() in a Function

Hi again.
I'm trying to write a user-defined function that accepts only one parameter,
a date.
The function then calculates the amount of days elapsed between the specifie
d date,
and the current system-date. I'm using DATEDIFF together with GETDATE() to t
ry and
calculate the difference, but GETDATE() keeps on causing an error.
Can one use GETDATE() in a function?
I tried to call a stored procedure from the same function, and an error stat
es that
only extended stored procedures or functions can be called from within the f
unction...
Any way to bypass this?Hi,
No, You cannot use getDate() inside a function. Non deterministic values can
not be used inside a function.(Te value og getdate changes every milli
second)
Solution1 :
The solution is Create view as select getDate() as currdate
and then use the view inside the function.
Thanks
Hari
MCDBA
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?|||Another option is to add a datetime parameter to your
function and pass GetDate() as the value for the parameter
when calling the function.
-Sue
On Wed, 12 May 2004 02:21:04 -0700, "Rival"
<anonymous@.discussions.microsoft.com> wrote:

>Hi again.
>I'm trying to write a user-defined function that accepts only one parameter
, a date.
>The function then calculates the amount of days elapsed between the specifi
ed date,
>and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
>calculate the difference, but GETDATE() keeps on causing an error.
>Can one use GETDATE() in a function?
>I tried to call a stored procedure from the same function, and an error sta
tes that
>only extended stored procedures or functions can be called from within the
function...
>Any way to bypass this?|||Rival
Here is another approach
CREATE FUNCTION dbo.My_Fn(@.dt AS DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
GO
SELECT dbo.My_Fn (GETDATE())
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?

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
mark"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|||Mark,
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...
> 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
>|||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|||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 Smeyer" <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 Smeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> now
> like
> where
> further
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>

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
mark
"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
|||Mark,
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...
> 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
>
|||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
|||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 Smeyer" <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 Smeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
> news:9DC85C5C-2C28-46A1-B51A-D6176AB0C7B8@.microsoft.com...
> now
> like
> where
> further
> so you would recommend passing the date from an app to the stored
> procedure
> instead ?
> (might be easier)
>
> cheers
> mark
>
>

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