SQL Date formats

[Edward R. Hunter]

The company I work for has been designing tables to hold dates in a variety of ways, YYYYMMDD, and large datetime dd-mm-yy hh:mm:ss.

I am looking for a way to query against the dates as normal dates mm-dd-yy, and without the time of course.

Can anyone point me to some good data on changing date formats on the fly for easier querying? I am using ASP.

Thanks.

[Jean-Michel Biltresse]

The following piece of code should help you :

(Let's consider your table is called MYTABLE and the column defined as a
DATETIME type is called col_datum)

SELECT * FROM dbo.MYTABLE
WHERE CONVERT(varchar(8), col_datum, 1) = '11/30/99'


------------
Edward R. Hunter at 11/30/99 10:40:17 AM


The company I work for has been designing tables to hold dates in a variety of ways, YYYYMMDD, and large datetime dd-mm-yy hh:mm:ss.

I am looking for a way to query against the dates as normal dates mm-dd-yy, and without the time of course.

Can anyone point me to some good data on changing date formats on the fly for easier querying? I am using ASP.

Thanks.

[Sudhakar]

This Query can be useful from ISQL/W.Hope this piece can be added to your information.

Considering following information
SELECT * FROM dbo.MYTABLE
WHERE CONVERT(varchar(12), col_datum, 101) = '11/30/1999'

(hint : 101 is Century format defined for USA)


------------
Jean-Michel Biltresse at 11/30/99 11:26:11 AM


The following piece of code should help you :

(Let's consider your table is called MYTABLE and the column defined as a
DATETIME type is called col_datum)

SELECT * FROM dbo.MYTABLE
WHERE CONVERT(varchar(8), col_datum, 1) = '11/30/99'


------------
Edward R. Hunter at 11/30/99 10:40:17 AM


The company I work for has been designing tables to hold dates in a variety of ways, YYYYMMDD, and large datetime dd-mm-yy hh:mm:ss.

I am looking for a way to query against the dates as normal dates mm-dd-yy, and without the time of course.

Can anyone point me to some good data on changing date formats on the fly for easier querying? I am using ASP.

Thanks.