Datetime Data Type problem when using stored procedures

[Guest_imported]

Hi,
Im trying to append data from one table to another using a stored procedure. The original table has data saved as a specific date and time i.e. (2001-12-30 17:07:26.623) but when i append this to the new table I only want the date i.e (2001-12-30 00:00:00.000)
so i can later do a GROUP BY on the particular date. Any ideas how i do this ?

 

[chiph]

You can do something like:

CONVERT(datetime, DATEPART(yyyy,DateCol) + '-' + DATEPART(mm,DateCol) + '-' + DATEPART(dd,DateCol))

which converts just the date portion of DateCol column into a datetime datatype which you use in an insert or update statement.

Chip H.

 

[tlbroadbent]

Another way to convert datetime data to date only is with a nested convert.

Select Col1, Col2,
Convert(datetime, convert(char(11), Col3)) As DateCol
From Table
Group By Convert(datetime, convert(char(11), Col3)) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mukund]

Hi Jonny,
You can try the following
Select Col1, Col2,
Convert(varchar(10), datetimefield, 101) As DateColumn
From TableName

Hope this helps.

Mukund.