datetime selection format - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

datetime selection format

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2007-11-19 : 06:03:13

Hi there,

I have an SQL Server 2005 database with an ASP front end. A number of the queries used involve selecting rows depending on a datetime value specified by the user.

The date format I'm using is DD/MM/YYYY. This is the format of the dates in the table and the format of the date variable inserted into the query, yet for some reason it will not return the correct rows.

Here's what's happening....

If the user sets the start/end dates to 01/01/2007 and 03/01/2007, I can get rows containing dates like this:

22/01/2007
16/02/2007
28/02/2007
11/03/2007
30/03/2007

....so instead of returning rows with dates from the first 3 days of January, it's returning rows from the first 3 months of the year, regardless of the day value.

I have used session.lcid=2057 in the code to specify UK date values and all the dates in the database are set in this format.

It all worked fine until I made a backup of the database & frontend and transferred them onto a laptop running SQL Server locally for a demonstration. Perhaps there is something that needs to be set on the new SQL Server?!

Hope this makes sense?!?!?!

Thanks!

Gyto
Posting Yak Master

144 Posts

Posted - 2007-11-19 : 06:12:45

I have just noticed the Server's language setting is English(United States)...would this make any difference? Only problem is I'm having to use SQL Server Management Studio Express and I don't know how to change it! lol

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-19 : 06:28:34

Check SET DATEFORMAT settings (DMY, MDY, ...)

Gyto
Posting Yak Master

144 Posts

Posted - 2007-11-19 : 06:38:59

Perfect....just added SET DATEFORMAT dmy; to the query and it works fine :)

Thanks very much!

Gyto
Posting Yak Master

144 Posts

Posted - 2007-11-19 : 07:03:05

Actually one more question....the query I added it to was embedded in the ASP code....is there any way I can add it to the following stored procedure:



CREATE PROCEDURE spProjectDetailReport
(
@ProjectType Int,
@DateFrom datetime,
@DateTo datetime
)

AS


INSERT INTO tProjectDetailReportData (ProjDesc, ProjAnticCompDate, ProjManager, ProjSponsor, ProjType, ProjDept)
SELECT tProject.ProjDesc, tProject.ProjAnticCompDate, tProjectManager.ProjectManagerName, tProjectSponsor.ProjectSponsorName, tProjectType.ProjectType, tDepartment.Department 
FROM (((tDepartment RIGHT JOIN tProject ON tDepartment.ProjectDeptID = tProject.ProjDept) LEFT JOIN tProjectManager ON tProject.ProjManagerID = tProjectManager.ProjectManagerID) LEFT JOIN tProjectSponsor ON tProject.ProjSponsorID = tProjectSponsor.ProjectSponsorID) LEFT JOIN tProjectType ON tProject.ProjTypeID = tProjectType.ProjectTypeID 
WHERE (((tProjectType.ProjectTypeID) = @ProjectType OR @ProjectType = 0)) 
AND (((tProject.ProjAnticCompDate) >= @DateFrom OR @DateFrom = '01/01/1970') 
AND ((tProject.ProjAnticCompDate) <= @DateTo  OR @DateTo = '01/01/1970') AND tProject.ProjAnticCompDate IS NOT NULL)

GO

I'm not sure where it needs to be (assuming it can be done?!)....I tried adding it in after the 'AS' but it had no effect....cheers

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-19 : 07:59:53

1. Always write date constant in the format YYYYMMDD, in that case your procedure woould not be 'dateforat'-sensitive:
... @DateFrom = '19700101' ...
2. To represent 'dont filter by date'; I would rather use NULL value
3. If ProjAnticCompDate is indexed I would rewrite this query so the index can be used in some cases
4. I did not understand your last question. How do you want to filter your data?
You want to display it for only 3 days of each month?
Add something like
AND DATEPART(dd,tProject.ProjAnticCompDate)<=3

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-19 : 09:32:11

The answer is simple: do not do ANY formatting at all!

You have a stored proc, with datetime parameters, that is beautiful, you are doing great there and much better off than 99% of the other programmers out there who just concatenate things into strings and execute them. Bravo on that, and because of that you've made things much easier.

Now, in ASP, do things the same way: pass actual Date values to as your parameters, do not pass string values! Use the date functions in ASP -- DateSerial, CDate(), and so on -- and be sure that in ASP you have valid DATE values in the scope of your ASP code. If you have user input that you need to convert from a specific format to a date, you do in in ASP. If you have Month/Year/Day drop-down lists and you are building a date from them, use DateSerial(), and so on. The key is, within your ASP code, you should first have valid dates stored in variables of the proper type. (VBScript does have types, they are just sort of hidden). Then, you simply pass those date variables to your parameters in your SQL Commands and now you are simply passing date VALUES directly to SQL server, and no server formats will ever be an issue.

The key is, this is not a SQL issue, but an ASP issue, if you do things right.

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

Gyto
Posting Yak Master

144 Posts

Posted - 2007-11-19 : 10:22:31

Thanks jsmith8858, that's really useful....and thanks for your thoughts too evilDBA. I have actually figured out a workaround for this for now because this particular version is only going to be used for a demonstration in one imminent meeting!! haha....but I think I may well implement some of your suggestions in the live version :) Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources