Solved

Format SQL 2005 View - Date needs to convert

Posted on 2014-10-16
6
212 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

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…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now