Date selecting with date from 1. jan 1900

[lhg1]

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.

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

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'CreateDate'.

 

[lhg1]

I tryede i like this as well - but then it just returns all rows in the table.

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

 

[Bubbles2610]

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

 

[lhg1]

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

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

 

[ca8msm]

You can't use the CreatedDate alias in your where statement. Instead, you have to use the DateAdd calculation e.g.

[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]

 

[PerlyGates]

Running the following:

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

 

[Bubbles2610]

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

 

[lhg1]

Hi

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

This seems to work

select alarm_ID, DateAdd(Second, create_date, '19700101') As CreateDate
from WM_MultiCenterIntegration
where DateAdd(Second, create_date, '19700101') > '20071107'
order by CreateDate desc

 

[ca8msm]

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]