Question about date formatting...

[ehenry]

I am trying to store the the the previous days date in a variable in the format 'MMDDYYYY' so I can use it as a portion of a CSV filename in a bulk insert statement. So far I have:

SELECT @fdate = CONVERT(VARCHAR(8), DATEADD(day,-1,GetDate()), 112) AS [MMDDYYYY]

I am getting the following error:
Incorrect syntax near the keyword 'AS'.

I have also tried using SET @fdate

Any help would be appreciated, I am sure this is something simple I am missing, since I am new to SQL Server.

 

[gmmastros]

[tt][blue]SELECT @fdate =[/blue][/tt]

In this case, select is not going to return a result set. Instead, it is going to set the value of the @fdate variable to a particular value.

When you do this:

[tt][blue]
SELECT SomeColumnName AS [MMDDYYYY]
[/blue][/tt]

You are returning a result set. When you don't use AS, the column name for the result set is given the same name as the column. When you include AS (officially called an alias), you will get the same results, but the column will be named [MMDDYYYY] instead of the actual column name.

So, you see, you can use select for 2 different purposes. You can use it to assign the value to a variable, and you can use it to return a result set.

When assigning a value to a variable, it does not make sense to use an alias because a variable cannot have an alias.

To solve your syntax problem, simply remove the AS part.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ehenry]

Yes I will Try removing the AS. I created function to return the date parts in the order I wanted in the meantime.

 

[JeffModen]

No need for a function... this is easy.

SELECT REPLACE(CONVERT(CHAR(10), GETDATE()-1, 101),'/','')

Now, the problem with that is the file names won't be sortable by date. Instead of using the MMDDYYYY format, I recommend using the YYYYMMDD format. Like this...

SELECT CONVERT(CHAR(8), GETDATE()-1, 112)

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row