Format Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Format Date

Author

Topic

jlgreco1
Starting Member

3 Posts

Posted - 2010-01-07 : 19:18:20

I have a date field formatted as nvarchar(8)-yyyymmdd and need to format it to mm/dd/yyyy. Every thing I tried blows up. Anyone have any examples that I can try.
Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-07 : 19:20:57

SELECT CONVERT(CHAR(10), CAST(Col1 AS DATETIME), 101)
FROM Table1

N 56°04'39.26"
E 12°55'05.63"

jlgreco1
Starting Member

3 Posts

Posted - 2010-01-07 : 20:13:15

I get this error message:
Arithmetic overflow error converting expression to data type datetime.

select TeamName,Fullname,pp_id,VENDOR,(ISNULL(count(*),0))
from
(select
	
	ENTRYDAT,
	OLNACS,
    Fullname,
	pp_id,
    operator_id,
	Lucent_Log_new,
	TeamName,
	VENDOR
	
 
from(
select 
    CONVERT(CHAR(10), CAST(a.ENTRYDAT AS DATETIME), 101) AS NDATE,
	 --CAST(CAST(a.ENTRYDAT AS VARCHAR(8)) AS DATETIME) NDATE,
	a.ENTRYDAT,
	b.Fullname,
	b.pp_id,
	b.Lucent_Log_new,
    b.operator_id,
	

(ISNULL(b.Market,'')+' '+ISNULL(b.Team,'')) TeamName,
	a.OLNACS,
	CASE
		WHEN a.BKID = '64' THEN 'NATIONWIDE'
		WHEN a.BKID = '77' THEN 'WASHFED'
		ELSE 'PNC'
		END AS VENDOR
FROM 
	Employee.dbo.opid_1 b, 
	APS.dbo.All_Olnacs a
	
where 
	a.OPRNUM = b.operator_id 
and CONVERT(CHAR(10), CAST(a.ENTRYDAT AS DATETIME), 101) between '12/01/2009' AND '12/31/2009'

	)bb


 )ZZ

GROUP BY TeamName,Fullname,pp_id,VENDOR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-07 : 21:46:34

check your ENTRYDAT value. There might be value that is not able to convert to datetime


select *
from   APS.dbo.All_Olnacs
where  isdate(ENTRYDAT) = 0

what is the date format in ENTRYDAT ?

quote:
and CONVERT(CHAR(10), CAST(a.ENTRYDAT AS DATETIME), 101) between '12/01/2009' AND '12/31/2009'

This where clause might give potential problem. As you are comparing the date in string format of MM/DD/YYYY. What happen if you wants the ENTRYDAT between 1st Dec 2009 and 31 Jan 2010 ?

"between '12/01/2009' and '01/31/2010'" ? You will not get any result return.

After converting ENTRYDAT to datetime, you should leave it as datetime and compare. Also specify the date in ISO format YYYYMMDD
so the statement should goes like

and CAST(a.ENTRYDAT AS DATETIME)  between '20091201' AND '20091231'

as the left side is a datetime, SQL Server will implicitly convert the string '20091201' and '20091231' to datetime before compare

KH
[spoiler]Time is always against us[/spoiler]

jlgreco1
Starting Member

3 Posts

Posted - 2010-01-07 : 22:48:06

This field I need to convert to date is formatted as nvarchar(8)- yyyymmdd.

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-08 : 01:39:19

You don't need to convert, you can use the below condition

AND DATEDIFF(DAY, a.ENTRYDAT, '12/01/2009') <= 0 AND DATEDIFF(DAY, a.ENTRYDAT, '12/31/2009') >=0

in place of

AND CONVERT(CHAR(10), CAST(a.ENTRYDAT AS DATETIME), 101) between '12/01/2009' AND '12/31/2009'

and it should work as expected.

balaganapathy n.

Anything you can imagine is real.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-08 : 02:12:38

Use this

where 
	a.OPRNUM = b.operator_id 
and CAST(a.ENTRYDAT AS DATETIME) between '12/01/2009' AND '12/31/2009'
and ISDATE(a.ENTRYDAT)=1 and LEN(a.ENTRYDAT)=8

This is why you should always use proper datetime datatype to store dates

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources