Converting Microsoft Access SQL to Microsoft SQL Server 2005

  • Thread starter Thread starter Brad Bross
  • Start date Start date
B

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
 
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
 
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())
 
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
 
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)
 
Back
Top