datetime function
-
How can i get only date from datetime function into the tabel.
-
Try this. Replace '11/12/01' with your date.
select CONVERT(SMALLDATETIME, '11/12/01 12:00AM', 1) from TableName
Darren
Darren -
If you want the current date inserted in a table, I use
CONVERT(varchar(10),GETDATE(),120)
Far away is close at hand in the images of elsewhere.
Anon. -
Another way that seems a bit cleaner since CONVERT actually performs a read of the syslanguages table. Would be to do like so.
DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
-
Neat trick!
-
Ok, so I have a slightly different scenario. I'll be at work tomorrow to test some of the tricks in this post, but maybe someone can tell me if this will work.
I'm doing a grouping by a date field, and so it looks something like this.
08-24-2002 00:00:00.000 $50.00
08-25-2002 00:00:00.000 $75.00
08-30-2003 00:00:00.000 $22.00
Here's my problem, the report is fine as long as the time portion of the date is all zeros. I get a sum of all charges per day. However, when a different time value appears, I get to dollar amounts for the same day, like so...
08-24-2002 00:00:00.000 $50.00
08-25-2002 00:00:00.000 $75.00
08-30-2003 00:00:00.000 $22.00
08-30-2003 13:21:02.001 $5.00
How can I remove the time portion in the SQL so that my sums are based on the date portion only? I've tried converting to a string (varchar), but then the order by portion orders them in alphabetical order rather than the actual date order.
Currently my SQL is something like this...
select chargedate, sum(chargeAmount)
from charges
group by chargedate
order by chargedate
How do I remove the pesky time portion?
-
select CONVERT(varchar(10),chargedate,110), sum(chargeAmount)
from charges
group by CONVERT(varchar(10),chargedate,120)order by CONVERT(varchar(10),chargedate,120)
Far away is close at hand in the images of elsewhere.
Anon. -
In your sql try this:
select convert(varchar,chargedate,101), sum(chargeAmount)
from charges
group by convert(varchar,chargedate,101)
order by convert(varchar,chargedate,101)
Converting the chargedate like this removes the time element and so the rows will be grouped and summarised by the date portion only. If you look at the convert function in Help or BOL you can see how to specify different date formats to suit your needs.
Jeremy
-
Jeremy,
The only problem with your solution is if the dates span more than one year and the results are required in date order, your query would group by month first.
Far away is close at hand in the images of elsewhere.
Anon. -
David,
Your right - I hadn't spotted that. Converting everything to yyyy-mm-dd is best.
Jeremy
-
Thanks!
I will test those solutions tomorrow at work. I also just read through a book and got this solution as well....
convert(datetime, convert(varchar(10), chargedate, 112))
The inner conversion would strip out the time portion, the outer conversion would convert the string back into a date format with all zeros in the time portion (midnight).
-
if you want the resulting column to be datetime then you could use Antares solution as well, eg
select DATEADD(d,DATEDIFF(d,0,chargedate),0), sum(chargeAmount)
from charges
group by DATEADD(d,DATEDIFF(d,0,chargedate),0)
order by DATEADD(d,DATEDIFF(d,0,chargedate),0)
Far away is close at hand in the images of elsewhere.
Anon. -
Roland,
If you do this a lot, you might want to consider a user defined function:
/* Returns date only */
CREATE FUNCTION [dbo].[fn_DateOnly] (@date datetime)
RETURNS smalldatetime AS
BEGIN
return (convert(datetime, convert(varchar(10), @date, 112))
)
END
Your query then becomes:
select dbo.fn_DateOnly(chargedate), sum(chargeAmount)
from charges
group by dbo.fn_DateOnly(chargedate)
order by dbo.fn_DateOnly(chargedate)
Jeremy
-
A user defined function sounds like a great idea. Thanks a lot!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply