SQL Server 2000 Date Format

[dhutton]

Hello everyone,

How would I go about changing the Date format so my query can read it. The below example does not work but if I were to have a table with YYYYMMDD the below query does work. The query doesnt like dashes or forward slashes when working with date fields in certain tables - How can I say in my query

IF Date has dashes THEN change FROM 2007-08-01 to 20070801

or

IF Date has forward slashes THEN change FROM 8/01/2007 to 20070801

**** This does not work because of the dashes in the date

SELECT TOP 100 *

FROM AcctRecv

WHERE BillDate BETWEEN '2007-08-01' AND '2007-08-31'

ORDER BY BillDate

[ck9663]

Hello everyone,

How would I go about changing the Date format so my query can read it. The below example does not work but if I were to have a table with YYYYMMDD the below query does work. The query doesnt like dashes or forward slashes when working with date fields in certain tables - How can I say in my query

IF Date has dashes THEN change FROM 2007-08-01 to 20070801

or

IF Date has forward slashes THEN change FROM 8/01/2007 to 20070801

**** This does not work because of the dashes in the date

SELECT TOP 100 *

FROM AcctRecv

WHERE BillDate BETWEEN '2007-08-01' AND '2007-08-31'

ORDER BY BillDate

convert it to date field. as long as it's a valid date, sql-server will convert those into a valid date field

[VBPhilly]

Use REPLACE and CONVERT functions in your SELECT.

SQL REPLACE has the syntax:

REPLACE(Express ion, TextToReplace, ReplacementText )

SQL may/may not implicitly convert a date to a string. Best to be cautious by using CONVERT with the first parameter in Replace.
Note that I nested it because you want to remove dashes and slashes:

SELECT REPLACE(REPLACE(CONVERT(VARCHAR(255), MyDateField), '-', ''), '/', '')

Its not pretty but it should work. If it does not: lookup the syntax for Replace and Convert.

Reference:
http://www.sqlteam.com/article/using-replace-in-an-update-statement

[dhutton]

SQL Server 2000 Date Format

Thanks for both of your replies.

VBPhilly - your suggestion works but only on the final output by displaying the converted date field - but it doesnt let me query with the converted date field.

***** This works great!!!

SELECT REPLACE(REPLACE (CONVERT(VARCHA R(255), BillDate), '-
', ''), '/', '') AS Bill_Date,

But when I add my WHERE argument the query still looks at the date field in its original state (with dashes and forward slashes) instead of the converted date

WHERE BillDate = '20070715'

Query does not reconize that this has been converted - even though the final output displays the converted "BillDate" field properly the way I want it.

[ck9663]

Thanks for both of your replies.

VBPhilly - your suggestion works but only on the final output by displaying the converted date field - but it doesnt let me query with the converted date field.

***** This works great!!!

SELECT REPLACE(REPLACE (CONVERT(VARCHA R(255), BillDate), '-
', ''), '/', '') AS Bill_Date,

But when I add my WHERE argument the query still looks at the date field in its original state (with dashes and forward slashes) instead of the converted date

WHERE BillDate = '20070715'

Query does not reconize that this has been converted - even though the final output displays the converted "BillDate" field properly the way I want it.

select....

where cast(BILLDATE as smalldatetime) = cast('20070715 as smalldatetime)

if the time part is giving you problem, you might want to handle that as well