Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 5th, 2003, 07:59 PM
Authorized User
 
Join Date: Jun 2003
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default Comparing Dates in SQL Server 7

Hi guyz am having problems comparing dates in SQL 7 and i don't know how i could fix this.

Scenario 1:
Select *
From tblOrderRecords
Where fldDate >= '01/01/2003' And fldDate <= '03/01/2003'

NOTE:: the date is btwn '01/01/2003' and '03/01/2003'
This statement gives me records but there is records of the next month. Sample result set is below
fldDate fldPhones
---------- ---------
2003-02-02 00:00:00 10
2003-01-02 00:00:00 34
2003-01-09 00:00:00 10

(3 row(s) affected)
Seriously the record dated 2003-02-02 00:00:00 should not be there

Scenario 2:
Select *
From tblOrderRecords
Where fldDate >= '01/01/2003' And fldDate <= '30/01/2003'

NOTE:: the date is btwn '01/01/2003' and '30/01/2003'
This gives me the error below
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Please somebody help coz it's realy makin me angry!!.. just explain why it's not giving me the right data and probably a solution.

Scenario 3:

I am designing a front end program using VB6 however am forced to edit dates from something like this 01/01/2003 to a string "2003-01-01" by getting parts of the date and concatenating them up to a string in order to get the dates otherwise it gives me an error.

Please help real time coz am very desparate!!




LION OF JUDDAH!
__________________
LION OF JUDDAH!
 
Old August 5th, 2003, 08:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use Ansi dates (yyyy-mm-dd) and deal with the fact that it is annoying. There are issues when trying to use real international dates with databases etc. Basically you can't totally fix the problems, so get to know and love ansi dates.

BTW Judah has only one D.

regards
David Cameron
 
Old August 6th, 2003, 12:45 AM
Authorized User
 
Join Date: Jun 2003
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Guyz i have jus noticed that instead of what retrieving data with date format(dd/MM/yyyy) the database engine uses the format (MM/dd/yyyy) that's why i get the error above if date is 30/01/2003.
is there away to i can change the date format for a database only and not the whole server.

   hey thanks for the reply (Cameron!!). But the main point of the tests i was doing as demontrated b4 was to be used in my client program VB6!!.. i have tried changing the date format but the date ends up having the system format (dd/MM/YYYY in Australia).

This goes out to David Cameron..
You betta recognize.. "LION OF JUDDAH" SOO WHAT??? where u at in Australia Anyway (Am in Melbourne)


LION OF JUDDAH!
 
Old August 6th, 2003, 12:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Look into CONVERT, see SQL books online for details. The way the database stores dates has no relation to how they are displayed. You can use convert to change the format to something that suits you better.

What is the JUDDAH thing? Some uber l337 net meme I've missed out on? Or just a deliberate misspelling?

I'm in Sydney.

regards
David Cameron
 
Old August 6th, 2003, 01:51 AM
Authorized User
 
Join Date: Jun 2003
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Am gonna stress this again while still asking for more ideas. I would like to use the date format(dd/MM/yyyy) in my client program so that i can change it at run time and it's also what the user understands. Note:: the SQL Statement is processed in vb6 and not the databse SO using CONVERT function in a stored procedure is posibble but it won't be posibble in VB6. if i use FormatDateTime function it only allows defined types.

Any more ideas please!!

To Cameron!!
tehehehehehe...stay away ma behind (cameron)....thanks alot though.. u been realy helpful.. have fun in a TOWN like sydney. Welcome to "Melbourne the place to be". tehehehehe!!

LION OF JUDDAH!
 
Old August 6th, 2003, 01:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use dd/mm/yy when you display dates to users, but convert this to yyyy-mm-dd when you have to do any work with the database. Use a string formatting function to convert from dd/mm/yy to a database recognisable format. I've got a pair of VBScript function to do just this, should be relatively easy to convert to VB. Call AustToANSIDate with a date.

Code:
Function AustToANSIDate(ByVal dtmDate)

    Dim Day, Month, Year

    If Trim(dtmDate) = "" Then 
        AustToUSDate = ""
        Exit Function
    End If

    dtmDate = FormatDate(dtmDate)
    Day = Left(dtmDate,2)
    Month = Left(right(dtmDate, 7), 2)
    Year = right(dtmDate, 4)
    AustToANSIDate= Year & "/" & Month & "/" & Day

End Function

Function FormatDate(ByVal dtmDate)

    If InStr(dtmDate, "/") = "2" Then
        dtmDate = "0" & dtmDate
    End If

    If InStr(4, dtmDate, "/") = 5 Then
        dtmDate = Left(dtmDate, 3) & "0" & Right(dtmDate, Len(dtmDate) - 3)

    ElseIf InStr(4, dtmDate, "/") = 0 Then

        If Len(dtmDate) = 4 Then
            dtmDate = Left(dtmDate, 3) & "0" & Right(dtmDate, Len(dtmDate) - 3)
        End If

        dtmDate = dtmDate & "/" & Year(Now())

    End If

    If Len(dtmDate) = 8 Then
        dtmDate = Left(dtmDate, 6) & Left(Year(Now()), 2) & Right(dtmDate, 2)
    End If

    FormatDate = dtmDate

End Function


regards
David Cameron
 
Old August 6th, 2003, 02:38 AM
Authorized User
 
Join Date: Jun 2003
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks buddy.. i had the same thing kindda code en it was working fine here but when i tried testing it in the clients SQL 7 it started giving me errors with the dates.

en here i am looking for another idea!!..

LION OF JUDDAH!
 
Old August 6th, 2003, 02:53 AM
Authorized User
 
Join Date: Jun 2003
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
Default

is there any reason why my SQL 7 would compare dates using this format MM/dd/yyyy en not dd/MM/yyyy.

sample result: instead of a statement like below in VB6 (don't worry about connections) returning only data between the 1st to 3rd of January it returns data from 1st January - 1st of March.
Select *
From tblOrderRecords
Where fldDate >= '01/01/2003' And fldDate <= '03/01/2003'

that's my problem.
Am looking for an explanation en probably a solution.
heeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeellllllllllllll lllP!!!!!!!!!



LION OF JUDDAH!
 
Old August 6th, 2003, 06:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've already said to use ANSI date format. If you use dd/mm/yyyy it will read this in as mm/dd/yyyy. This could result in a conversion error if the day is higher than 12.

I'll say it again. Use ANSI dates.

Quote:
quote:Thanks buddy.. i had the same thing kindda code en it was working fine here but when i tried testing it in the clients SQL 7 it started giving me errors with the dates.
I've never had any problems with that function I posted, and it has been in use for a couple of years.

Quote:
quote:Am looking for an explanation en probably a solution.
You have had an explanation and a solution.

regards
David Cameron





Similar Threads
Thread Thread Starter Forum Replies Last Post
comparing date column with sql server mallikalapati ASP.NET 2.0 Professional 3 March 12th, 2008 06:06 PM
comparing dates help. warhero XSLT 1 July 5th, 2007 09:41 AM
Comparing dates Tomi XSLT 1 September 21st, 2006 04:45 AM
Comparing Dates bennybee JSP Basics 1 April 3rd, 2005 09:31 PM
Help comparing dates Dave Brown Beginning PHP 3 December 20th, 2004 04:03 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.