Date format conversion - 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 2000 Forums

SQL Server Development (2000)

Date format conversion

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-18 : 11:35:17

Hi there,

I currently have the following SQL statement to return fields from my SQL Server 2000 database:

SELECT ServReportNo, SiteNo, AreaCode, AreaDesc, PropertyName, LocationCode, Address1, Address2, ResStatus, Address3, PostCode, ServType, InstallCheckDescription, ContNo, DateInspected, FirmName, DateNextInspection, CertificateRecieved, CertificateInfo, DescriptionOfWork

FROM dbo.vSiteService

WHERE ServReportNo = ('"& ServReportNo &"')

The date format of the 'DateInspected' field is currently mm-dd-yy.....however I would like to change this to dd-mm-yy.

I am aware that this can be done using something like - CONVERT(datetime, ServReportNo, 103) - but I'm not sure if and how this can used with this SQL statement....or would I need to create a seperate SQL statement specifically to modify that field?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-18 : 11:37:54

[code]SELECT ServReportNo, SiteNo, AreaCode, AreaDesc, PropertyName, LocationCode, Address1, Address2, ResStatus, Address3, PostCode, ServType, InstallCheckDescription, ContNo, convert(varchar(10), DateInspected, 103), FirmName, DateNextInspection, CertificateRecieved, CertificateInfo, DescriptionOfWork
FROM dbo.vSiteService
WHERE ServReportNo = ('"& ServReportNo &"')[/code]

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-18 : 11:41:12

quote:

Originally posted by khtan

SELECT ServReportNo, SiteNo, AreaCode, AreaDesc, PropertyName, LocationCode, Address1, Address2, ResStatus, Address3, PostCode, ServType, InstallCheckDescription, ContNo, convert(varchar(10), DateInspected, 103), FirmName, DateNextInspection, CertificateRecieved, CertificateInfo, DescriptionOfWork
FROM dbo.vSiteService
WHERE ServReportNo = ('"& ServReportNo &"')

hmm....I tried that way but then when I tried to print out the value using:

rs_SiteService.Fields.Item("DateInspected").Value

it produced the error:

ADODB.Fields error '800a0cc1' 

Item cannot be found in the collection corresponding to the requested name or ordinal

Do I need to use a different statement to return the value if I have converted it?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-18 : 11:42:51

>>or would I need to create a seperate SQL statement specifically to modify that field
You definately don't want to modify the column's datatype.

>>but I'm not sure if and how this can used with this SQL statement
Ideally you would preserver the datatype and let the front end presentation layer worry about the formatting but if you want to convert it to a formatted character then Khtan's solution should do the trick.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-18 : 11:44:25

maybe you need to alias the "converted" result with a column alias.
convert(varchar, <datecol>, 103) as [columlnName]

Be One with the Optimizer
TG

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-18 : 11:46:43

quote:

>>but I'm not sure if and how this can used with this SQL statement

Ideally you would preserver the datatype and let the front end presentation layer worry about the formatting but if you want to convert it to a formatted character then Khtan's solution should do the trick.

Be One with the Optimizer
TG

Well, literally all I want to do is take a date from the database, modify the format and then display it.....at the moment it appears modifying it is no problem but then it won't let me display it using the same statement as before (it doesn't seem to be able to find it in the recordset)

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-18 : 11:47:13

quote:
Originally posted by TG

maybe you need to alias the "converted" result with a column alias.
convert(varchar, <datecol>, 103) as [columlnName]

Be One with the Optimizer
TG

ahaaa...that might do it...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 11:47:18

Cant you do it in front end application?

Madhivanan

Failing to plan is Planning to fail

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-18 : 11:51:20

quote:
Originally posted by madhivanan

Cant you do it in front end application?

Madhivanan

Failing to plan is Planning to fail

I don't see why not if it's better....and if u told me how....I'm using an ASP front end :P

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 11:59:39

If you send formatted date to front end, it is actually Date-like-Varchar so that you cant apply date functions like datediff, dateadd over it. Cant you use Format function in asp like Format(date,"dd-mm-yyyy")?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources