If statment on mmdd

Transact-SQL
1

hi i want to create an if satament on a date but i want it to leave out the year.

so if the date file has 20150630 i just want my if statment to look at 0630 and leave out the year. Is this possible and how can it be done

2

If your date field is date or datetime field, you can do:

case when month(datefield)*100+day(datefield)=630 then ... else ... end

if your date field is string:

case when substring(datefield,5,4)='0630' then ... else ... end

if your date field is numeric:

case when datefield%10000=630 then ... else ... end
3

This makes no sense. The IF statement controls program flow in procedural code. It does not apply to a column. Maybe you meant a CASE expression?

By definition, a DATE must have a year field (have you ever read a book on SQL or RDBMS? This will be in the section on temporal data).

Your mindset is wrong; this is SQL and not 1960's COBOL. In COBOL, there was no temporal data types and we kept dates in strings. In SQL, the term “field” means an inseparable part of a DATE, TIME or TIMESTAMP (called a DATETIME2(n) in T-SQL dialect). These are {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND} and you can do this basic research with any book on the language.

Furthermore, the only display format in ANSI/ISO Standard SQL is ISO-8601, which uses a string with the format 'yyyy-mm-dd', with the dashes. We do not use INTEGER as you have.

Finally, just have a month-day interval is weird. T-SQL does not have INTERVAL data types yet; Google it. What good do they do you with a year? Would you store just the inches without feet for a distance?

What are you trying to do? How do you want to handle leap years?

4

You could try convert

RIGHT(CONVERT(CHAR(8), @yourdate, 112), 4)
5

Yes. You can compare MONTH() and DAY() of the dates:

WHERE MONTH(date_file_date) = MONTH(table_date) AND DAY(date_file_date) = DAY(table_date)
6

There could be some uses for such data. For example, what time of the year produces the largest number of births or deaths? Do more people die in winter than in summer? Are more babies born 10 months after winter, etc.

I came across this recently. Would have never guessed!! Apparently, it is very useful in forensic accounting (assuming of course that the crooks don't know about Benford's law.)