Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime

Transact-SQL

WHats wrong


Hi !

I am new with SQL and TSQL

I want to collect rows between two a start time and a end time.

The time is in a textstring, like filename_wast_erth1_20070506_1812_all.txt

I cannot use any other time in the database when i collect data!

The database looks like this (almost anyway)

FILENAME nvarchar(255) TIME

filename_wast_erth1_20070506_1812_all.txt 2007-01-16 10:36:15

filename_wast_erth1_20070506_1812_all.txt 2007-01-16 10:36:20

filename_wast_erth1_20070506_1813_all.txt 2007-01-16 10:36:25

filename_wast_erth1_20070506_1815_all.txt 2007-01-16 10:36:35

filename_wast_erth1_20070506_1815_all.txt 2007-01-16 10:36:45

filename_wast_erth1_20070506_1818_all.txt 2007-01-16 10:37:05

I trying to do like this:

SELECT ,

@theState = CONVERT(char(20),DATE,20),

@id = ID,

@temp11filename = SUBSTRING(FILENAME, 21,4)+ '-'+ SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+ SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2)+':00',

@filename = FILENAME

FROM DELIVERY

WHERE @temp11filename BETWEEN @theEndTime AND @theCurTime

ORDER BY ID DESC;

END

This result in no data

Anyone with a idea how to do it



Top

Re: Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime

Manivannan.D.Sekaran


As per your select query it will assign the last record value on the @theState, @id, @temp1Filename & @filename varibale..

use the following query to get the all result..

Code Snippet

Select

@TheState =TheState , @ID=ID, @Temp11filename =Temp11filename ,@Filename =FileName

From

(

SELECT

TheState = CONVERT(char(20),DATE,20),

ID= ID,

Temp11filename = Cast(SUBSTRING(FILENAME, 21,4)+ '-'+

SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+

SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2)

+':00' as DateTime),

FileName = FILENAME

FROM DELIVERY

) as Data

WHERE Temp11filename BETWEEN @theEndTime AND @theCurTime

ORDER BY ID DESC;






Top

Re: Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime

clintz

hi,
on your where clause, @theEndTime must be lessthan @theCurTime..
and your query will not return any result because it's assigning value to variables..

try,

SELECT

CONVERT(char(20),DATE,20),

ID,

SUBSTRING(FILENAME, 21,4)+ '-'+ SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+ SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2)+':00',

FILENAME

FROM DELIVERY

WHERE @temp11filename BETWEEN @theEndTime AND @theCurTime

ORDER BY ID DESC;

END

- clintz





Top

Re: Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime

Marreoragarn

But I want to have the substrings from FILENAME into the variable @temp11filename.

I want to mask out 2007-05-06 18:18:00 from filename_wast_erth1_20070506_1818_all.txt.

2007-05-06 18:18:00 is the date I want to use as argument when getting the rows.

@temp11filename = SUBSTRING(FILENAME, 21,4)+ '-'+ SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+ SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2)+':00',

Here @temp11filename is 2007-05-06 18:18:00

I want to use @temp11filename to find out and then get all rows between @theEndTime AND @theCurTime

Am I thinking wrong




Top

Re: Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime

Manivannan.D.Sekaran

The above (my query) does the same..

Here you have to use the derived tables. You can't do this task like other programming languages..

Check my query & let me know..





Top

Re: Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime

Marreoragarn

It works. I did like this!

SELECT @state = STATE,

@theState = CONVERT(char(20),STATE_TIMESTAMP,20),

@id = ID,

@filename = FILENAME

FROM DELIVERY

WHERE SUBSTRING(FILENAME, 21,4)+ '-'+ SUBSTRING(FILENAME, 25,2)+'-'+SUBSTRING(FILENAME, 27,2)+' '+ SUBSTRING(FILENAME, 30,2)+ ':'+ SUBSTRING(FILENAME, 32,2) between @theEndTime and @theCurTime

ORDER BY ID DESC;

END

I want to use SUBSTRING but start count from the end of the string. Anyone know howto




Top

Re: Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime

Arnie Rowland

I want to use SUBSTRING but start count from the end of the string. Anyone know howto

Use the REVERSE() function.





Top

Re: Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime

Marreoragarn

Thanks!


Top