• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 683
  • Last Modified:

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

I have a query similar to this:

SELECT
REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112) + CONVERT(VARCHAR(10), GETDATE(), 108),':','') as datenum
WHERE datenum BETWEEN 20071001094734 AND 20080901094734

Problem is I get the error 'Invalid column name 'datenum'.
If I take the where line out, it works fine.

Can anyone get this wo work with the where clause included?
Could anyone change it so that he value of datenum is numeric? I get errors when i put it into another convert statement.
The format of the date and time is important so cant be changed.

Thanks,
kinnon_2000
0
kinnon_2000
Asked:
kinnon_2000
  • 3
  • 2
  • 2
1 Solution
 
Jai STech ArchCommented:
SELECT * FROM (SELECT
REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112) + CONVERT(VARCHAR(10), GETDATE(), 108),':','') as dd) as datenum
WHERE datenum.dd BETWEEN 20071001094734 AND 20080901094734
0
 
Jai STech ArchCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
kinnon_2000Author Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Get date has been unreliable and quirky for me
????

in how far?
also, are you aware of GETUTCDATE() function?
0
 
kinnon_2000Author Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that ORDER BY and WHERE can work with DATETIME properly, without any conversion at all!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now