SQL Custom Parameter

[PHTech]

1

 

[Necuno]

so you only want the month part and compare that to Billing.BillingDate ?

 

[Lazy]

Tried this?

WHERE year(Billing.BillingDate) = 2009

can be expanded to:

WHERE year(Billing.BillingDate) = 2009 AND month(Billing.BillingDate) = 6


can be expanded to:

WHERE year(Billing.BillingDate) = 2009 AND month(Billing.BillingDate) = 6 AND day(Billing.BillingDate) = 30

Something like that yes. It should basicly complete the YEAR part, me entering the MONTH, and the DAY can be any day "*".

WHERE month(Billing.BillingDate) = <whatever you enter>

 

[PHTech]

THANX Lazy...!!! That worked 100%

 

[Lazy]

THANX Lazy...!!! That worked 100%

No problem

 

[guest2013-1]

Also

PHTech, please please please please use the following date format:

30-Jun-2009

I promise you it will save you MONTHS of headaches

 

[PHTech]

Also

PHTech, please please please please use the following date format:

30-Jun-2009

I promise you it will save you MONTHS of headaches

Will do so... Thanx for the advice and thanx for all the help...!

 

[Tesla]

Also

PHTech, please please please please use the following date format:

30-Jun-2009

I promise you it will save you MONTHS of headaches

Would you mind elaborating a bit?

 

[dequadin]

Would you mind elaborating a bit?

I think I know what acid was onto there. Take today's date, is it 11-09-2009 or 09-11-2009 or 2009-11-09 or 2009-09-11 or 11-Aug-2009?

Only one of those is totally unambiguous if you don't know beforehand which date format you are working with.

 

[Tesla]

I think I know what acid was onto there. Take today's date, is it 11-09-2009 or 09-11-2009 or 2009-11-09 or 2009-09-11 or 11-Aug-2009?

Only one of those is totally unambiguous if you don't know beforehand which date format you are working with.

Definitely 11 August, because the other options are either September or November

 

[dequadin]

Definitely 11 August, because the other options are either September or November

lol my bad but you get the idea

 

[guest2013-1]

Exactly guys. Remember, your application may go to a PC that doesn't have the date configured exactly like the way you programmed it for. Using the sequence I described will give total transparency to the database (or related components) you're using that needs the date.

No computer in the world can't understand 12-Dec-2009, but some, depending on the regional settings, will struggle with 12/03/09

 

[Tesla]

lol my bad but you get the idea

Yes thanks.

Was just wondering because I too seemed to struggle in the past with not knowing which way the database would interpret say 09/09/2009.
I found that always having my language set to 'British English' on my Login account (speaking of MSSQL in particular) solved my problem. 09/09/2009 will always be interpreted as dd/mm/yyyy.

EDIT:
But I gather that AcidRaZor was specifically referring to displaying of the date.
A Procedure with a SELECT CONVERT instead of a concatenation of the various year/month/day could work well. A computed
column would be possible but could have some performance issues.

 

[Tesla]

Exactly guys. Remember, your application may go to a PC that doesn't have the date configured exactly like the way you programmed it for. Using the sequence I described will give total transparency to the database (or related components) you're using that needs the date.

No computer in the world can't understand 12-Dec-2009, but some, depending on the regional settings, will struggle with 12/03/09

Ok, I get what you're saying.

 

[FarligOpptreden]

I read up somewhere that some standard set for dates (might be ISO) is to write it out as yyyyMMdd. We've had tons of date parsing issues with different servers having different regional settings. Since we changed it to yyyyMMdd we haven't had a single problem... yet.

 

[guest2013-1]

EDIT:
But I gather that AcidRaZor was specifically referring to displaying of the date.
A Procedure with a SELECT CONVERT instead of a concatenation of the various year/month/day could work well. A computed
column would be possible but could have some performance issues.

INSERT INTO sometable (ddatefield) VALUES ('12-Dec-2009')

That works. And I just follow through with displaying the date in that format as well. Because then people in America can't force their mm/dd/yyyy format on there and miss a meeting on my CRM system I wrote because they got the date wrong. Ok?

It's a universal format understood by all human's and computers alike. Especially when you don't control the enviroment your application is running on, for example, if the server is located in Dubai vs a server located in the US.

Now granted, if you have your own dedicated server you'll setup the regional settings to reflect correctly... but when you don't... chaos.

Seen this happen with many programmers before AND ONLY YOU CAN PREVENT FOREST FIRES... I mean... prevent your program from "crashing" just because someone installed it on their PC that might have different regional settings

 

[TelkomUseless]

I read up somewhere that some standard set for dates (might be ISO) is to write it out as yyyyMMdd. We've had tons of date parsing issues with different servers having different regional settings. Since we changed it to yyyyMMdd we haven't had a single problem... yet.

Yip..YYYY/MM/DD is the big daddy. Never had any issues.

 

[FarligOpptreden]

Yip..YYYY/MM/DD is the big daddy. Never had any issues.

No no no! Not yyyy/MM/dd, but yyyyMMdd (without the "/"). All our date formats were in yyyy/MM/dd and we had loads of trouble. Since removing the "/"-es and just formatting it as yyyyMMdd we haven't had any problems... YET.

EDIT: Here's an interesting article about it:

http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

Throughout this article, you'll notice that I use the ISO dateformat: yyyymmdd. This is a safe dateformat, meaning it should always work, no matter what your specific computer settings are. It is also unaffected by any SET DATEFORMAT or SET LANGUAGE settings.

 

[dequadin]

FarligOpptreden is correct (I seem to be saying that too often )

Scrounged through some bookmarks (XMarks FTW) and found these:
How do I delimit/format dates for database entry?

And yes yyyymmdd is the ISO standard which is totally independent of regional settings.
How to Set the Day/Month/Year Date Format in SQL Server

Date used to me such an issue, with .NET things are a lot easier. I still prefer Acid's solution because it's less likely to be broken by someone "fiddling" in the code, and is a bit more readable. Each to there own...

 

[FarligOpptreden]

FarligOpptreden is correct (I seem to be saying that too often )

Woohoo! Seems like you just passed an ominous post count...