Date filter

[solomon_13000]

The date stored in my db is in the following format:

2011-06-10 17:15:26.000
2011-06-10 17:15:27.000
2011-06-10 17:15:28.000
2011-06-10 17:15:29.000

However I intend to retrieve the date above using the format below:

2011-06-10

How is it done?

Your help is kindly appreciated.

[urtrivedi]

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]

For details refer following link http://www.sql-server-helper.com/tips/date-formats.aspx

[solomon_13000]

When I perform a filteration using the code below I do not see any results.

select  
                    a.id as 'id', 
                    a.adjust as 'adjust', 
                    a.counter as 'counterCode',  
                    c.name as 'counterName', 
                    a.clientName as 'clientName', 
                    a.loginId as 'loginId', 
                    a.exchRate as 'exchRate', 
                    a.filledQty as 'filledQty', 
                    a.ordStatus as 'ordStatus', 
                    a.price as 'price', 
                    a.quantity as 'quantity', 
                    a.remark as 'remark', 
                    a.settCurr as 'settCurr', 
                    tradCurr=isNull(c.currency, b.currency),  
                    a.traderCode as 'traderCode', 
                    a.tradingAccNo as 'tradingAccNo', 
                    a.transType as 'transType', 
                    a.createdDate as 'createdDate',
                    b.id as 'exchId', 
                    b.name as 'exchCode' 
                    from rm_markettransaction a 
                    left join RM_Exchange b 
                    on a.rm_exchange_fk = b.id 
                    left join RM_Counter c 
                    on c.code = a.counter 
                    where b.srcType='offline' and a.createdDate = '2011-06-10'

[urtrivedi]

When you are not using code posted by me then how you can expect result. use following query

select  
                    a.id as 'id', 
                    a.adjust as 'adjust', 
                    a.counter as 'counterCode',  
                    c.name as 'counterName', 
                    a.clientName as 'clientName', 
                    a.loginId as 'loginId', 
                    a.exchRate as 'exchRate', 
                    a.filledQty as 'filledQty', 
                    a.ordStatus as 'ordStatus', 
                    a.price as 'price', 
                    a.quantity as 'quantity', 
                    a.remark as 'remark', 
                    a.settCurr as 'settCurr', 
                    tradCurr=isNull(c.currency, b.currency),  
                    a.traderCode as 'traderCode', 
                    a.tradingAccNo as 'tradingAccNo', 
                    a.transType as 'transType', 
                    a.createdDate as 'createdDate',
                    b.id as 'exchId', 
                    b.name as 'exchCode' 
                    from rm_markettransaction a 
                    left join RM_Exchange b 
                    on a.rm_exchange_fk = b.id 
                    left join RM_Counter c 
                    on c.code = a.counter 
                    where b.srcType='offline' and CONVERT(VARCHAR(10), a.createdDate, 120) = '2011-06-10'