Like Operator On Date Format

Posted by Rock_Monu under Sql Server on 10/15/2013 | Points: 10 | Views : 1397 | Status : [Member] | Replies : 8

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

How TO Use Like Operator On Date Format and Format of date is yyyy-mm-dd.
nad in database the record is 2013-10-14,2014-10-15 Like This.Then I Will Filter on Year.
So Please give Proper Solution.Urgent...!!!!!!

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 10/15/2013 [Member] [MVP] Platinum | Points: 25

0	Can you give examples for which you want LIKE search? Means your are matching for particular date / year/ month? Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rock_Monu, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 10/15/2013 [Member] [MVP] Platinum | Points: 25

0	>>Will Filter on Year. we can convert the dateColumn as per out requirement... Do you wish to search records as per Year only....? SELECT * FROM TableName WHERE Year( DateColumn) = SearchValueForYear Note: Here DateColumn Should be of type DATE/DATETIME Example: SELECT CONVERT(VARCHAR(26), GETDATE(), 23) -- for getting YYYY-MM-DD format from DATETIME field References: http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html http://www.codeproject.com/Questions/613061/SQL-SERVER-like-operator-for-datetime-coloumn Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rock_Monu, if this helps please login to Mark As Answer. \| Alert Moderator

>>Will Filter on Year.
we can convert the dateColumn as per out requirement...
Do you wish to search records as per Year only....?


SELECT * FROM TableName 

WHERE Year( DateColumn) = SearchValueForYear

Note: Here DateColumn Should be of type DATE/DATETIME
Example:

SELECT CONVERT(VARCHAR(26), GETDATE(), 23)

-- for getting YYYY-MM-DD format from DATETIME field

References:
http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html
http://www.codeproject.com/Questions/613061/SQL-SERVER-like-operator-for-datetime-coloumn

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Rock_Monu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pradeepmanne on: 10/15/2013 [Member] Starter | Points: 25

0	hi, try the DATEPART() function SELECT * FROM tbl_name WHERE (DATEPART(yy, register_date) = 2013 AND DATEPART(mm, register_date) = 10 AND DATEPART(dd, register_date) = 14) Rock_Monu, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Rock_Monu on: 10/17/2013 [Member] Starter | Points: 25

0	@chandu...Sir matching on particular year...like 2013,2014 .because i want select 2013,2014 year wise data in data grid view in windows app..... Rock_Monu, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Samirbhogayta on: 10/17/2013 [Member] Starter | Points: 25

0	Can you try this SELECT * FROM TableName WHERE CONVERT(VARCHAR, DateTimeColumn , 120) LIKE '2013-06%' SAMIR Sr. Software Engineer Rock_Monu, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Nismeh on: 10/18/2013 [Member] Starter | Points: 25

0	Hey Hi, You can use 'between and' operator to set minimum and maximum value. So that you can set starting date of year and can end up with other date or year as you want. LIke you want data of 2013 and 2014. And than you can also use order by to set proper flow of output data. Select * from table where myDate between '2013-01-01' and 2014-12-31' IT KNOWLEDGE IS APPLIED KNOWLEDGE So Just Do It Rock_Monu, if this helps please login to Mark As Answer. \| Alert Moderator

Hey Hi,

You can use 'between and' operator to set minimum and maximum value. So that you can set starting date of year and can end up with other date or year as you want.

LIke you want data of 2013 and 2014. And than you can also use order by to set proper flow of output data.
Select * from table where myDate between '2013-01-01' and 2014-12-31'

IT KNOWLEDGE IS APPLIED KNOWLEDGE
So Just Do It

Rock_Monu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/18/2013 [Member] [MVP] Platinum | Points: 25

0	Hi Monu, If you select 2013 then datagrid should have the records which comes under 2013 year.. that's it right? If YES, Simply add WHERE condition while retrieving data from Database SELECT * FROM TableName WHERE Year(DateColumn) = 2013 WHERE Year(DateColumn) = YearParam NOTE: The above code dependa on the datatype of DateColumn too Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rock_Monu, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Monu,
If you select 2013 then datagrid should have the records which comes under 2013 year.. that's it right?

If YES,
Simply add WHERE condition while retrieving data from Database

SELECT * FROM TableName 
WHERE Year(DateColumn) = 2013

WHERE Year(DateColumn) = YearParam
NOTE: The above code dependa on the datatype of DateColumn too

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Rock_Monu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/22/2013 [Member] [MVP] Platinum | Points: 25

0	Have you solved the issue? If Yes, "Mark as Answer "; otherwise post us back Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rock_Monu, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts