1,105,581 Community Members

Date filter

Member Avatar
solomon_13000
Junior Poster
119 posts since Jul 2009
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
urtrivedi
Posting Virtuoso
1,900 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 409 [?]
Skill Endorsements: 27 [?]
 
0
 
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

Member Avatar
solomon_13000
Junior Poster
119 posts since Jul 2009
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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'
Member Avatar
urtrivedi
Posting Virtuoso
1,900 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 409 [?]
Skill Endorsements: 27 [?]
 
0
 

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'
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: