Change date format of COALESCE statemetnt is a query or view to "YYYYMM"

• doug.r.nelson

  Mr or Mrs. 500

  Points: 504

  More actions

  November 4, 2008 at 12:57 pm

  #195446

  Is there a way to convert a date type of "date-time" in a view or query to a "YYYYMM" format?

  In Access I could format date types in IIF Statements using using "Format" e.g.

  "Install_YearMonth: (Format([Contract Live Date],[BNL Out Date],[Booking Date],[ LV Date])),"yyyymm""

  In SQL Server 2005 & 2008 I am using the "COALESCE" command instead of an IIF statement

  whice is more efficient and faster. However the "Format" statement isn't recognised.

  My problem is the result of the "COALESCE" statement is a "date" formatted "date-time". How can I have the result in a "YYYYMM" format?

  Here is the Query

  SELECT [tbl_IDS_Extract_Local-2].Customer_No,

  MasterProductTable_Database.SFAProductCategory,

  MasterProductTable_Database.ProductLevel,

  tbl_IDS_Extract_Local-2].Serial_No,

  [tbl_IDS_Extract_Local-2].Install_Type,

  [tbl_IDS_Extract_Local-2].IDS_Status,

  [tbl_IDS_Extract_Local-2].AssetStatus,

  [tbl_IDS_Extract_Local-2].Install_YearMonth,

  [tbl_IDS_Extract_Local-2].IDS_YearMonth,

  COALESCE ([tbl_IDS_Extract_Local-2].Contract_Live_Date,

  [tbl_IDS_Extract_Local-2].BNL_Out_Date,

  [tbl_IDS_Extract_Local-2].Booking_Date,

  [tbl_IDS_Extract_Local-2].LV_Date) AS INST_YearMonth

  FROM [tbl_IDS_Extract_Local-2] INNER JOIN

  MasterProductTable_Database ON

  [tbl_IDS_Extract_Local-2].Prod_Hierarchy =

  MasterProductTable_Database.PrdNum;

  Thank you for any help I can get.

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  November 4, 2008 at 1:01 pm

  #893843

  Best is to format all stuff in the front end application. (because this way you keep the datatime characteristics as long as possible)

  Check out convert in BOL

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 4, 2008 at 1:05 pm

  #893847

  Where is this query being used, a report, a front end app?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 4, 2008 at 1:09 pm

  #893853

  If you need to do it in the query, does this do what you want?

  SELECT

  [tbl_IDS_Extract_Local-2].Customer_No,

  MasterProductTable_Database.SFAProductCategory,

  MasterProductTable_Database.ProductLevel,

  tbl_IDS_Extract_Local-2].Serial_No,

  [tbl_IDS_Extract_Local-2].Install_Type,

  [tbl_IDS_Extract_Local-2].IDS_Status,

  [tbl_IDS_Extract_Local-2].AssetStatus,

  [tbl_IDS_Extract_Local-2].Install_YearMonth,

  [tbl_IDS_Extract_Local-2].IDS_YearMonth,

  LEFT(CONVERT(varchar(8),COALESCE ([tbl_IDS_Extract_Local-2].Contract_Live_Date,

  [tbl_IDS_Extract_Local-2].BNL_Out_Date,

  [tbl_IDS_Extract_Local-2].Booking_Date,

  [tbl_IDS_Extract_Local-2].LV_Date), 112), 6) AS INST_YearMonth

  FROM

  [tbl_IDS_Extract_Local-2]

  INNER JOIN MasterProductTable_Database

  ON ([tbl_IDS_Extract_Local-2].Prod_Hierarchy = MasterProductTable_Database.PrdNum);

• navecomp

  Old Hand

  Points: 358

  More actions

  November 4, 2008 at 1:12 pm

  #893856

  The easy way to do it is to create a function that will receive the date as a param and return a varchar formated YYYMM.

  then call the function from your select statement.

• willchen28

  SSC Journeyman

  Points: 95

  More actions

  November 4, 2008 at 1:18 pm

  #893859

  You need to wrap your COALESCE in a COVERT function. Something like this should work:

  LEFT(CONVERT(VARCHAR(8), COALESCE ([tbl_IDS_Extract_Local-2].Contract_Live_Date,

  [tbl_IDS_Extract_Local-2].BNL_Out_Date,

  [tbl_IDS_Extract_Local-2].Booking_Date,

  [tbl_IDS_Extract_Local-2].LV_Date), 112), 6)

• doug.r.nelson

  Mr or Mrs. 500

  Points: 504

  More actions

  November 6, 2008 at 10:31 am

  #895067

  Thank you very much for your help. Wraping the COALESCE in a COVERT function worked perfectly.

  Doug

• doug.r.nelson

  Mr or Mrs. 500

  Points: 504

  More actions

  November 6, 2008 at 10:32 am

  #895068

  Thank you very much for your help. Wraping the COALESCE in a COVERT function worked perfectly.

  Doug

• doug.r.nelson

  Mr or Mrs. 500

  Points: 504

  More actions

  November 6, 2008 at 10:34 am

  #895071

  Thank you for your question, I wanted to use the coalesce functionin a SQL Server View to give me an installation date where the date can come from one of multiple fields that might be null.

  Wraping the COALESCE in a COVERT function seems to be the best way at present.

  Doug

• doug.r.nelson

  Mr or Mrs. 500

  Points: 504

  More actions

  November 6, 2008 at 10:38 am

  #895075

  I wish I could format dates as you suggest, but I built the database of 15.5 million records from monthly extracts from an InfoLease database to give installation and installed base history. Changing date formats for monthly extracts going back to 2001 would not be practical. Also, we want to keep the date fields as date fields, not yearmonth fields.

  I do want to thank you for taking time out of your day to help me.

  Doug

• doug.r.nelson

  Mr or Mrs. 500

  Points: 504

  More actions

  November 6, 2008 at 10:39 am

  #895078

  Thank you very much for your help. Wraping the COALESCE in a COVERT function worked perfectly.

  Doug