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.
| Author |
Topic |
|
insolitude
Starting Member
3 Posts |
 Posted - 2009-01-28 : 13:26:58
|
| New to SQL, trying to figure out how to work with a date field (varchar,50) formatted as M/D/YYYY. For example:SELECT * FROM table1 WHERE date1 < '3/15/2009'This will pull 3/1, 3/10-14.SELECT * FROM table1 WHERE date1 < '3/31/2009'This will pull 3/1-3, 3/10-30.Is there a way to select BEFORE, BETWEEN, AFTER based on this field? TIA! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-01-28 : 13:57:25
|
a very important database concept is to store data in appropriate datatypes. Your date1 column should be [datetime] or [smalldatetime]. Assuming ALL your date1 values are formatted correctly and consistently you could:set dateformat 'mdy'SELECT * FROM table1 WHERE where convert(datetime, date1) < '2009-03-15' Be One with the OptimizerTG |
 |
|
|
insolitude
Starting Member
3 Posts |
Posted - 2009-01-28 : 14:17:59
|
| Thanks for the reply. I am going back to my developer to get the date field changed -- earlier I had it changed because I need M/D/YYYY format in my CSV, but now I realize I can reformat to suit my needs.All date1 values are formatted as M/D/YYYY (no leading zeros). I ran what you suggested and got:#1193 - Unknown system variable 'dateformat' |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-01-28 : 14:34:24
|
| Are you using MS SqlServer? Because that is the technology this forum is for...Be One with the OptimizerTG |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-01-28 : 14:35:53
|
| Are you using MySQL or some other type of database management system?This site is for questions about Microsoft SQL Server.CODO ERGO SUM |
|
|
|
insolitude
Starting Member
3 Posts |
Posted - 2009-01-28 : 15:42:56
|
| My apologies to all, I guess I'm using MYSQL. I'm off to look for the right forum... |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-01-29 : 00:32:13
|
quote:
Originally posted by insolitude
My apologies to all, I guess I'm using MYSQL. I'm off to look for the right forum...
post it in www.dbforums.com--> mysql |
|
|
|
|
|
|
Select before M/D/YYYY