datetime format [Answered]RSS

• • Reply
  aoshi_kh

  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

• • Reply
  Rajneesh Ver...

  All-Star

  28957 Points

  7244 Posts

  Re: datetime format

  Sep 28, 2010 05:32 AM|Rajneesh Verma|LINK

  <div>

  Cast the datetime to a date, then group my that:

  SELECT SUM(credit), DATE(mydate) FROM a_table GROUP BY DATE(a_table.mydate);
  

  Or you can group by the alias as:

  SELECT SUM(credit), DATE(mydate) DateOnly FROM a_table GROUP BY DateOnly;
  

  
  

  
  

  </div>
  Rajneesh Verma's Blog
  www.rajneeshverma.com
  
  
• • Reply
  shridhar.wak...

  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) 
  
  
         

  foreach(second in my life)
  {
  my experience++;
  }
  
• • Reply
  Dharit Utpal...

  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  

  Dharit Utpal Mehta
  
  Dont Forget To Click On "ANSWER BUTTON" As It is helpful to others.
  
• • Reply
  shiningstar

  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.

  Mark as answer if it helps you.
• • Reply
  aoshi_kh

  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

• • Reply
  Dharit Utpal...

  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

  
 

  Dharit Utpal Mehta
  
  Dont Forget To Click On "ANSWER BUTTON" As It is helpful to others.
  
• • Reply
  shiningstar

  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.

  Mark as answer if it helps you.