Nvarchar to Date

Question

In my table I have

ReportingWeek (Date)         eg 2017-03-19
BookedWeek (nvarchar(12)     eg 07.2017

I would like to select on rows where the BookedWeek is in the last 17 weeks.

Do I need to convert BookedWeek to a date, then query it for last 17 weeks, or can it be done directly as a nvarchar?

Which dbms are you using? (When it comes to date/time many products are far from ANSI SQL compliant.) — jarlh, Mar 22 '17 at 10:42

score 0 · Answer 1 · answered Mar 22 '17 at 10:51

0

Yes, you have to convert string to datetime first, see this example :

SELECT * WHERE DATEDIFF(DAY,cast('2017-03-19' AS datetime),GETDATE())<17

answered Mar 22 '17 at 10:51

Rajendra

191
1
13

score -1 · Answer 2 · answered Mar 22 '17 at 10:43

You don't need to convert the value to a date. If you want to treat it as a date, that is reasonable. This is especially important if you want to compare it to "real world" dates.

I should note that if you are going to store the value as a string, you should store it in YYYYWW format (YYYY.WW is fine). That way, the values can be sorted and compared to each other.

score -1 · Answer 3 · answered Mar 22 '17 at 11:10

No need to convert bookedweek to date. We can handle by string functions. Below query should give you the expected result, considering across the year too.

create table #tmp
(
bookedweek nvarchar(12)
)
insert into #tmp values ('07.2017'),('09-2017'),('01.2017'),('50.2016'),('52.2016'),('13.2016'),('48.2016'),('49.2016')
select * from #tmp
where (year(getdate())=RIGHT(bookedweek,4) and datepart(week,getdate())- LEFT(bookedweek,2)<17)
      OR (year(getdate()) - RIGHT(bookedweek,4)=1 and 53 - LEFT(bookedweek,2) + datepart(week,getdate()) < 17)
drop table #tmp

Here 53 is the total weeks in a calendar year.

Nvarchar to Date

3 Answers3