We help IT Professionals succeed at work.

getdate sql

zolf
zolf used Ask the Experts™
on

hello there,

i am facing an issue,when i say updatedDate=getdate,it does not return anything even though i have a updated record today. what is wrong, does it take into consideration the time also.how can i only get the date and excude the time factor.

cheers
zolf
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you have to add parentheses..

updatedDate=getDate()

Author

Commented:

i tried something like this

SELECT * FROM table WHERE updatedOn = getDate()

but it does not return anything,but infact I have a row which was updated today. my updatedOn col is type datetime. i mean i have records like this 20011-08-07 09:12:22:

Commented:
the function getdate()  is used with paranthesis as the other functions in sql...

you have to use date(getdate())
Commented:
himm...
based on your query no result set is normal.

if you want to return al the updates done in the current date you sould use something like this which truncates the time portion of the returnin value

SELECT * FROM table WHERE updatedOn >=  convert(datetime, getDate(), 104)

Author

Commented:

i am using it with parenthesis, see my query i have written.infact if i dont put the parenthesis,i would get an error when i run the query
Kent DyerIT Security Analyst Senior
Commented:
This may help you..

Dates in SQL Server

HTH,

Kent
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
GETDATE() return current date and time. Means the engine gonna search for records with same date and time (till miliseconds) in that column. You need to convert it to correct format or if you want check next code that returns all records updated in last 24h:
SELECT * FROM table WHERE updatedOn BETWEEN getDate()-1 AND getDate()

Open in new window

Author

Commented:

tigin44:

i tried your query,but still no luck.do i have to make any changes in the convert also can you please give me a brief description as to what we are doing
SELECT * FROM batch WHERE lastupdated >=  convert(datetime, getDate(), 104)

Author

Commented:

VMontalvao:

your query worked.thanks a lot. can you please explain in bried as to what the query is doing.appreciate your help

Author

Commented:

VMontalvao:
>>code that returns all records updated in last 24h:

you mean today.correct??

Author

Commented:

kdyer:

that link was helpful
G_H

Commented:
Hi There,

I think VMontalvao is heading you in the right direction, but to full answer the question, we need to know exactly what you are trying to achieve. It maybe:

A. Any entries in the last 24 hours. (Example 2011-08-06 16:00:00 to 2011-08-07 16:00:00)
B Any entries in the current day. (Example 2011-08-07 00:00:00 to 2011-08-07 16:00:00)

GH


Author

Commented:

G_H:

thanks for your comments.i need the answer to your comment B.by the way why to 2011-08-07 16:00:00,shouldnt it be to 2011-08-07 24:00:00 i.e.

2011-08-07 00:00:00 to 2011-08-07 24:00:00

B Any entries in the current day. (Example 2011-08-07 00:00:00 to 2011-08-07 16:00:00)

Author

Commented:

??
G_H

Commented:
OK, I put the question in that way, so that you could answer the question.

Consider if the time is 4 in the afternoon, then there should be no database entries AFTER 16:00... So checking against the CURRENT time for the "end" is fine...

Anyway... Try this...


SELECT Whatever FROM Wherever WHERE DateField BETWEEN CURDATE() AND ADDDATE(CURDATE(), 1)

GH
Commented:
Hi Zolf,
          you can try this as well.
SELECT * FROM table WHERE lastupdated >=  convert(nvarchar(10), getDate(), 113)

Author

Commented:

can you please explain the difference between

SELECT * FROM table WHERE updatedOn BETWEEN getDate()-1 AND getDate()

SELECT Whatever FROM Wherever WHERE DateField BETWEEN CURDATE() AND ADDDATE(CURDATE(), 1)

SELECT * FROM table WHERE lastupdated >=  convert(nvarchar(10), getDate(), 113)


Author

Commented:

i get error when i try this

SELECT * FROM WProduct WHERE created = getDate()-1 AND getDate()
or lastupdated= getDate()-1 AND getDate()

error i get is

An expression of non-boolean type specified in a context where a condition is expected, near 'or'.
G_H
Commented:
Hi Zolf,

If questions remain un-answered after a while, I always re-read the them to see if I have missed something which may not have been obvious at the time... In this case, I note I was alerted to this question because it was posted in "MySQL". I see however that it is also posted in MSSQL. All of the help I have posted is specific to MySQL. If you are using MSSQL, Zolf has the answer I believe, otherwise try my example.

Your question about the difference first...

"Gate Date" version. MySQL does not have a getDate function, to get the date you use "CurrDate" instead.

"CurrDate" Version. This should work (MySQL,), as we are looking for a date between two points - The start of this day, and the start of the next day.

"Convert" version. This is from mimram18, and I guess would work on MSSQL, but will not work on MySQL.

For you second post about the error, I have pasted your code below, with brackets so you can see what is going on. Think of it as each element has to be true or false...

SELECT * FROM WProduct WHERE
            (created = getDate()-1_
 AND
              ( getDate()  )
 or
              (lastupdated= getDate()-1)
 AND
             ( getDate()  )

...As each of the lines after "AND" is (MSSQL) going to return a DATE rather than a boolean True or False, this is where your error comes from.

I hope this helps, but please tell us if you are using MSSQL or MySQL...

GH

Author

Commented:

i am sorry it should have been posted in MSSQL,i made a mistake. i am using MSSQL 2005

Author

Commented:

i want the query to work on MSSQL please

Author

Commented:
i still get error when i try to run that query

Msg 4145, Level 15, State 1, Line 5
An expression of non-boolean type specified in a context where a condition is expected, near 'or'.

SELECT * FROM WProduct WHERE
            (created = getDate()-1)
 AND
              ( getDate()  )
 or
              (lastupdated= getDate()-1)
 AND
             ( getDate()  )
G_H

Commented:
Yes, you will get that error, read the end of my last post...

Have you tried mimran18's idea:

SELECT * FROM table WHERE lastupdated >=  convert(nvarchar(10), getDate(), 113)

GH

Author

Commented:

i get this error when i try mimran18's idea:
SELECT * FROM WProduct WHERE lastupdated >=  convert(nvarchar(10), getDate(), 113)  

Arithmetic overflow error converting expression to data type datetime.
G_H

Commented:
Can you please post the content of the field "lastupdated"

GH
convert(datetime, getDate(), 104)

Hi,
    In Sql we have getdate() function to Get system's date

To Use this function for date Comparison, Or displying in report, we play around this function by using
 CONVERT () , Substring (),Dateadd(), etc,..
For today 9th Aug 2011  
SELECT CONVERT(VARCHAR(19), GETDATE(), 120) -- Gives you in 2011-08-09 07:26:56
similarly
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) -- Gives you in 09.08.2011
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) -- Gives you in 2011.08.09
SELECT CONVERT(VARCHAR(19), GETDATE(), 100) -- Gives you in Aug  9 2011  7:50AM

in same way you can try for foramt 100 to 131, all will give you dates in diffrent formats.


For you query SELECT * FROM batch WHERE lastupdated >=  'Todays'

Use  SELECT * FROM batch WHERE CONVERT(VARCHAR(10), lastupdated,102) =  CONVERT(VARCHAR(10), GETDATE(), 102)


For comparison  format LHS and RHS for same date format and then compare

IN 2005 we have datetime datatype, whereas in sql2008 we have date and datetime two datatype to use for date.if you are using 2008 then convert both side to date datatype and go ahead.



As a SQL developer i will suggest you to use function as much as you can,because function are heavy on resource. it kills you performance.

feel free to revert back if you have any concerns on my solution.
Hi Zolf,
          i am sorry for typos i mentioned  "use function as much as you can" , please read this as "avoid using function as much as you can because functions consume more resource".

Regards,
Rajnish Kumar

Author

Commented:

>>Can you please post the content of the field "lastupdated"

2011-08-07 09:16:41.013

Author

Commented:

RajnishChoudhary:

>>For comparison  format LHS and RHS for same date format and then compare
i did not understand this sentence,can you please elaborate


>>"avoid using function as much as you can because functions consume more resource".

what do you mean by function.can you please give an e.g
with LHS and RHS Comparison  i mean Left Hand Side (LHS) and Right Hand Side(RHS)

in your Query SELECT * FROM batch WHERE lastupdated >=  convert(datetime, getDate(), 104)

Lastupdated is on LHS and getdate() in RHS of =

hence apply same format on both side as i mentioned in query.


with Functions i mean ltrim(),rtrim(),dateadd(),getdate(),Convert(),max(),Count(),substring()...etc..
If you can avoid these function in query it will give you better result.
but here in you scenario,you have no other option if you are using getdate very few times.

but if you are using getdate multiple time then it will be better to store formated value in one variable
and use this variable instead.
E.g:

DECLARE @TODAY VARCHAR(10)
SET @TODAY=CONVERT(VARCHAR(10), GETDATE(), 102)
SELECT * FROM batch WHERE CONVERT(VARCHAR(10), lastupdated,102)=@TODAY

Regards,
Rajnish