Date problem (1 Viewer)

[VegaLA]

No, not personally, but date conversion.
I have this lovely bit of code to convert date
SELECT convert(varchar,convert(varchar,datepart(mm,tmpdate))+ '/' + convert(varchar,datepart(dd,tmpDate),101) + '/' + convert(varchar,datepart(yyyy,tmpdate)),101) AS TheDate from tmpTbl

but I noticed that any day or month numbered between 1 and 9 displays as 1/1/2006 but I would like it to show as 01/01/2006 Is there any way of making the day and month double digits ?

Thanks,
Mitch..........

 

[SQL_Hell]

quote : No, not personally, but date conversion.



Here's a bit of code I use to deal with the lack of leading 0 in a converted date value

set @yesterday = (select dateadd(d,-1,getdate()))

IF(LEN(DATEPART(mm,@yesterday))=1)
SELECT @mm = '0'+CAST(DATEPART(mm,@yesterday) AS CHAR(1))
ELSE SELECT @mm = DATEPART(mm,@yesterday)

IF(LEN(DATEPART(dd,@yesterday))=1)
SELECT @dd = '0'+CAST(DATEPART(dd,@yesterday) AS CHAR(1))
ELSE SELECT @dd = DATEPART(dd,@yesterday)

SELECT @yy = CAST(DATEPART(yy,@yesterday) AS CHAR(4))

set @rundate = @yy+@mm+@dd

I hope this helps

 

[VegaLA]

Thanks SQL_Hell, but I am having problems trying to mix it into my existing code. The error mesage I get is a s follows,

Msg 207, Level 16, State 1, Line 4
Invalid column name 'hire_date'.

and the code I tried using is

use pubs

if (LEN(convert(varchar, datepart(mm,hire_date)))=1)
SELECT '0' + convert(varchar,convert(varchar,datepart(mm,hire_date))+ '/' +
convert(varchar,datepart(dd,hire_date),101) + '/' +
convert(varchar,datepart(yyyy,hire_date)),101) AS day from employee
else SELECT convert(varchar,convert(varchar,datepart(mm,hire_date))+ '/' +
convert(varchar,datepart(dd,hire_date),101) + '/' +
convert(varchar,datepart(yyyy,hire_date)),101) AS day from employee

I know the code only checks for month but I wanted to get that right before moving onto day. The code should display all the records for Hire_Date.
I used your code as is and it worked fine, telling me that 'Command(s) completed successfully.' but does not show anything.

As you can probably guess I am pretty new to this !

Thanks for helping me out.

Mitch.........

 

[SQL_Hell]

Hi

this should do the trick

SELECT hire_date INTO #hiredate FROM employee

DECLARE @d datetime
DECLARE @date varchar(30)
DECLARE @count int
DECLARE @max int
SET @count = 0
SELECT @max= count(1)from #hiredate
--drop table #hiredate2

CREATE TABLE #hiredate2
(
 date varchar(20)
)
--select @max
SELECT top 1 @d = hire_date FROM #hiredate ORDER BY hire_date

WHILE @count < @max
BEGIN
SET @count = @count +1
IF (LEN(convert(varchar, datepart(dd,@d)))=1)
SET @date = '0' + cast(datepart(dd,@d)as char(1)) + '/'
ELSE SELECT @date = cast(datepart(dd,@d)as char(2)) + '/'
IF (LEN(convert(varchar, datepart(mm,@d)))=1)
set @date = @date + '0' + cast(datepart(mm,@d)as char(1)) + '/'
ELSE SET @date = @date +cast(datepart(mm,@d)as char(2)) + '/'
SET @date = @date + cast(datepart(yy,@d) as char(4))
--select @date
INSERT INTO #hiredate2
values (@date)
DELETE FROM #hiredate WHERE hire_date = @d
SELECT TOP 1 @d = hire_date FROM #hiredate ORDER BY hire_date
END


SELECT * FROM #hiredate2

 

[VegaLA]

Ahh... that did the job. It would have taken me another few weeks to figure that out !!
You're a Genius, thank you very much !!!

 

[SQL_Hell]

Good, I'm glad it helped

you may well be able to tidy that code up a bit, it's not exactly elegant