Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to write a Microsoft SQL Server stored procedure which takes an account ID, and returns an authentication ticket and whether the ticket is expired.

Something like this:

SELECT Authentication.ticket, Authentication.TicketExpires < getDate()
FROM Authentication WHERE Authentication.accountID = @id

The purpose is to return a row with two columns - the ticket, and a bit field (0 or 1) telling me whether the ticket is expired. This bit field isn't part of the table, the ticketExpires column is SMALLDATETIME.

Does anyone know the correct syntax for this?

share|improve this question

migrated from serverfault.com Aug 28 '12 at 5:16

This question came from our site for professional system and network administrators.

    
I'm not 100% sure what you mean by "return a BIT" field - Your query (assuming it's syntactically correct) should be generating a boolean value (True or False) for that date comparison. What is the SQL server returning? What are you expecting? –  voretaq7 Feb 23 '12 at 17:10

3 Answers 3

Most database systems have built in functions for things like this. In MySQL, you would use NOW() for example. A quick Google for "Date and time functions MS SQL Server" got me to a page that says the function GETDATE() will return a DATETIME of the current system timestamp. I'm not sure if you can compare a SMALLDATETIME to a DATETIME, though.

Reference: http://msdn.microsoft.com/en-us/library/ms188383.aspx

share|improve this answer
    
Thanks - I've found the date functions, but I don't know how to return a BIT field which is generated as part of the query. –  James Feb 23 '12 at 14:06
    
Ah, sorry, I misunderstood your question. That's a bit more complicated, and beyond my SQL ability. :-) –  Kyle Smith Feb 23 '12 at 16:13

Is the information on this page any use?

It looks like you'll have to write something like the following:

select  cast(datediff(s,Authentication.TicketExpires,getdate()) as bit) from ...

This will return a 1 if the ticket expiry date is in the future, and a 0 if it is exactly now or in the past.

share|improve this answer

I think this achieves what you're looking for. Hope this helps.

 SELECT
      Authentication.Ticket
      , CAST(CASE
           WHEN Authentication.TicketExpires < GetDate() THEN 1
           ELSE 0
      END AS bit) AS 'IsExpired'
 FROM Authentication
 WHERE Authentication.accountID = @id
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.