SQL Server Dates + 'between'

[mike55]

Hi all

 

Heres my problem...In most of my tables i have a date, now this date has to be in the format dd/mm/yyyy...Therefore i am storing the date field as a varchar of length 10, the problem is how can i do a between statement on a table (i.e. between date 1 and date 2) as the statement seems to ignore the year part and simple focuses on the day and month...

 

Any suggestion on how to get around this problem.

 

Mike55

[eramgarden]

if u dont get your answer here, goto http://www.sqlmag.com

 

they're great for sql stuff.

[PlausiblyDamp]

Is there a particular reason why you are storing dates as strings? Date range comparisons are much easier if the date is stored as a date.

[Joe Mamma]

Hi all

 

Heres my problem...In most of my tables i have a date, now this date has to be in the format dd/mm/yyyy...Therefore i am storing the date field as a varchar of length 10, the problem is how can i do a between statement on a table (i.e. between date 1 and date 2) as the statement seems to ignore the year part and simple focuses on the day and month...

 

Any suggestion on how to get around this problem.

 

Mike55

as pd said, store dates as dates. . . database should not worry about format. that occurss at presentation layer and enforced by your localizzation settings.

[mike55]

as pd said' date=' store dates as dates. . . database should not worry about format. that occurss at presentation layer and enforced by your localizzation settings.[/quote']

 

I know it would be easier if i stored the dates as "dates", however when i do a select * on a table with date entries SQL Server returns the date in the format of dd/mm/yy hh:mm:ss, and I need it to be in the format dd/mm/yyyy as the data will be displayed in a crystal report.

 

Mike55

[mike55]

On second thoughts, is there anyway to remove the hh:mm:ss during the select???

[PlausiblyDamp]

If you are using SQL you can always format the returned values using SQL's CONVERT function, or alternatively if you are using them in a .Net application then you should be able to use .Net's formatting features.

[mike55]

If you are using SQL you can always format the returned values using SQL's CONVERT function' date=' or alternatively if you are using them in a .Net application then you should be able to use .Net's formatting features.[/quote']

 

The .net formatting features are out, as the data is been selected straight from one table and inserted into another table without going near .net. Will have a look at the convert function.

 

The first posting relates to the following section of code, when i run the code, it fails to execute... ye mind having a quick look at it and making some suggestions...

 

1. Open connection, working (i know this as i have already opened and closed the connection on a number of occasions and the code is stored in a connect method that can be called from anywhere in the project)

 

2.specify the select statement...

strSelect = select Student.SchoolNum, School.SchoolName, Student.CourseName, Student.MartId, Student.studentID, Student.StudentNum, Student.Forename, Student.Surname, Scan.ScanDate, Scan.Reason

From (Student Inner join scan on student.MartId = Scan.MartId) Inner join school on student.schoolNum = School.schoolNum

Where ((School.MartSchoolID) >= "+SchoolA+" and (School.MartSchoolID) <= "+SchoolB+")
and ((Scan.ScanDate) between "+dateA+" and +"dateB+")
and ((Scan.Reason) =  'Absent' or (Scan.Reason) = 'Suspended')

 

3. Join the select statement with the insert statement and execute the code

 strInsert = "Insert into tableA "
 Execute(strInsert & strSelect)

 

Now when i execute the statement and the check the table i find that the table has no data in it.

 

I have checked the table structure and verified that there is data in the tables i am selecting from.

 

So would appreciate any suggestions.

 

Mike55

[mike55]

Ok I've tried the convert and cast function, and both are basically converting a date "01/01/2004" into the format "Jan 1 2004", but is it possible to cast/convert the data into the format dd/mm/yyyy or dd/mm/yy.

 

Mike55

[PlausiblyDamp]

Try CONVERT(varchar(20), , 103)

[mike55]

Thanks lad, worked perfectly.

 

Mike55