Solved

Format SQL 2005 View - Date needs to convert

Posted on 2014-10-16
6
219 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
  • 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 65

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 65

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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 …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

825 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