Date/Time as Date Only

[Jamie2002]

I've got a date/time field and need to know how to convert it into date only so that my query groups by day.

Any help is appreciated.

Thanks.
J

 

[Ed2020]

Rather than grouping on the field try creating an expression in your query: Expr1: Format([DateField],"dd/mm/yyyy&quot

Hope this helps.

Ed Metcalfe.

 

[Jamie2002]

I am using SQL Server 6.5, sorry I did'nt make this clear.

 

[tlbroadbent]

In SQL 6.5, use the Convert function.

Group By Convert(char(10), MyDate, 112) Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[Jamie2002]

Thanks. Works perfect.

 

[melberti]

I'm trying to do the same thing, essentially. Here's the gist of my SQL:

SELECT * FROM TABLE WHERE DATE_PART_ONLY_OF(datetime column name) = '09/20/2002';

I tried format(columname,'mm/dd/yyyy') and it errors on me, that format is not a recognized function name.

Thanks for any help.

 

[SQLBill]

Which type of SQL are you using? ORACLE, Microsoft Transact-SQL, ACCESS, or other?

Microsoft Transact-SQL (used with MS SQL Server, which is what this forum is for) doesn't have 'DATE_PART_ONLY_OF or FORMAT as part of its syntax.

If you aren't using Microsoft SQL Server, you would be better off posting in the proper forum.

-SQLBill

 

[SQLBill]

By the way, my previous post was meant for melberti who 'tacked' his query onto this thread.

-SQLBill

 

[melberti]

Transact SQL, I guess... I'm building a dynamic querystring. I'm trying to compare a date provided by a user with a date/time stamp stored in the database -- I want to disregard the time and look only at the date itself.

I know there's no DATE_PART_ONLY_OF -- I was simply trying to show where I'm having the issue. That's what I'm trying to do, get ONLY the date part of that date/time column value.

I'm trying to use the CONVERT function now, but cannot quite figure it out.

 

[SQLBill]

Try this:

convert(char(8), getdate(), 1)

what that does is: gets the system date (today's date) converts it to mm/dd/yy hh:mm:ss format; then returns only the first 8 characters (mm/dd/yy).

To test it, just add SELECT in front. Then you can replace the getdate() function with your column name, a variable, whatever has a datetime type.

By the way, in the future please start your own thread. Why? The person who creates the thread usually 'marks' the thread for email notification so they know when someone responds. Look at the beginning of this thread on the right side, there's a spot marked "Who's marked this thread?" Now the original poster is getting all these email notifications when none of them are responses to his/her post. So, this is a courtesy type of request.

-SQLBill

 

[MeanGreen]

Try this:
select convert(varchar(10),<col1>,101) from <table>

This will get you the date format as 09/20/2002 in character format. Hope this helps.