Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

Logical expressions in sql that will return a bit value??

I have the following table:

CREATE TABLE [dbo].[tblGiftCertificates] (
      [id] [int] IDENTITY (1, 1) NOT NULL ,
      [publishDate] [smalldatetime] NOT NULL ,
      [expiryDate] [smalldatetime] NOT NULL ,
      [name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [description] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [price] [money] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblGiftCertificates] ADD
      CONSTRAINT [DF_tblGiftCertificates_publishDate] DEFAULT (getdate()) FOR [publishDate],
      CONSTRAINT [DF_tblGiftCertificates_expiryDate] DEFAULT (getdate() + 1) FOR [expiryDate],
      CONSTRAINT [PK_tblGiftCertificates] PRIMARY KEY  CLUSTERED
      (
            [id]
      )  ON [PRIMARY]
GO

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

Is there any way to return a bit value in sql that will represent whether the expiryDate has passed.
Something like:

  isExpired = (expiryDate > GETDATE() )

Cheers.
0
smacca
Asked:
smacca
1 Solution
 
ZylochCommented:
Hi

I believe you can use DateDiff for that. Something like

DateDiff('d',expiryDate,getDate()) > 0 AS isExpired        //Tests if not expired

However, the only way I know to return a boolean is to do a SELECT CASE statement with the DateDiff being the case
and then check if any values are returned. If they are, then voila, good for you, if not, then it's expired, boo

Regards,
Zyloch
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You have to cast the expression result to bit data type:

isExpired = CAST(expiryDate > GETDATE()  AS BIT)

if that doesn't give the expected values, you should do like this:
isExpired = CAST( CASE WHEN expiryDate > GETDATE() THEN 1 ELSE 0 END  AS BIT )

CHeers
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now