Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format SQL 2005 View - Date needs to convert

Posted on 2014-10-16
6
Medium Priority
?
225 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

718 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