Select and format date

Eyeballeth SQL expert PortletPaul's article SQL Server Date Styles (formats) using CONVERT() for a TON of T-SQL date formatting expressions.

In your case, mm/dd/yyyy = convert(varchar, your_data_here ,1)

SELECT convert(varchar, dodb,1) FROM dbo.Users

Select allOpen in new window

Keep in mind that converting like this renders it as a varchar.

For BigMonty: I am trying to do it directly on the recordset SQL
Jim: It returns 01/20/73  I need it to be 01/20/1973

-- this is an example of my select and quite a few of those are dates I need to format --

SELECT  a.I94Depdate ,          a.VisaWhere ,          a.VisaDate ,          a.VisaExp ,          a.VisaType ,          a.VisaStatus ,          a.AbrdApt ,          a.AbrdCareOf ,          a.AbrdCity ,          a.AbrdCntry ,          a.AbrdState ,          a.AbrdStr ,          a.AbrdZip ,          a.AlienRegnNum ,  Replace(a.AlienRegnNum,'-','') AS AlienRegnNumComb,        a.ApNum ,          a.ApDate ,          a.ArrivalCity ,  a.ArrivalCity + ' ' + a.ArrivalState AS ArrivalCityState,        a.ArrivalDate ,          a.ArrivalState ,          a.CelPhone , a.AbrdStr + ' ' + a.AbrdApt + ' ' + a.AbrdCity + ' ' +  a.AbrdState + ' ' + a.AbrdZip + ' ' + a.AbrdCntry AS Abrdfulladdress ,          a.consulateaddr ,          a.ConsulateCity ,          a.ConsulateCntry ,          a.ConsulateName ,          a.consulatezip ,          a.ContactId ,          a.DayPhone ,          a.Dob ,          a.Dswg ,          a.Ead ,          a.EadDate ,          a.Email ,          a.EvePhone ,          a.ExpiresOn ,          a.Fax ,          a.FirmId ,          a.FirstNm ,          a.Gc ,          a.GcDate ,          a.I20 ,          a.I20Date ,          a.I797 ,          a.I797Date ,          a.I94 ,   Replace(a.I94,'-','') AS I94NumComb,   I94, STUFF(Replace(I94,'-',''), 4, 0, '-') AS I94NumCombDash,        a.I94Date ,          a.Iap66 ,          a.Iap66Date ,          a.LastNm ,  a.FirstNm + ' ' + a.MiddleNm + ' ' + a.LastNm as fullname,         a.MaidenNm ,          a.MailApt ,          a.MailCareOf ,          a.MailCity ,          a.MailCntry ,          a.MailState ,          a.MailStr ,          a.MailZip ,          a.MajFldStudy ,          a.MiddleNm ,          a.Ms ,          a.Nationality ,          a.NiStatus ,          a.OtherNms ,          a.Pob ,          a.PobCountry ,          a.PobState ,          a.PpExpireOn ,          a.PpIssueCountry ,          a.PPIssuedate ,          a.PpNum ,          a.Qualification ,          a.ResApt ,          a.ResCareOf ,          a.ResCity ,          a.ResCntry ,          a.ResState ,          a.ResStr ,          a.ResZip ,          a.Sex ,          a.Ssn , Replace(a.ssn,'-','') AS Ssndl, Replace(a.ssn,'-','') AS SSNComb,          a.Title ,          a.UserId ,          a.UserType  FROM    users a    WHERE

Select allOpen in new window

Looks correct, and there is no option to leave this be as a date instead of converting it to a varchar correct?

None.  Dates are stored in a specific format, and do not allow themselves to be cosmetically rendered and remain as a date.   If your front-end needs dates to behave like dates for purposes of doing math and sorting, then I'd go with formatting it cosmetically in the front-end (BigMonty's solution).

Between both solutions I'll be able to format it. Thx