adding a date to select statement

[impulse24]

Hi All,

I have a select statement to display all the records in the table. What I would like to add is a field with the current date. How would I go about this?

I have tried the statment below, but it's no good
Select *, Now(mmddyy) from comics

Spiderman 083101
Superman 083101

 

[tlbroadbent]

The syntax depends on the RDMS you are using. Because your sample uses NOW(), I assume you are using Access. If this is not true let me know.

Select *, date() As TheDate from comics

The ANSI SQL equivalent is

Select *, CURRENT_TIMESTAMP As TheDate from comics

Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[impulse24]

thanks works great..i am using sql 7 as the rdbms. was just thinking of the Now function in VB. Is there a way to format the date? I tried date("mmddyy&quot but that didnt work

 

[tlbroadbent]

Look under the topic "CAST and CONVERT" in SQL Books Online or Transact-SQL Help. You'll find that you can use the CONVERT function to format dates to different styles.

Examples: the numbers are style numbers (102 & 1)

/* convert date to yyyy.mm.dd format (ANSI std) */
Select *,
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102) As TheDate
From comics

/* convert date to mm/dd/yy format (American std) */
Select *,
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 1) As TheDate
From comics

/* Remove the / with the replace function (mmddyy) */
Select *,
REPLACE(CONVERT(CHAR(10), CURRENT_TIMESTAMP, 1),'/','') As TheDate
From comics

Keep in mind that these converted columns are now character data type and will sort like character data not datetime data. Therefore, be cautious when ordering by converted columns or you'll get ordering you may not want. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[impulse24]

thanks..this worked great.