Converting Microsoft Access SQL to Microsoft SQL Server 2005

[Brad Bross]

Good afternoon mavens, would one of you be able to assist this tyro?

I have a successful Microsoft Access SQL that reads
" >Format(Format(Date(),"yyyymmdd"),"General Number") "

Microsoft SQL Server 2005 will not accept it. Would one of you know how to
convert it? Furthermore, would there be a web site any where which would
help me with such conversions in the future? Many thx.

Brad

 

[Jeff Boyce]

Brad

"numbers" are things you can add, subtract, multiply and divide.

Care to explain how you plan to divide a date?

Why do you feel you need to format that date as a "General Number"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jellifish]

Not sure why you would want to do this, but if you must, then the following
should work:

(YEAR(GETDATE()) * 10000) + (MONTH(GETDATE()) * 100) + DAY(GETDATE())

 

[Brad Bross]

Jeff, here is the Access SQL:
SELECT CIMOR_dwvStandardMeansTest.ResponsibilityEndDate
FROM CIMOR_dwvStandardMeansTest
WHERE
(((CIMOR_dwvStandardMeansTest.ResponsibilityEndDate)>Format(Format(Date(),"yyyymmdd"),"General Number")));

What I am attempting to do is look through the column of dates which are
formatted as text YYYYMMDD. Trying to pick out those which are greater than
today's date.

Brad

 

[Sylvain Lafontaine]

Use the convert function with the format 112 to convert a date to the format
"yyyymmdd":

select convert (char(8), getdate(), 112)

See http://msdn.microsoft.com/en-us/library/ms187928.aspx for more info on
the CAST and the CONVERT functions. The result above will be of type string
but you should be OK for making your comparaison.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Brad Bross]

Thx for everyone's replies.