where @startdate > cast("20000101" as datetime) and @enddate <=
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

quote:

---

Originally posted by Nav522

Hi Folks,
I have this procedure which has startdate and enddate as parameteres and my requirement is to get rid of this parameter and include the logic so that it looks for the date starting from 01/01/2000 until previous month ending i.e Jan 31. Thanks a lot

---

SET DATEFORMAT DMY

SELECT CONVERT(datetime, '19991231')
SELECT CONVERT(datetime, '1999-12-31')

quote:

---

Originally posted by Kristen

OK ... there are others ... but not 8 digits plus punctuation

'31 Dec 1999'
{d '1999-12-31'}
'1999-12-31T01:02:03.456'

BOL says that "ISO 8601 Numeric '1998-02-23'" is not DATEFORMAT dependant, but that is simply not the case. Although BOL qualifies that in the text that the "T" and Time part must be used in ISO 8601

BOL says "Unseparated String Format
You can specify date data as an unseparated string. The date data can be specified by using four, six, or eight digits, an empty string, or a time value without a date value.

The SET DATEFORMAT session setting does not apply to all-numeric date entries, such as numeric entries without separators. The six-digit or eight-digit strings are always interpreted as ymd. The month and day must always be two digits.

This is the valid un-separated string format: [19]960415

A string of only four digits is interpreted as the year. The month and date are set to January 1. When you specify only four digits, you must include the century."

I think it is a crying shame that SQL Server is so liberal with its string dates, as there must be shedloads of code out that that works OK because of server locale - and if that ever needs to be moved (company merger, for example) its going to be a nightmare.

I wonder if Upgrade Advisor moans about that?

---

quote:

---

Originally posted by Kristen

Its another of my wishes ... that SQL had a STRICT mode, or LINT, so that it would warn about such usage in development.

I discovered that SET LANGUAGE also changes date interpretation ... so a user may be added to the database, set their default language, and Bang! all the string dates fall over in a heap

---