Select date statement

• petey20

  SSCommitted

  Points: 1741

  More actions

  September 30, 2002 at 7:30 am

  #79469

  I have a smalldatetime field that I want to display in a .NET textbox. The following produces the format I want except for single digit days or months, i.e. days and months 1-9. I want to keep the mm/dd/yyyy format, so if the following query were to return sept 30 it gives me 9/30/2002 instead of 09/30/2002. I believe the issue is in my Datepart parameters. If there's an easier way to get a 01/01/2002 format other than how I'm doing it...I'd appreciate any suggestions!!

  select convert(varchar,datepart(month,PartsDueBy)) + '/' + convert(varchar,datepart(day,PartsDueBy))+ '/' + convert(varchar,datepart(year,PartsDueBy)) from [order]

  Thanks!

  Petey

• petey20

  SSCommitted

  Points: 1741

  More actions

  September 30, 2002 at 9:12 am

  #437492

  This does it but it seems a bit extream??

  select

  case len(convert(varchar,datepart(mm,PartsDueBy)))

  when 1 then '0' + convert(varchar,datepart(mm,PartsDueBy))

  when 2 then convert(varchar,datepart(mm,PartsDueBy))

  end

  + '/' +

  case len(convert(varchar,datepart(dd,PartsDueBy)))

  when 1 then '0' + convert(varchar,datepart(dd,PartsDueBy))

  when 2 then convert(varchar,datepart(dd,PartsDueBy))

  end

  + '/' +

  convert(varchar,datepart(year,PartsDueBy))

  from [order]

• NPeeters

  SSChampion

  Points: 12541

  More actions

  September 30, 2002 at 9:36 am

  #437499

  Just use CONVERT(VARCHAR, PartsDueBy, 101) for the mm/dd/yyyy format.

  You can check the BOL for other formats.

• jmadren

  SSCrazy

  Points: 2055

  More actions

  September 30, 2002 at 9:39 am

  #437500

  All you need is Convert(varchar(10),PartsDueBy,101)

  Jay

  
  Jay Madren

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  September 30, 2002 at 9:49 am

  #437501

  Think you want to use the convert function. Here try this:

  create table date_test (sd smalldatetime)

  insert into date_test values(getdate())

  select convert(char(10),sd,101) from date_test

  drop table date_test

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP