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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-24 : 08:55:53
|
| Ken writes "I am trying to retriev Data from a field that is a DateTime datatype. Within the field itself here is what the date looks like......6/25/1972. In the Query Analyzer I am unable to retrieve this particular data using this type of query......Select DateLeadGenerated from MVCI_LeadTracking_Table Where DateLeadGenerated < 5/25/2004I get all the field names with no data.When I change the < to a > it gives me this data......Select DateLeadGenerated from MVCI_LeadTracking_Table Where DateLeadGenerated > 5/25/2004DateLeadGenerated --------------------------- 1972-06-25 00:00:00.0001972-06-25 00:00:00.000(2 row(s) affected)This does not make sense to me. For one that 1972-06-25 is not > 5/25/2004 and in the table the date format looks like this 6/25/1972.Thank You Ken" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-24 : 09:10:26
|
| Select DateLeadGenerated from MVCI_LeadTracking_Table Where DateLeadGenerated < '5/25/2004'<O>Edited by - Page47 on 07/24/2002 09:10:40 |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-07-24 : 09:13:52
|
| dates are 'funny' in SQL server....they need extra care...compared to other types of variables....1. dates can be displayed in many formats...DMY, MDY, YMD....etc likewise dates input can be 'interpreted' differently....depending on your system/client defaults....2. it's best to avoid intrepretation altogether....by explicitly setting the format you want dates read/display under....look at the SET DATEFORMAT DMY (MDY, YMD) command....(search here for other example/advice)3. date's also benefit from quotes wrapped around them....You see 6/25/1972....and intrepret it as June 25, 1972...SQL Server sees 6/25/1972 and just sees a sequence of numbers and another character.... |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2002-07-24 : 15:51:49
|
| select (5/25/2004) = 0 -- as integer value and evaluating to the date '6/25/1972' IS greater than zeroneed quotes around date in where clause like page47 said |
|
|
|
|
|
|
Retrieving Data from a field that is a DateTime datatype