Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Format SQL 2005 View - Date needs to convert

Posted on 2014-10-16
6
Medium Priority
?
229 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 66

Accepted Solution

by:
Jim Horn earned 2000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

927 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