Select Starttime

Question

Post reply

Select Starttime

kjkeyan

SSC-Addicted

Points: 451
More actions
April 15, 2010 at 7:22 am

#217879

Hi All,
Am trying to retrieve Start time From a table but the criteria is
i need to retrieve data between certain timing,The StartTime datatype is nvarchar and its in the format like '12:30 AM'
The query is :
SELECT
StartTime
FROM
TicketTransaction
WHERE
StartTime >= '12:30 PM'
AND StartTime <= '05:30 PM'

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

shen-dest SSCrazy Points: 2498 More actions · Answer 1

should not query be like

SELECT

StartTime

FROM

TicketTransaction

WHERE

StartTime <= '12:30 PM'

AND StartTime >= '05:30 PM'

Regards,
MShenel

kjkeyan SSC-Addicted Points: 451 More actions · Answer 2

Thanks Shenel its executing the output but not exactly the record i need

for eg:

SELECT

StartTime

FROM

TicketTransaction

WHERE

StartTime <= '12:30 PM'

AND StartTime >= '05:30 PM'

The Output like :

05:45 PM

06:16 PM

06:23 PM

12:23 PM

10:21 AM

06:15 PM

06:24 PM

06:36 PM

06:49 PM

But i need a out put between 12:30 pm and 05:30 pm

Regards

kjkeyan

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

Try:

SELECT StartTime

FROM TicketTransaction

WHERE

convert(datetime,StartTime) <= '12:30 PM'

AND

convert(datetime,StartTime) >= '05:30 PM'

You should also consider permanently changing the datatype of this column to datetime, or perhaps time.

/SG

Paul White SSC Guru Points: 150467 More actions · Answer 4

Stefan_G (4/17/2010)
You should also consider permanently changing the datatype of this column to datetime, or perhaps time.

Absolutely, yes. Problems related to storing dates and times as something other than a true date/time type come up again and again on these forums. I have yet to encounter a satisfactory reason to store date/time data like that - it just makes life harder, to no advantage.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kjkeyan SSC-Addicted Points: 451 More actions · Answer 5

HI,

Thanks for your suggestion ,but i have tried this query

SELECT StartTime

FROM TicketTransaction

WHERE

convert(datetime,StartTime) <= '12:30 PM'

AND

convert(datetime,StartTime) >= '05:30 PM'

Its not working and i am getting an error

'Conversion failed when converting datetime from character string'

And i have tried this query

SELECT convert( datetime,StartTime,109)

FROM TicketTransaction

WHERE

StartTime <= '12:30 PM'

AND

StartTime >= '05:30 PM'

Its giving the output but not exact record .

Thanks and regards

kjkeyan

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 6

Hi,

Try the below script. But changing datatype is the best option what Stefan and Paul White’s suggested. Otherwise you need to do lot of date conversions to get the result.

SELECT StartTime

FROM(SELECT StartTime, CAST(CONVERT(NVARCHAR(20), GETDATE(), 23) + ' ' + StartTime AS DATETIME) ConvertedStartTime

FROM TicketTransaction) AS T1

WHERE

CONVERT(NVARCHAR(20), ConvertedStartTime, 8) <= CONVERT(NVARCHAR(20), '12:30 PM', 8)

AND

CONVERT(NVARCHAR(20), ConvertedStartTime, 8) >= CONVERT(NVARCHAR(20), '05:30 PM', 8)

_____________________________________________
One ounce of practice is more important than tonnes of dreams

Stefan_G SSCertifiable Points: 6609 More actions · Answer 7

kjkeyan (4/18/2010)
HI,
Thanks for your suggestion ,but i have tried this query
SELECT StartTime
FROM TicketTransaction
WHERE
convert(datetime,StartTime) <= '12:30 PM'
AND
convert(datetime,StartTime) >= '05:30 PM'
Its not working and i am getting an error
'Conversion failed when converting datetime from character string'
And i have tried this query
SELECT convert(datetime, StartTime, 109)
FROM TicketTransaction
WHERE
StartTime <= '12:30 PM'
AND
StartTime >= '05:30 PM'
Its giving the output but not exact record .

It sounds like you have data in the table that is not in a valid format.

Try using

SELECT MAX(convert(datetime, StartTime, 109))

FROM TicketTransaction

and see if you get any error.

If you get an error, you definitely have bad data in the table, and you should be able to find the offending row(s) using this query:

SELECT *

FROM TicketTransaction

WHERE ISDATE(StartTime) = 0

After fixing the bad data in the table, my original query should work.

/SG