April 2, 2015 at 2:53 am
Hello sir,
I have to filter my some of the data using below sql
select * from dbo.tmp_date
where issue_dt <'042015' but its not filtering out because of NVARCHAR an if suppose i used datatype to int its removing 0 (zero) from the begning
so pls help me how to resolve this issue.
My data is in formate MMYYYY formate
CREATE TABLE dbo.tmp_date (issue_dt NVARCHAR(10));
INSERT INTO dbo.tmp_date (issue_dt)
SELECT '062012'
UNION
SELECT '062079'
UNION
SELECT '062015'
UNION
SELECT '062014'
GO
select * from dbo.tmp_date
where issue_dt <'042015'
go
April 2, 2015 at 4:25 am
Unfortunately, the date format you are using is not supported in the CONVERT function.
You definitely should change the data type of that column: if it is supposed to store dates, use the date data type. The conversion to show the users their preferres format should be done in the application and not in the database.
A while back, I wrote an article on dealing with custom date formats in T-SQL[/url]. You can probably use the parse function in that article or you could write your own conversion to a date with something similar to this:
DECLARE @filter_date nvarchar(10) = '042015';
select *
from dbo.tmp_date
where CONVERT(date,SUBSTRING(issue_dt,3,4) + SUBSTRING(issue_dt,1,2) + '01',112) < CONVERT(date, SUBSTRING(@filter_date,3,4) + SUBSTRING(@filter_date,1,2) + '01', 112)
Anyway, if you still decide that a date column is not what you want, NVARCHAR is overkill if you're just storing numeric characters.
-- Gianluca Sartori
April 2, 2015 at 5:39 am
Thanks Sir for your help 🙂
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply