Problem with GETDATE() function.

Hi everyone,

DECLARE @.altstartdate datetime
SET @.altstartdate = getdate()
set @.altstartdate = @.altstartdate -1
select * from tbl_document where convert(char,dicreationdttm,101) = convert(char,@.altstartdate,101)

The person who wrote the code was trying to get the previous day of data, So today is 03/07/06, within the database it only have data from 03/06/06 and back 15 days, so what I want is data from 03/06 - -2/20, which I believe is 15 days worth of data, How would I go about geting 15 days of data, do I still need to use getdate() function. Any help please. Thanks

LystraHELP!!! Please|||Dump your variables and use this instead:select * from tbl_document where convert(char,dicreationdttm,101) = convert(char,dateadd(day, -1, getdate()),101)...or this, which is more efficient:select * from tbl_document where dateadd(day, datediff(day, 0, dicreationdttm, 0) = dateadd(day, datediff(day, 0, getdate())-1, 0)

Problem with GETDATE()

Hello All,

I have a problem as follows

if i execute SELECT GETDATE() statement multiple times in a single run it returns me the same datetime without any difference in even milliseconds.

I am unable to figure out what is wrong. I am assuming that whenever executed in a transaction it will give the same result.

could anybody let me know what is correct. Thanks for your help in advance.

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

even then i get the same date.

What are you trying to achieve? The amount of time it takes to run multiple Select GetDate() is very minor. We would be able to help you better if we knew what your goal was.

|||

Hi, mate

I just executed:

SELECTGETDATE()SELECT *FROM Table1SELECTGETDATE()

and the the two dates was different. (Table1 has 120 000 rows)

This means that the query is executing too fast (in less than a millisecond) and that is why you receive the same results.

|||

yeah... if u execute query select getdate() several times one after another u cant understand the difference of milliseconds. don't worry...

|||

Hi Diamsorn,

Thanks for the reply. but all i am trying to do was i have a history table and i have included modified date as a part of primary key and when i am trying to update my main table i am inserting a record into history table. eventhough i am doing it in different time system says it is a violation of primary key.

For eg. Table1 is having below columns

Column1 Column2 Column3 and Suppose Primary key is composite key of column1 and column2

I have HistoryTable having columns

Column1 Column2 modifieddate and Suppose Primary key is composite key of Column1,Column2 and Modifieddate. but when i am trying to update the table1, and though trigger i am capturing getdate() to fill modifieddate, then as it is not different it is giving error.

how to overcome this problem?

Gneralproblem

|||

Which table is giving the primary key violation error? Table1 or HistoryTable.

What is your purpose of having a composite primary key in your history table of column1, column2, and modified date?

I would move away from using a trigger to insert into your history table, and do your update/insert inside of a transaction in a stored procedure. Triggers are a maintenance nightmare and I avoid them personally at all costs.

|||

Hi Diamsorn,

History table is giving me error. As i have to update the same record in Table1 and track the changes in HistoryTable. As my operation is so fast and as it is caputring same date it is giving primary key violation.

I would appreciate if any way to handle this problem using Triggers.

Thanks,

GeneralProblem

Problem with GETDATE in SQL Stored Procedure

hi all,

i am using a stored procedure where i am using GETDATE to give default value to a field ( @.effectivedate as Datetime = GETDATE)

i am making the SP call in my code .

Dim cmd As System.Data.Common.DbCommand = db.GetStoredProcCommand("sel_TemplateData")

db.AddInParameter(cmd, "@.TemplateID", DbType.Int32, Convert.ToInt32(_templateId))

db.AddInParameter(cmd, "@.State", DbType.String, mrmParams("State").ToString())

db.AddInParameter(cmd, "@.SectionCode", DbType.String, mrmParams("SectionCode").ToString())

Dim ds As DataSet = db.ExecuteDataSet(cmd)

should i need to pass this as a parameter along with other parameter as below ? will it be defaultly taken.

when i try to add this parameter an error is thrown " cannot convert string to datetime .

is the syntax for GETDATE correct.

thanks in Advance

Since Getdate() is the non-deterministic function you can’t assign this function as your default value of the SP parameter.

Try to use the following approach to pick up the current date when there is no explicit value passed for datetime valued parameters.

C

Code Snippet

raete Procedure TestDateParam

(

@.Date as datetime= '1900-01-01'

)

as

Begin

Set @.Date = Case When @.Date = '1900-01-01' Then Getdate() Else @.Date End;

Select @.Date Date

End

Go

Exec TestDateParam --it will use the current date

Exec TestDateParam '1/1/2007' --it will use the passed date value

|||

Could you instead use NULL as your default value? If that is possible then you would not need the set statement but could use the ISNULL or COALESCE function -- something like:

ISNULL(@.Date, getdate())

or

COALESCE(@.Date, getdate())

within the body of your stored procedure

|||

If you are wanting to make @.EffectiveDate an optional parameter for the procedure, I suggest using Kent's suggestion of setting the optional value equal to NULL. It is cleaner than presuming a redefined date means none supplied...

|||In addition to Kent and Arnie you could consider using the syntax:

Create procedure someproc
(
@.SomeDate DATETIME = NULL
)
AS

SELECT
(...Something)
WHERE YourColumn = @.SomeDate OR @.SomeDate IS NULL

But this highly depends on your needs in the logic of the stored procedure.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem with getdate function in optional parameters

Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used.

I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works.

ALTER PROCEDURE

[dbo].[LinksFees_Record]

@.Date

datetime=getdate

AS

INSERT INTOLinkSearchFees

(LinkID, Price, [Date])

SELECTIDASLinkID, SearchDayFeeASPrice, @.DateFROMLinksWHERE(SearchDayFee > 0)

RETURNWhen I call the StoredProcedure the following exception occur:Conversion failed when converting datetime from character string.

How can I fix it?

Hi!

Try this:

ALTER PROCEDURE

[dbo].[LinksFees_Record]
@.Date

datetime = NULL
AS

IF @.DATE IS NULL SET @.Date= getdate()
... rest of your procedure goes here ...

Now if the users passes no parameter then @.Date will be replaced by getdate() result. Beware that if the user passes a NULL it will also be replaced by GetDate() results.

Have a good day,

David

problem with DEFAULT (getdate())

Hi all

I create table and set default value detdate(). But after insert record date display ‘1900-01-01 00:00:00’.

Example this,

CREATE TABLE [tblTemp1] (

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 values(0)

select ItemUserDate from dbo.tblTemp1 =‘1900-01-01 00:00:00’

select getdate() =’2006-07-20 15:53:27.820’ I need this answer

Hi,

try this:

CREATE TABLE [tblTemp1] (

part1 char,

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 part1, values('a')

And then do your select. You'll recieve the correct answer.

You have you result because you're putting 0 in your default column, so default has no effect anymore...

Greeting.

|||

Hi

To make Insert work for the table structure with a single datetime column you can use Default VALUES option: INSERT INTO dbo.tblTemp1 DEFAULT VALUES

|||

thankx Stefan Haeck

|||thanks NB2006

Problem with DateAdd & GetDate()

Hi,
I've been struggling trying to figure out this code to no avail. I need to
query a date time field on an external database to give me data if the
Closed_Time has changed within the last day. The Closed_Time field looks
like this 9/1/2005 11:59:00 AM. The code I've been attempting to use is
(Closed_Time > DATEADD(day, - 7, GETDATE()) and when that is executed a
Lexical Element error message is returned. I've searched on the boards for
other variations of this code and none have worked so far.
I'm hoping someone can shed a little light on it for me.
Thanks.What error message?
Also look up DATEDIFF in Books Online, if you haven't already.
ML
http://milambda.blogspot.com/|||Whats the exact error message ?
HTH, Jens Suessmeyer.|||ML wrote:
> What error message?
> Also look up DATEDIFF in Books Online, if you haven't already.
>
Why would DATEDIFF be relevant?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> I've been struggling trying to figure out this code to no avail. I need
> to
> query a date time field on an external database to give me data if the
> Closed_Time has changed within the last day. The Closed_Time field looks
> like this 9/1/2005 11:59:00 AM. The code I've been attempting to use is
> (Closed_Time > DATEADD(day, - 7, GETDATE()) and when that is executed a
> Lexical Element error message is returned. I've searched on the boards
> for
> other variations of this code and none have worked so far.
Assume the current date/time is 20060126 11:07:13.003. What does the
evaluation of the expression "DATEADD(day, - 7, GETDATE()" yield? It is
likely not the value you desire. However, your description doesn't match
this query - you said "... has changed within the last day" yet your
expression subtracts 7 days from the current date/time.
At a minimum, the following should help you understand datetime values and
how they should be used.
http://www.karaszi.com/sqlserver/info_datetime.asp
And as everyone else suggested, post the error message - exactly as it
appears. Perhaps it would help to define/identify what "external database"
means - I've never seen sql server return an error message with the text
"lexical element error" in any form.|||Jason wrote:
> Hi,
> I've been struggling trying to figure out this code to no avail. I
> need to query a date time field on an external database to give me
Using OPENQUERY?

> data if the Closed_Time has changed within the last day. The
> Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code
> I've been attempting to use is (Closed_Time > DATEADD(day, - 7,
> GETDATE()) and when that is executed a Lexical Element error message
> is returned. I've searched on the boards for other variations of
> this code and none have worked so far.
> I'm hoping someone can shed a little light on it for me.
>
I suggest asking for help in a group devoted to the type of external
database you are using. It sounds as if that rdbms requires a different
syntax for the DATEADD function than that required by Transact-SQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The exact error message is
ADO Error Driver) Expected lexical element not found :)
I was informed that it could be an ODBC issue. I get data from a company
and I use their own ODBC connection to pull it. We're under the assumption
that we are limited becasue of the connection. With that in mind I just
attempted this Closed_Time > DATEADD(day, -1, 1/26/2006 10:02:25 AM)) and th
e
same error was given. If I'm limited by the ODBC connection, what other way
can I get records that were just updated in the last day.
Thanks for all the help, I appreciate it.
Jason
"Bob Barrows [MVP]" wrote:

> Jason wrote:
> Using OPENQUERY?
>
> I suggest asking for help in a group devoted to the type of external
> database you are using. It sounds as if that rdbms requires a different
> syntax for the DATEADD function than that required by Transact-SQL.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||The query is taking place via a SQL task in a DTS package.
"Bob Barrows [MVP]" wrote:

> Jason wrote:
> Using OPENQUERY?
>
> I suggest asking for help in a group devoted to the type of external
> database you are using. It sounds as if that rdbms requires a different
> syntax for the DATEADD function than that required by Transact-SQL.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||"ODBC" tells us nothing. Is it an Oracle database? Foxpro? MySQL? DB2? (I've
never seen a Jet error message containing the word "Lexical" so I think
Access can be ruled out)
You have to use syntax that works in the external database. We cannot help
you with that without knowing the type of database involved. And even then,
the chances of finding somebody who is knowledgeable about that database in
a SQL Server group are not great.
Could you ask a developer in that "company" to write a query that does what
is needed and send it to you?
Bob Barrows
Jason wrote:
> The exact error message is
> ADO Error Driver) Expected lexical element not found :)
> I was informed that it could be an ODBC issue. I get data from a
> company and I use their own ODBC connection to pull it. We're under
> the assumption that we are limited becasue of the connection. With
> that in mind I just attempted this Closed_Time > DATEADD(day, -1,
> 1/26/2006 10:02:25 AM)) and the same error was given. If I'm limited
> by the ODBC connection, what other way can I get records that were
> just updated in the last day.
> Thanks for all the help, I appreciate it.
> Jason
> "Bob Barrows [MVP]" wrote:
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Ok it looks like in order to query that database I need to use this syntax.
(Closed_Time > { ts '2006-01-25 00:00:00' })
Is there then a way for me to store the current date in this format in
another table perhaps and then pass it along to this query or would that fai
l
on me as well? I'm just looking to automate it as much as possible.
Thanks.
"Bob Barrows [MVP]" wrote:

> "ODBC" tells us nothing. Is it an Oracle database? Foxpro? MySQL? DB2? (I'
ve
> never seen a Jet error message containing the word "Lexical" so I think
> Access can be ruled out)
> You have to use syntax that works in the external database. We cannot help
> you with that without knowing the type of database involved. And even then
,
> the chances of finding somebody who is knowledgeable about that database i
n
> a SQL Server group are not great.
> Could you ask a developer in that "company" to write a query that does wha
t
> is needed and send it to you?
> Bob Barrows
> Jason wrote:
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Problem with Date Function ?

I insert date into table using getdate() function and that works fine.

Now from my query when I try to compare with it and returns nothing.

Thats how the date is stored in the table : 6/4/2007 1:46:57 PM

Now If I do

SELECT * FROM Sometable

WHERE date = getdate() ///Nothng returned

SELECT * FROM sometable

WHERE date = '6-4-2007' // Nothing is returned

How to Fix this issue ?

This has to do with the way that datetime data is stored. Try this:

Code Snippet

select * from someTable

where date >= convert(datetime(convert(varchar(10), getdate(), 101)))

and date < convert(datetime(convert(varchar(10), getdate()+1, 101)))

or

Code Snippet

select * from someTable

where date >= '6-4-2007'

and date < convert(datetime, '6-4-2007')+1

The problem with using the BETWEEN operator is that it includes events that occur exactly on midnight which is technically part of the next day. There are a number of other ways of doing this in addition to these two.

Something that you ought to avoid is loading either GETDATE() or your date constant '6-4-2007' into a variable. Loading this data into a variable eliminates some optimization options that might be exploited by the SQL optimizer.

|||

Hello Kent,

That will return me all dates greater than that specific date. I want the exact date, is there any way to change how the date is stored ? As i'm creating my SQL programatically, it is hard to have everything done at that level.

|||

Sorry my bad, I made a mistake in dates. That works !!!

Is there any way to fix this at Database Level....

|||

Harsimrat,

Datatime data type can include also time part, so if you need to select rows you will have to specify the time part you want.

select *

from sometable

where date >= '20070604' and date < '20070605'

-- or

select *

from sometable

where date between '2007-06-04T10:00:00' and '2007-06-04T18:59:59.997'

If you used function getdate() to insert the rows, then remember that this function returns current date and time and it will not be useful to match rows inserted in the past unless both operations are executed in the same time.

-- this could work

declare @.t table (c1 datetime)

insert into @.t values(getdate())

select * from @.t where c1 = getdate()

-- but not this

declare @.t table (c1 datetime)

insert into @.t values(getdate())

waitfor delay '00:00:00.003'

select * from @.t where c1 = getdate()

AMB

|||

Adding to Kent's explanition, when you store a datetime value as you have, then you will not get a match unless the criteria has the EXACT same datetime value -up to the milliseconds.

So to find all values for a particular date, you need to look for values since midnight, and up to the next midnight. Kent provided one set of criteria that accomplishes that goal, here is another.

--For today

Code Snippet

WHERE ( [Date] >= dateadd( day, datediff( day, 0, getdate() ), 0 )
AND [Date] < dateadd( day, datediff( day, 0, getdate() + 1 ), 0)

)

p.s., You really shouldn't name your columns and tables with 'RESERVED WORDS'. [Date] is a reserved word that has special meaning in SQL Server. Refer to Books Online, Topic: 'Reserved Words'

|||

When I run this query:

Code Snippet

declare @.someTable table
( date datetime
)
insert into @.someTable
select '6/3/7 15:00' union all
select '6/3/7 23:59:59.997' union all
select '6/4/7' union all
select '6/4/7 8:00' union all
select '6/4/7 23:59:59.997' union all
select '6/5/7'
--select * from @.someTable

select * from @.someTable
where date >= convert(datetime, (convert(varchar(10), getdate(), 101)))
and date < convert(datetime, (convert(varchar(10), getdate()+1, 101)))

I get this result:

Code Snippet

date
--
2007-06-04 00:00:00.000
2007-06-04 08:00:00.000
2007-06-04 23:59:59.997

Is that result incorrect?