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.

Recommended Answers

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

Jump to Post

All 3 Replies

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'

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'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.