• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

SQL Dates

Hi,

I have a field in my DB that is a SmallDateTime, I am trying to get all the records that are for today, I tried using:

SELECT id, DeliveryDate FROM tbl_items WHERE (DeliveryDate = GetDate())

But this does not work it returns nothing

How can I specify the date correctly, do I need to cast the DeliveryDate to a Date not time as well?

Thanks
0
sanjshah12
Asked:
sanjshah12
2 Solutions
 
LIONKINGCommented:
Try this:

SELECT id, DeliveryDate
FROM tbl_items
WHERE DeliveryDate = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>a field in my DB that is a SmallDateTime
This has a time component

>= GetDate())
This also has a time component

>I am trying to get all the records that are for today,
This is date without time, so if you want to get the date only you'll have to compare both values as date:

SELECT id, DeliveryDate FROM tbl_items WHERE CAST(DeliveryDate as date) = CAST(GetDate() as date)
0
 
tlaytonCommented:
It wont work like that because GETDATE gets the current datetime (including hours, minutes, seconds etc) and you only want the day, try something like this:

SELECT id, DeliveryDate FROM tbl_items WHERE (YEAR(DeliveryDate ) = YEAR(GETDATE()) AND MONTH(DeliveryDate ) = MONTH(GETDATE()) AND DAY(DeliveryDate ) = DAY(GETDATE()))
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Éric MoreauSenior .Net ConsultantCommented:
SELECT id, DeliveryDate FROM tbl_items WHERE (DeliveryDate = convert(varchar(10), GETDATE(), 120))
0
 
Berkson WeinTech FreelancerCommented:
Are you storing the time of day as well, or just the date?
0
 
LIONKINGCommented:
As stated above, the query will return nothing because GETDATE() has a time component that is not going to match the time component in your field.
If you would like to ignore the time component you can try the code I posted.

jimhorn's solution would work in SQL Server 2008+, but if you're using SQL 2k5 you're going to have to deal with the time component of your field and GETDATE.
0
 
kapilkhalasCommented:
Hi,

If your dateformat is mm/dd/yyyy then you can write the following query

SELECT id, DeliveryDate FROM tbl_items WHERE (DeliveryDate = CONVERT(varchar, GetDATE(), 101))

and if the date format is yyyy/mm/dd then instead of 101 write 111.

Hope this helps you.
0
 
Saurabh BhadauriaCommented:
Try this..


SELECT id, DeliveryDate FROM tbl_items WHERE
DeliveryDate between
cast(convert(char(10),getdate(),121) as datetime)  and
 cast( (convert(char(10),getdate(),121) + ' 23:59:59:000') as datetime)
0
 
Scott PletcherSenior DBACommented:
SELECT
    ...
FROM dbo.tbl_items
WHERE
    DeliveryDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
    DeliveryDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)


Which is equivalent to >= today at midnight and < tomorrow at midnight, that is, ALL of TODAY only.

That makes time irrelevant, which is best, rather than trying to code an exact ending time, since it's so open to mistakes (for example, ' 23:59:59:000' is not correct, it should be ' 23:59' since the column was stated as smalldatetime; for full datetime, it would be '23:59:59.997', not 998 or 999 -- no way everyone will remember all that!!).

And if the time were later changed to full datetime, or datetime2, any code with explicit times would again be wrong anyway.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Alpesh PatelAssistant ConsultantCommented:
DateTime have date and time part, however getdate contains current date and time. so I will not get  todays data. for that you have to write code for datestart and end with time.
0
 
sanjshah12Author Commented:
Wow so many anwers, thank guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now