Format of Date in Select Statement from ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Format of Date in Select Statement from ASP

Author

Topic

adjones1980
Starting Member

36 Posts

Posted - 2007-10-19 : 05:39:38

Hello,

I have a Select statement in my ASP application that calls SQL Server 2000 to retrieve some records that match the given date.

Here is an example of what the ASP site would send to SQL...

SELECT dCount FROM tbl_ExpFactors WHERE dCount = '2005-3-18'

In my ASP app this DOES NOT return any rows, however if I run this same statement in SQL Analyzer it DOES return the expected rows. Is this down to my formatting of the date? If so, why does it work in analyzer?

I am in the UK which is why I am using this international formatting.

adjones1980
Starting Member

36 Posts

Posted - 2007-10-19 : 06:31:49

Well, I didn't get find the answer but I found that using ....dCount = '" & theDate.ToString & "'"... provides you with a usable date format.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-19 : 07:20:54

theDate.ToString("put your format here") also works

some formats
theDate.ToString("yyyy-mm-dd")
theDate.ToString("dd.mm.yyyy")
theDate.ToString("dd/mm/yyyy")
theDate.ToString("mm/dd/yyyy")
etc...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-10-19 : 08:11:50

You could also create a stored procedure and pass a date parameter to it

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-19 : 09:13:40

You should never concatenate any variables like that. Even without a stored proc, you should ALWAYS use parameters. Always. Then, formatting is never a concern, as well as escaping and delimiting and other issues; you are always passing things by VALUE.

see: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-19 : 09:16:00

ohhh..... i udmderstood this as being the vice versa problem!!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources