Help in SQL dates 1

[sivi]

I am not an experienced in using SQL Server.

I am trying to compare date values, which are stored as varchar on tables, with 1 month back or 1year forward or 2days back from the system date, in different scenarios.

I have tried using DATEDIFF and CONVERT. I keep getting syntax errors.

Please could someone help me with this.

Many thanks

 

[AWithers]

Please post some 'date' values so we can see the format it is in

Andy

 

[mwolf00]

SELECT * FROM myTable WHERE
cast(textDate as datetime) < getDate()-2 -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[tlbroadbent]

If you show the date format, we should be able to give specific help.

Recommendations: Store dates in SQL Server as datetime columns rather than varchar or char. Review the article at the following link for more info about date handling in SQL Server.

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[sivi]

Andy, Terry Thanks for your reply. The date is in ddmmyyyy (25.10.2002) format.

Many thanks in advance, I would appreciate your response.

 

[AWithers]

I dont think you can convert your text values directly and need to try something like this

select cast(right('20.11.2002',4)+'/'+SUBSTRING ( '20.11.2002' , 4 , 2 ) +'/'+left('20.11.2002',2) as datetime)


where '20.11.2002' is yourdatefield, I presume your text values are always 10 characters long


Andy

 

[tlbroadbent]

dd.mm.yyyy format is style 104 on T-SQL. You can convert character dates to datetime with the CONVERT function.

Convert(datetime, DateColumn, 104) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[sivi]

Andy /Terry /Mike
Many thanks for your suggestions, I really appreciate it.

cast(right(DATEFIELD,4)+'/'+substring(DATEFIELD,3,2)+'/'+left(DATEFIELD,2) as datetime > dateadd(mm,-4,getdate()) gave me the results I wanted, selecting all the dates going back 3 months.

I would appreciate if you could advice me on padding character variables with '0' or spaces to a required length.

I look forward to hearing from you.

S

 

[tlbroadbent]

Here are some examples of padding a character string with leading spaces or zeros.

Convert an INT to char(12) with leading spaces.

Select CharCol=str(IntCol,12)

Convert an INT to char(12) with leading zeros.

Select CharCol=Replace(str(IntCol,12), ' ', '0')
Select CharCol=
Right(Replicate('0',12)+convert(varchar(12), IntCol), 12)

Convert a string to CHAR(20), Right-justified and left-padded with spaces.

Select PaddedCol=
Replicate(' ', 20-datalength(VarcharCol))+VarcharCol)

There are numerous other ways to pad strings. I recommend reading about string functions in the Transact-SQL reference in SQL BOL. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.