If you are using MSSQL then use GROUP BY CONVERT(VARCHAR(10),YourDateColumn,101) and if you are using MSSQL 2008 the you can use GROUP BY CONVERT(DATE,YourDateColumn)
i using sql server. i have try day(myDate) and date(myDate) but it all not work.. i wish to group by the date format like this -- > "2010-09-09" without the time behine 2010-09-09 12:00:41.29
CREATE FUNCTION [dbo].[ufsFormat] ( @Date datetime, @fORMAT VARCHAR(80)
) RETURNS NVARCHAR(80) AS
BEGIN
DECLARE @Dateformat INT
DECLARE @ReturnedDate VARCHAR(80) DECLARE @TwelveHourClock
INT
DECLARE @Before INT
DECLARE @pos INT
DECLARE @Escape INT
-- (c) Pinal Dave http://www.SQLAuthority.com SELECT @ReturnedDate='error! unrecognised format '+@format SELECT @DateFormat=CASE
@format WHEN 'mmm dd yyyy hh:mm AM/PM'
THEN 100 WHEN 'mm/dd/yy' THEN 1 WHEN 'mm/dd/yyyy' THEN 101 WHEN 'yy.mm.dd' THEN 2 WHEN 'dd/mm/yy' THEN 3 WHEN 'dd.mm.yy' THEN 4 WHEN 'dd-mm-yy' THEN 5 WHEN 'dd Mmm yy' THEN 6 WHEN 'Mmm dd, yy' THEN 7 WHEN 'hh:mm:ss' THEN 8 WHEN 'yyyy.mm.dd' THEN 102 WHEN 'dd/mm/yyyy' THEN 103 WHEN 'dd.mm.yyyy' THEN 104 WHEN 'dd-mm-yyyy' THEN 105 WHEN 'dd Mmm yyyy'
THEN 106 WHEN 'Mmm dd, yyyy'
THEN 107 WHEN 'Mmm dd yyyy hh:mm:ss:ms AM/PM'
THEN 9 WHEN 'Mmm dd yyyy hh:mi:ss:mmm AM/PM'
THEN 9 WHEN 'Mmm dd yy hh:mm:ss:ms AM/PM'
THEN 109 WHEN 'mm-dd-yy' THEN 10 WHEN 'mm-dd-yyyy' THEN 110 WHEN 'yy/mm/dd' THEN 11 WHEN 'yyyy/mm/dd' THEN 111 WHEN 'yymmdd' THEN 12 WHEN 'yyyymmdd' THEN 112 WHEN 'dd Mmm yyyy hh:mm:ss:Ms'
THEN 113 WHEN 'hh:mm:ss:Ms'
THEN 14 WHEN 'yyyy-mm-dd hh:mm:ss'
THEN 120 WHEN 'yyyy-mm-dd hh:mm:ss.Ms'
THEN 121 WHEN 'yyyy-mm-ddThh:mm:ss.Ms'
THEN 126 WHEN 'dd Mmm yyyy hh:mm:ss:ms AM/PM'
THEN 130 WHEN 'dd/mm/yy hh:mm:ss:ms AM/PM'
THEN 131 WHEN 'RFC822' THEN 2 WHEN 'dd Mmm yyyy hh:mm'
THEN 4 ELSE 1 END
SELECT @ReturnedDate='error! unrecognised format '
+@format+CONVERT(VARCHAR(10),@DateFormat) IF @DateFormat>=0 SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat) --check for favourite and custom formats that can be done quickly ELSE IF @DateFormat=-2--then it is RFC822 format SELECT @ReturnedDate=LEFT(DATENAME(dw,
@Date),3) +
', ' + STUFF(CONVERT(NVARCHAR,@Date,113),21,4,' GMT') ELSE IF @DateFormat=-4--then it is european day format with minutes SELECT @ReturnedDate=CONVERT(CHAR(17),@Date,113) ELSE
BEGIN
SELECT @Before=LEN(@format) SELECT @Format=REPLACE(REPLACE(REPLACE(
@Format,'AM/PM','#'),'AM','#'),'PM','#') SELECT @TwelveHourClock=CASE
WHEN @Before >LEN(@format)
THEN 109 ELSE 113 END,
@ReturnedDate='' WHILE (1=1)--forever BEGIN
SELECT @pos=PATINDEX('%[yqmidwhs:#]%',@format+' ') IF @pos=0--no more date format strings BEGIN
SELECT @ReturnedDate=@ReturnedDate+@format BREAK
END
IF @pos>1--some stuff to pass through first BEGIN
SELECT @escape=CHARINDEX
('\',@Format+'\')
--is it a literal character that is escaped? IF @escape<@pos
BEGIN
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@escape-1) +SUBSTRING(@format,@escape+1,1) SET @format=RTRIM(SUBSTRING(@Format,@Escape+2,80)) CONTINUE
END
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@pos-1) SET @format=RTRIM(SUBSTRING(@Format,@pos,80)) END
SELECT @pos=PATINDEX('%[^yqmidwhs:#]%',@format+' ')--get the end SELECT @ReturnedDate=@ReturnedDate+--'('+substring(@Format,1,@pos-1)+')'+ CASE SUBSTRING(@Format,1,@pos-1) --Mmmths as 1--12 WHEN 'M' THEN
CONVERT(VARCHAR(2),DATEPART(MONTH,@Date)) --Mmmths as 01--12 WHEN 'Mm' THEN
CONVERT(CHAR(2),@Date,101) --Mmmths as Jan--Dec WHEN 'Mmm' THEN CONVERT(CHAR(3),DATENAME(MONTH,@Date)) --Mmmths as January--December WHEN 'Mmmm' THEN DATENAME(MONTH,@Date) --Mmmths as the first letter of the Mmmth WHEN 'Mmmmm' THEN CONVERT(CHAR(1),DATENAME(MONTH,@Date)) --Days as 1--31 WHEN 'D' THEN
CONVERT(VARCHAR(2),DATEPART(DAY,@Date)) --Days as 01--31 WHEN 'Dd' THEN
CONVERT(CHAR(2),@date,103) --Days as Sun--Sat WHEN 'Ddd' THEN CONVERT(CHAR(3),DATENAME(weekday,@Date)) --Days as Sunday--Saturday WHEN 'Dddd' THEN DATENAME(weekday,@Date) --Years as 00--99 WHEN 'Yy' THEN
CONVERT(CHAR(2),@Date,12) --Years as 1900--9999 WHEN 'Yyyy' THEN DATENAME(YEAR,@Date) WHEN 'hh:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8) WHEN 'hh:mm:ss:ms'
THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12) WHEN 'h:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8) --tthe SQL Server BOL syntax, for compatibility WHEN 'hh:mi:ss:mmm'
THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12) WHEN 'h:mm:ss:ms' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12) WHEN 'H:m:s' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30),
@Date,@TwelveHourClock),13,8),':0',':'),2,30) WHEN 'H:m:s:ms' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30),
@Date,@TwelveHourClock),13,12),':0',':'),2,30) --Hours as 00--23 WHEN 'hh' THEN
REPLACE(SUBSTRING(CONVERT(CHAR(30),
@Date,@TwelveHourClock),13,2),' ','0') --Hours as 0--23 WHEN 'h' THEN
LTRIM(SUBSTRING(CONVERT(CHAR(30),
@Date,@TwelveHourClock),13,2)) --Minutes as 00--59 WHEN 'Mi' THEN
DATENAME(minute,@date) WHEN 'mm' THEN
DATENAME(minute,@date) WHEN 'm' THEN
CONVERT(VARCHAR(2),DATEPART(minute,@date)) --Seconds as 0--59 WHEN 'ss' THEN
DATENAME(second,@date) --Seconds as 0--59 WHEN 'S' THEN
CONVERT(VARCHAR(2),DATEPART(second,@date)) --AM/PM WHEN 'ms' THEN
DATENAME(millisecond,@date) WHEN 'mmm' THEN DATENAME(millisecond,@date) WHEN 'dy' THEN
DATENAME(dy,@date) WHEN 'qq' THEN
DATENAME(qq,@date) WHEN 'ww' THEN
DATENAME(ww,@date) WHEN '#' THEN
REVERSE(SUBSTRING(REVERSE(CONVERT(CHAR(26),
@date,109)),1,2)) ELSE SUBSTRING(@Format,1,@pos-1) END
SET @format=RTRIM(SUBSTRING(@Format,@pos,80)) END
END
RETURN @ReturnedDate END GO SELECT [dbo].[ufsFormat]
('8/7/2008',
'mm/dd/yy') GO SELECT [dbo].[ufsFormat]
('8/7/2008',
'hh:mm:ss') GO SELECT [dbo].[ufsFormat]
('8/7/2008',
'mmm') GO SELECT [dbo].[ufsFormat]
('8/7/2008',
'Mmm dd yyyy hh:mm:ss:ms AM/PM') GO SELECT [dbo].[ufsFormat]
('8/7/2008',
'#') GO
Dharit Utpal Mehta
Dont Forget To Click On "ANSWER BUTTON" As It is helpful to others.
Member
92 Points
406 Posts
datetime format
Sep 28, 2010 05:10 AM|aoshi_kh|LINK
if i have field datetime = 2010-09-08 11:31:59.123
2010-09-08 17:42:08.737 2010-09-09 9:42:08.737
how can i group by only date? Eg: 2010-09-08 = 2 record 2010-09-09 = 10 record
currently when i use group by datetime.. because of the time differece, so it show out all the row. Please help
All-Star
28988 Points
7251 Posts
Re: datetime format
Sep 28, 2010 05:32 AM|Rajneesh Verma|LINK
Cast the datetime to a date, then group my that:
Or you can group by the alias as:
</div>www.rajneeshverma.com
Participant
1530 Points
498 Posts
Re: datetime format
Sep 28, 2010 05:34 AM|shridhar.wakhare|LINK
If you are using MSSQL then use GROUP BY CONVERT(VARCHAR(10), YourDateColumn, 101) and if you are using MSSQL 2008 the you can use GROUP BY CONVERT(DATE, YourDateColumn)
{
my experience++;
}
Member
260 Points
105 Posts
Re: datetime format
Sep 28, 2010 05:55 AM|Dharit Utpal Mehta|LINK
Select Avg(Salary) from TableName Group By Created Date
Dont Forget To Click On "ANSWER BUTTON" As It is helpful to others.
Member
684 Points
274 Posts
Re: datetime format
Sep 28, 2010 06:02 AM|shiningstar|LINK
if you are using sql server as your database, you can use its in-built functions day(), month(), year() to create a date string as done below:
select * from myTable
group by day(record_date) + '-' + month(record_date) + '-' + year(record_date)
where record_date is date field in the table.
Hope this helps you.
Mark as answer if it helps you.
Member
92 Points
406 Posts
Re: datetime format
Sep 28, 2010 06:12 AM|aoshi_kh|LINK
i using sql server. i have try day(myDate) and date(myDate) but it all not work.. i wish to group by the date format like this -- > "2010-09-09" without the time behine 2010-09-09 12:00:41.29
Member
260 Points
105 Posts
Re: datetime format
Sep 28, 2010 06:28 AM|Dharit Utpal Mehta|LINK
http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/
CREATE FUNCTION [dbo].[ufsFormat]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
DECLARE @Dateformat INT
DECLARE @ReturnedDate VARCHAR(80)
DECLARE @TwelveHourClock INT
DECLARE @Before INT
DECLARE @pos INT
DECLARE @Escape INT
-- (c) Pinal Dave http://www.SQLAuthority.com
SELECT @ReturnedDate='error! unrecognised format '+@format
SELECT @DateFormat=CASE @format
WHEN 'mmm dd yyyy hh:mm AM/PM' THEN 100
WHEN 'mm/dd/yy' THEN 1
WHEN 'mm/dd/yyyy' THEN 101
WHEN 'yy.mm.dd' THEN 2
WHEN 'dd/mm/yy' THEN 3
WHEN 'dd.mm.yy' THEN 4
WHEN 'dd-mm-yy' THEN 5
WHEN 'dd Mmm yy' THEN 6
WHEN 'Mmm dd, yy' THEN 7
WHEN 'hh:mm:ss' THEN 8
WHEN 'yyyy.mm.dd' THEN 102
WHEN 'dd/mm/yyyy' THEN 103
WHEN 'dd.mm.yyyy' THEN 104
WHEN 'dd-mm-yyyy' THEN 105
WHEN 'dd Mmm yyyy' THEN 106
WHEN 'Mmm dd, yyyy' THEN 107
WHEN 'Mmm dd yyyy hh:mm:ss:ms AM/PM' THEN 9
WHEN 'Mmm dd yyyy hh:mi:ss:mmm AM/PM' THEN 9
WHEN 'Mmm dd yy hh:mm:ss:ms AM/PM' THEN 109
WHEN 'mm-dd-yy' THEN 10
WHEN 'mm-dd-yyyy' THEN 110
WHEN 'yy/mm/dd' THEN 11
WHEN 'yyyy/mm/dd' THEN 111
WHEN 'yymmdd' THEN 12
WHEN 'yyyymmdd' THEN 112
WHEN 'dd Mmm yyyy hh:mm:ss:Ms' THEN 113
WHEN 'hh:mm:ss:Ms' THEN 14
WHEN 'yyyy-mm-dd hh:mm:ss' THEN 120
WHEN 'yyyy-mm-dd hh:mm:ss.Ms' THEN 121
WHEN 'yyyy-mm-ddThh:mm:ss.Ms' THEN 126
WHEN 'dd Mmm yyyy hh:mm:ss:ms AM/PM' THEN 130
WHEN 'dd/mm/yy hh:mm:ss:ms AM/PM' THEN 131
WHEN 'RFC822' THEN 2
WHEN 'dd Mmm yyyy hh:mm' THEN 4
ELSE 1 END
SELECT @ReturnedDate='error! unrecognised format ' +@format+CONVERT(VARCHAR(10),@DateFormat)
IF @DateFormat>=0
SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
--check for favourite and custom formats that can be done quickly
ELSE IF @DateFormat=-2--then it is RFC822 format
SELECT @ReturnedDate=LEFT(DATENAME(dw, @Date),3) + ', ' + STUFF(CONVERT(NVARCHAR,@Date,113),21,4,' GMT')
ELSE IF @DateFormat=-4--then it is european day format with minutes
SELECT @ReturnedDate=CONVERT(CHAR(17),@Date,113)
ELSE
BEGIN
SELECT @Before=LEN(@format)
SELECT @Format=REPLACE(REPLACE(REPLACE( @Format,'AM/PM','#'),'AM','#'),'PM','#')
SELECT @TwelveHourClock=CASE WHEN @Before >LEN(@format) THEN 109 ELSE 113 END, @ReturnedDate=''
WHILE (1=1)--forever
BEGIN
SELECT @pos=PATINDEX('%[yqmidwhs:#]%',@format+' ')
IF @pos=0--no more date format strings
BEGIN
SELECT @ReturnedDate=@ReturnedDate+@format
BREAK
END
IF @pos>1--some stuff to pass through first
BEGIN
SELECT @escape=CHARINDEX ('\',@Format+'\') --is it a literal character that is escaped?
IF @escape<@pos BEGIN
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@escape-1) +SUBSTRING(@format,@escape+1,1)
SET @format=RTRIM(SUBSTRING(@Format,@Escape+2,80))
CONTINUE
END
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@pos-1)
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
SELECT @pos=PATINDEX('%[^yqmidwhs:#]%',@format+' ')--get the end
SELECT @ReturnedDate=@ReturnedDate+--'('+substring(@Format,1,@pos-1)+')'+
CASE SUBSTRING(@Format,1,@pos-1)
--Mmmths as 1--12
WHEN 'M' THEN CONVERT(VARCHAR(2),DATEPART(MONTH,@Date))
--Mmmths as 01--12
WHEN 'Mm' THEN CONVERT(CHAR(2),@Date,101)
--Mmmths as Jan--Dec
WHEN 'Mmm' THEN CONVERT(CHAR(3),DATENAME(MONTH,@Date))
--Mmmths as January--December
WHEN 'Mmmm' THEN DATENAME(MONTH,@Date)
--Mmmths as the first letter of the Mmmth
WHEN 'Mmmmm' THEN CONVERT(CHAR(1),DATENAME(MONTH,@Date))
--Days as 1--31
WHEN 'D' THEN CONVERT(VARCHAR(2),DATEPART(DAY,@Date))
--Days as 01--31
WHEN 'Dd' THEN CONVERT(CHAR(2),@date,103)
--Days as Sun--Sat
WHEN 'Ddd' THEN CONVERT(CHAR(3),DATENAME(weekday,@Date))
--Days as Sunday--Saturday
WHEN 'Dddd' THEN DATENAME(weekday,@Date)
--Years as 00--99
WHEN 'Yy' THEN CONVERT(CHAR(2),@Date,12)
--Years as 1900--9999
WHEN 'Yyyy' THEN DATENAME(YEAR,@Date)
WHEN 'hh:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
WHEN 'hh:mm:ss:ms' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'h:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
--tthe SQL Server BOL syntax, for compatibility
WHEN 'hh:mi:ss:mmm' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'h:mm:ss:ms' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'H:m:s' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,8),':0',':'),2,30)
WHEN 'H:m:s:ms' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,12),':0',':'),2,30)
--Hours as 00--23
WHEN 'hh' THEN REPLACE(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2),' ','0')
--Hours as 0--23
WHEN 'h' THEN LTRIM(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2))
--Minutes as 00--59
WHEN 'Mi' THEN DATENAME(minute,@date)
WHEN 'mm' THEN DATENAME(minute,@date)
WHEN 'm' THEN CONVERT(VARCHAR(2),DATEPART(minute,@date))
--Seconds as 0--59
WHEN 'ss' THEN DATENAME(second,@date)
--Seconds as 0--59
WHEN 'S' THEN CONVERT(VARCHAR(2),DATEPART(second,@date))
--AM/PM
WHEN 'ms' THEN DATENAME(millisecond,@date)
WHEN 'mmm' THEN DATENAME(millisecond,@date)
WHEN 'dy' THEN DATENAME(dy,@date)
WHEN 'qq' THEN DATENAME(qq,@date)
WHEN 'ww' THEN DATENAME(ww,@date)
WHEN '#' THEN REVERSE(SUBSTRING(REVERSE(CONVERT(CHAR(26), @date,109)),1,2))
ELSE
SUBSTRING(@Format,1,@pos-1)
END
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
END
RETURN @ReturnedDate
END
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mm/dd/yy')
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'hh:mm:ss')
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mmm')
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'Mmm dd yyyy hh:mm:ss:ms AM/PM')
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', '#')
GO
Dont Forget To Click On "ANSWER BUTTON" As It is helpful to others.
Member
684 Points
274 Posts
Re: datetime format
Sep 29, 2010 12:09 AM|shiningstar|LINK
select year(myDate) +'-' + month(myDate) + '-' day(myDate) from myTable.
Mark as answer if it helps you.