Solved

Format SQL 2005 View - Date needs to convert

Posted on 2014-10-16
6
223 Views
Last Modified: 2014-10-16
I have SQL 2005 view I need to export into a long string. This long string is formatted with spaces between each value into about 40 fields of data. I am almost done but I have a SQL date field. In my view I need the date to convert to MMDDYYYY. I am not sure how to convert this value in SQL view.  

Currently:
2014-09-24 15:31:43.893

Needs to be:
09242014

SAMPLE:
Select dbo.Invoices.InvoiceDate
From Invoices
0
Comment
Question by:allenkent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40385381
here is the code:

select month(invoicedate)+day(invoicedate)+year(invoicedate)
From Invoices

Open in new window

0
 

Author Comment

by:allenkent
ID: 40385393
The view above is ADDING the values. I am getting things like:
2047  for  2014-09-24 15:31:43.893

9+24+2014 = 2047
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40385396
>I have a SQL date field. In my view I need the date to convert to MMDDYYYY
Using GETDATE..
SELECT REPLACE(convert(varchar, GETDATE() ,110), '-', '')

Open in new window

So in your case..
SELECT REPLACE(convert(varchar, InvoiceDate ,110), '-', '') as InvoiceDate
FROM Invoices

Open in new window


Here's a handy reference on converting dates
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40385404
Sorry, forgot to cast as char , try this

select cast(month(invoicedate) as char(2)) + cast(day(invoicedate) as char(2))+ cast(year(invoicedate) as char(4))
From Invoices

Open in new window

0
 

Author Closing Comment

by:allenkent
ID: 40385421
Perfect.
SELECT REPLACE(convert(varchar, InvoiceDate ,110), '-', '') as InvoiceDate
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40385423
samo4fun's answer is still not correct, as it doesn't handle single-digit months and days.

For example,  the below returns {blank}2{blank}42014
Declare @dt datetime = '2014-02-04'
SELECT CAST(MONTH(@dt) as char(2)) + CAST(DAY(@dt) as char(2)) + CAST(YEAR(@dt) as char(4))

Open in new window

Using RIGHT, varchar instead of char, and adding trailing zeros would make it return 02042014 correctly though..
Declare @dt datetime = '2014-02-04'
SELECT RIGHT('0' + CAST(MONTH(@dt) as varchar(2)),2)  + RIGHT('0' + CAST(DAY(@dt) as varchar(2)),2) + CAST(YEAR(@dt) as char(4))

Open in new window

0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question