get month and year and day in query

[dot_net_help]

i have following query


SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, starttime)) as date FROM table

and these me return like this

2011-02-03 00:00:00.000


i want to convert that query that give reult like this

thursday ,feburay 2,2011

how to do this ??

[Pradeep1210]

Assuming you have your date in @date variable (otherwise replace with your date column name):

SELECT DATENAME(weekday, @date) + ', ' 
     + DATENAME(month,   @date) + ' ' 
     + DATENAME(day,     @date) + ', ' 
     + DATENAME(year,    @date) + ' '

[Pradeep1210]

Note that we did that because none of the CONVERT options satisfy your need. If you can be content with one of these predefined formats, then you should prefer CONVERT.
http://msdn.microsoft.com/en-us/library/ms187928.aspx

[dot_net_help]

it should be like thi s??

DECLARE @VariableName datetime

set @VariableName = SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, starttime)) as date FROM table

this giving me error ..

Incorrect syntax near the keyword 'SELECT'.

[Pradeep1210]

DECLARE @date DATETIME
SELECT DISTINCT @date = DATEADD(day, 0, DATEDIFF(day, 0, starttime)) FROM table1 
SELECT DATENAME(weekday, @date) + ', ' 
     + DATENAME(month, @date) + ' ' 
     + DATENAME(day, @date) + ', ' 
     + DATENAME(year, @date) + ' '