1. GETDATE() problem with smalldatetime (where mydate = getdate()?)
Hi.
I am very new to SQL Server so please bear with me. I am sure it is
version 7.0 though (oh lordie I hear you call cry!).
Anyway...here are my difficulties: I imported a lot of data from
Access to SQL, using the SQL import thing in Enterprise Manager. One
of my tables relies strongly on, what was, a Date/Time field in Access
which I now notice is a smalldatetime field.
The dates in this field are displayed, in Enterprise Manager anyway,
as dd/mm/yyyy although, through tinkering, I noticed that they are
actually stored in the database as yyyy-mm-dd. (I'm presuming they are
displayed as dd/mm/yyyy according to my regional settings?)
Ok, so I was trying to create a web page that would display any item
that had "today" as the date (changing dynamically as each day passes
of course). I used to do this in VBScript / ASP using the line:
SELECT * FROM tablename WHERE datefield = Date()
(or was it Now() - oh crikey!)
Anyway, it used to work fine like that. In moving to SQL I decided
that I would just create a view that already ran this query, and then
just have my page say SELECT * FROM todaysitemstable. This, I felt,
would be the most efficient way of presenting this data (all advice
greatly appreciated!).
I chased up the manual, online support etc and discovered that I
needed to use GETDATE() when trying this same thing in SQL. I
couldn't (can't!) get this to work though because, I THINK, the
smalldatetime type saves "to the minute", and yet my old data was
simply dd/mm/yyyy. The upshot being that when I asked for matches for
GetDate() it was saying to me "there are no items that match
yyyy-mm-dd 00:00" (yes, I know, computers can't talk to you!).
Further investigation, and I tried converting mydate and getdate into
varchar types (varchar(10)) like so:
SELECT *
FROM dbo.tablename
WHERE (CONVERT(varchar(10), mydate, 101) = CONVERT(varchar(10),
GETDATE(), 101))
Bingo!! It worked and I was well chuffed. However...I also now need to
add two new items of functionality:
I want to query my table to show me dates that are equal to or greater
than GETDATE(). Tried the logical thing which was to change the = in
the above statement to a >= but it doesn't work. And I think I know
why, I just don't know what to do now!?
I am presuming that, because I am converting to a varchar value that
this changes the data to a string - dd/mm/yyyy (eg. 21/03/2003). So
trying to perform a mathematical command on a string is pretty much
useless? Is that why it isn't working? Can someone help in terms of
letting me know what I need to do?
Also, I would like to show items that occur, or have occured in the
current week which, in my mind, sounds complicated.
All help greatly appreciated. Hope to hear from someone...
Regards
Nathon
2. Preventing Auto-update of Diagrams
3. newbie: subquery error: want to make more readable, get error (msg 116)
4. Record not in index?
5. Correlated Subqueries in 6.5 and 7.0
6. Data or Database Replication tools
7. Bug with division/subqueries in SQL SERVER 7.0 Sp3
8. TechTips: How to provide "Cancel" and/or "Undo"
9. Incorrect Subquery Results between SQL 6.5 and 7.0
10. correlated subqueries and general subqueries.
11. Running subqueries inside subqueries...
12. Subqueries and Table Aliases and Subquery execution
13. Subquery in Subquery
by