SQL SERVER 2005 Express, Small query issue selecting numeric date yyyymmddhhmmss

this will also work (just changed the col names)
SELECT * FROM (SELECT
REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112) + CONVERT(VARCHAR(10), GETDATE(), 108),':','') as datenum) as returnparam
WHERE returnparam.datenum BETWEEN 20071001094734 AND 20080901094734

you cannot use the datenum directly in sql server.

now, I don't see a sense in the query as it is posted, I assume that you don't use getdate(), but a column from a table?

if not:

SELECT
REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112) + CONVERT(VARCHAR(10), GETDATE(), 108),':','') as datenum
WHERE getdate() >= convert(datetime,  '2007-10-01 09:47:34', 120)  
AND getdate() <= convert(datetime,  '2008-09-01 09:47:34', 120)

Yup, using columns fromm a table, not getdate. Get date has been unreliable and quirky for me and my business in tha past, so we stick to the format defined. Its reliable to search by, and order by. It is easy to parse, format, and adjust for timezones. The code is php based, so the WHERE values are created and inserted into the query string.

Thanks for your help folks.

>Get date has been unreliable and quirky for me
????

in how far?
also, are you aware of GETUTCDATE() function?

Nope, never heard of it. Sounds usefull. All our servers are set to UTC, whch is same as gmt wihout the daylight saving setting, so machine date will be utc.

One of the biggest headaches weve had with SQL server datetime was ordering for web based reports. Large reports ordered by datetime have time and again failed to order correctly, even for simple select queries where there are large amounts of data. This was resolved by fetching all datetime fields as above. now the data orders perfectly. It may well be we were doing something wrong, however, not being fans of the date_time format as a business we find this a far cleaner way to deal with dates and times programatically. In the past I used a funtion which loked like this:

FUNCTION [dbo].[OrdDate]
  (@fromdate DATETIME)
 RETURNS numeric(16)
 AS
 BEGIN
 DECLARE @newdate varchar(16)
 DECLARE @yy VARCHAR(4)
 SET @yy= CAST(DATEPART(yy,@fromdate) AS VARCHAR(4))
 DECLARE @mm VARCHAR(4)
 SET @mm= CAST(DATEPART(mm,@fromdate) AS VARCHAR(4))
 DECLARE @dd VARCHAR(4)
 SET @dd= CAST(DATEPART(dd,@fromdate) AS VARCHAR(4))
 DECLARE @hh VARCHAR(2)
 SET @hh= CAST(DATEPART(hh,@fromdate) AS VARCHAR(2))
 DECLARE @mi VARCHAR(2)
 SET @mi= CAST(DATEPART(mi,@fromdate) AS VARCHAR(2))
 DECLARE @ss VARCHAR(2)
 SET @ss= CAST(DATEPART(ss,@fromdate) AS VARCHAR(2))
 DECLARE @ms VARCHAR(3)
 SET @ms= CAST(DATEPART(ms,@fromdate) AS VARCHAR(3))

 -- pad out the 2 digit numbers if they are less than 10
 if CAST(@mm AS NUMERIC(2)) <10      SET @mm='0'+@mm      ;
 if CAST(@dd AS NUMERIC(2)) <10      SET @dd='0'+@dd
 if CAST(@hh AS NUMERIC(2))<10      SET @hh ='0'+@hh
 if CAST(@mi AS NUMERIC(2))<10      SET @mi='0'+@mi
 if CAST(@ss AS NUMERIC(2))<10      SET @ss='0'+@ss
 if CAST(@ms AS NUMERIC(3))<100      SET @ms='0'+@ms
 if CAST(@ms AS NUMERIC(3))<10      SET @ms='0'+@ms      
 --now build the numeric value to be returned
 SET @newdate=@yy+@mm+@dd+@hh+@mi+@ss+@ms
 return CAST(@newdate AS NUMERIC(16))
END

However, the method described in this thead above is much more efficient.

note that ORDER BY and WHERE can work with DATETIME properly, without any conversion at all!!