SQL Date Problem

[paragvshah]

Friends,

I have one query, I am using a table with a field name lastDate and Datatype is varchar(10). I am storing the date in (yyyy/mm/dd) format in the table. Now I want a query which would compare the dates from lastdate field in the table with the currentdate (getdate()) and retreive the rows from the table matching currentdate

The query that i had tried is as follows

Select * FROM TableName
Where LastDATE = cast(year(getdate()) as varchar(4))+'/'+cast(Month(getdate()) as varchar(2))+'/'+cast(Day(getdate()) as varchar(2))

But this query doesnt give me any results matching for dates like 01/08/2003, 05/01/2003 etc. where there is a leading zero in month or day. because the cast function removes the leading zeros from month and day and gives the output like 1/8/2003, 5/1/2003 and so this doesn't matches with the dates in table as i have used varchar datatype for Lastdate field in table.

Can anybody help me out for sorting out this query.

Thnx in advance

Parag Shah

 

[dickiebird]

I haven't tried it - but how about :
Where LastDATE = cast(year(getdate()) as varchar(4))+'/'+cast(right("00" + Month(getdate()),2)
as varchar(2))+'/'+cast(right("00" + Day(getdate()),2) as varchar(2))



Dickie Bird (-)))

 

[paragvshah]

DickieBird,

This is not working out. Its still displays the date as 1/1/2003 etc.

Parag

 

[SQLBill]

Try this:

DECLARE @mydate VARCHAR(10)
SET @mydate = '2004/01/09'

SELECT @mydate
WHERE @mydate = CONVERT(VARCHAR(10), GETDATE(), 111)

-SQLBill

 

[nads260]

You can try This:

where LastDATE = convert(varchar(10),GetDate(),111)

 

[dan1967]

Try this conversion:

select cast(year(getdate()) as char(4))+'/'+
	right('0' + rtrim(convert(char(2), Month(getdate()))), 2)+'/'+
	right('0' + rtrim(convert(char(2), Day(getdate()))), 2)

Dan.

 

[paragvshah]

Thnx all

where LastDATE = convert(varchar(10),GetDate(),111)

This worked out.

Parag