Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date selecting with date from 1. jan 1900

Status
Not open for further replies.

lhg1

IS-IT--Management
Mar 29, 2005
134
DK
Hi

I have a field that is secounds from 1. jan 1900 - And I convertede this to a date, but making selections on this date seems to course problems.

Code:
select alarm_ID, DateAdd(Second, create_date, '19700101') As CreateDate
from WM_MultiCenterIntegration
where CreateDate > '2007-11-07'
order by CreateDate desc

ERROR said:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'CreateDate'.
 
I tryede i like this as well - but then it just returns all rows in the table.

Code:
select alarm_ID, DateAdd(Second, create_date, '19700101') As CreateDate
from WM_MultiCenterIntegration
where DateAdd(Second, create_date, '19700101') > '2007-11-07'
order by CreateDate desc
 
I don't understand exactly what you are trying to do with this SQL statement but I have noticed one thing.

The reference to createdate in the WHERE clause is incorrect. It should be referencing the actual column name in your view or table and not the column alias i.e. create_date instead of createdate

In logical query processing the WHERE clause is processed before the SELECT clause.
The use of the column alias is the ORDER BY clause is allowed because that is processed after the SELECT clause.

Your SQL statement should be as follows

select alarm_ID, DateAdd(Second, create_date, '19700101') As CreateDate
from WM_MultiCenterIntegration
where Create_Date > '2007-11-07'
order by CreateDate desc
 
Hi

The problem is the create_date field - it is an integer with a number of secounds from 1. jan 1900

If I do a normal select i get this

Code:
select alarm_ID, create_date
from WM_MultiCenterIntegration

MC00068668  1093967711
MC00069194  1094047234
MC00069227  1094067911
MC00069300  1094103666
MC00069384  1094128248

The DateAdd(Second, create_date, '19700101') is made to convert it into a regular date.

This also meens using create_date in the where clause is something of a problem, it refers to an integer
 
You can't use the CreatedDate alias in your where statement. Instead, you have to use the DateAdd calculation e.g.
Code:
[COLOR=blue]DECLARE[/color] @WM_MultiCenterIntegration [COLOR=blue]TABLE[/color](alarm_id [COLOR=blue]int[/color], create_date [COLOR=blue]int[/color])

[COLOR=blue]INSERT[/color] @WM_MultiCenterIntegration [COLOR=blue]values[/color] (1, 1000000)
[COLOR=blue]INSERT[/color] @WM_MultiCenterIntegration [COLOR=blue]values[/color] (2, 10000000)
[COLOR=blue]INSERT[/color] @WM_MultiCenterIntegration [COLOR=blue]values[/color] (3, 100000000)
[COLOR=blue]INSERT[/color] @WM_MultiCenterIntegration [COLOR=blue]values[/color] (4, 1000000000)

[COLOR=blue]select[/color] alarm_ID, [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Second[/color], create_date, [COLOR=red]'19700101'[/color]) [COLOR=blue]As[/color] CreateDate
[COLOR=blue]from[/color] @WM_MultiCenterIntegration
[COLOR=blue]where[/color] [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Second[/color], create_date, [COLOR=red]'19700101'[/color]) > [COLOR=red]'20010101'[/color]
[COLOR=blue]order[/color] [COLOR=blue]by[/color] CreateDate [COLOR=#FF00FF]desc[/color]


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]
 
Running the following:

Code:
INSERT INTO @WM_MultiCenterIntegration
SELECT 'MC00068668',  1093967711 UNION ALL
SELECT 'MC00069194',  1094047234 UNION ALL
SELECT 'MC00069227',  1094067911 UNION ALL
SELECT 'MC00069300',  1094103666 UNION ALL
SELECT 'MC00069384',  1094128248 

select alarm_ID, 
       DateAdd(Second, create_date, '19700101') As CreateDate
 from @WM_MultiCenterIntegration
--where DateAdd(Second, create_date, '19700101') > '2007-11-07'
order by CreateDate desc

-- yields 

alarm_ID        CreateDate
---------------- -----------------------
MC00068668      2004-08-31 15:55:11.000
MC00069194      2004-09-01 14:00:34.000
MC00069227      2004-09-01 19:45:11.000
MC00069300      2004-09-02 05:41:06.000
MC00069384      2004-09-02 12:30:48.000


All dates are in the year 2004 i.e. < 2007

 
I'm curious now.

I created a table and populated it with the sample data you provided. When I run your query I don't get any results returned because the dates generated by the dateadd function are from august & september 2004.
This is what I did

>>>>>>>>>>>>>>>>>>>>
create table WM_MultiCenterIntegration (
alarm_id varchar(20),
create_date integer)

insert into WM_MultiCenterIntegration
values('MC00068668' , 1093967711)

insert into WM_MultiCenterIntegration
values('MC00069194' , 1094047234)

insert into WM_MultiCenterIntegration
values('MC00069227' , 1094067911)

insert into WM_MultiCenterIntegration
values('MC00069300' , 1094103666)

insert into WM_MultiCenterIntegration
values('MC00069384' , 1094128248)

select alarm_ID, DateAdd(Second, create_date, '1970-01-01') As CreateDate
from WM_MultiCenterIntegration
where DateAdd(Second, create_date, '1970-01-01') > '2007-11-07'
order by CreateDate desc

NO Results Returned
>>>>>>>>>>>>>>>>>>>>

This query returned

select alarm_ID, DateAdd(Second, create_date, '1970-01-01') As CreateDate
from WM_MultiCenterIntegration

alarm_ID CreateDate
MC00068668 2004-08-31 15:55:11.000
MC00069194 2004-09-01 14:00:34.000
MC00069227 2004-09-01 19:45:11.000
MC00069300 2004-09-02 05:41:06.000
MC00069384 2004-09-02 12:30:48.000
 
Hi

i'm not quite sure I understod what you did. But It lead me in the rigth direction

This seems to work
Code:
select alarm_ID, DateAdd(Second, create_date, '19700101') As CreateDate
from WM_MultiCenterIntegration
where DateAdd(Second, create_date, '19700101') > '20071107'
order by CreateDate desc
 
i'm not quite sure I understod what you did
Look at your where statement compared to mine. You used the CreateDate alias wheras I used the actual DateAdd(Second, create_date, '19700101') function.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top