Quickly change m/d/yyyy dates to mm/dd/yyyy dates in Excel

Sometimes you get data in an Excel or csv file and it will be in a m/d/yyyy format.


Here is an example

1/1/2005
2/1/2012
2/12/2012
12/5/2015

This will look fine if you open the csv file in Excel but if you need to import this data into a database you might get an error that the format is not correct.

 What you want is the data to be in this format
01/01/2005
02/01/2012
02/12/2012
12/05/2015


If you use Format Cells and then pick Date you will see that there is no mm/dd/yyyy format What you have to do is pick the Custom Category and click on the m/d/yyyy format

Now change m/d/yyyy to mm/dd/yyyy

Click OK and you are all set, you will see that the dates are now in mm/dd/yyyy format

Let me just mention that the problem was not the conversion in SQL itself but a tool that did the import choked

BTW SQL Server can do this fairly easily

If I run the following

1 2 3 4 5 6 7 8 9 10 11

;with dates (date) as (select '1/1/2001' union all select '11/1/2001' union all select '1/12/2001' union all select '5/5/2001' union all select '1/22/2001' union all select '12/1/2001' union all select '01/01/2001' ) select convert(date,date) from dates

I will get this as output

2001-01-01
2001-11-01
2001-01-12
2001-05-05
2001-01-22
2001-12-01
2001-01-01

That of course is just a date, if you want mm/dd/yyyy format, you can use convert and style 101...

1 2 3 4 5 6 7 8 9 10 11 12 13 14

;with dates (date) as (select '1/1/2001' union all select '11/1/2001' union all select '1/12/2001' union all select '5/5/2001' union all select '1/22/2001' union all select '12/1/2001' union all select '01/01/2001' ) select convert(varchar(10),convert(date,date),101) from dates select convert(date,date) from dates

And here is that output

01/01/2001
11/01/2001
01/12/2001
05/05/2001
01/22/2001
12/01/2001
01/01/2001

Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

Take a look at this query.

SELECT * FROM

(

SELECT customer_id, ‘MTD’ AS record_type, COUNT(*), SUM(…), AVG(…)

FROM payment_table

WHERE YEAR(payment_dt) = YEAR(GETDATE())

and MONTH(payment_dt) = MONTH(GETDATE())

GROUP BY customer_id) MTD_payments

UNION ALL

(

SELECT customer_id, ‘YTD’ AS record_type, COUNT(*), SUM(…), AVG(…)

FROM payment_table

WHERE

WHERE YEAR(payment_dt) = YEAR(GETDATE())

GROUP BY customer_id) YTD_payments

UNION ALL

(

SELECT customer_id, ‘LTD’ AS record_type, COUNT(*), SUM(…), AVG(…)

FROM payment_table) LTD_payments

) payments_report

ORDER BY customer_id, record_type

Can you see the problem?
A person had this query, it would run for over 24 hours. Wow, that is pretty bad, I don't think I had ever written something that ran over an hour, and the ones I did were mostly defragmentation and update statistics jobs.

The problem is that the following piece of code

where year(payment_dt) = year(getDate())
and month(payment_dt) = month(getDate())

is not sargable. First what does it mean to be sargable? A query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.

This query is not sargable because there is a function on the column, whenever you use a function on the column you will not get an index seek but an index scan. The difference between an index seek and an index scan can be explained like this: when searching for something in a book, you go to the index in the back find the page number and go to the page, that is an index seek. When looking for something in a book you go from page one until the last page, read all the words on all the ages and get what you need, that was an index scan. Do you see how much more expensive in terms of performance that was?

Let's get back to the query, what can we do to make this piece of code use an index seek?
where year(payment_dt) = year(getDate())
and month(payment_dt) = month(getDate())

You would change it to this:
where payment_dt >= dateadd(mm, datediff(mm, 0, getdate())+0, 0)
and payment_dt < dateadd(mm, datediff(mm, 0, getdate())+1, 0)

You can see the complete question on the MSDN forum site here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3746751&SiteID=1
The Person said that his query went from over 24 hours to 36 seconds. Wow!! That is very significant. hardware cannot help you out if you have bad queries like that.

The same exact day I answered a very similar question, take a look here: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3752248&SiteID=1

The person had this

AND DATEDIFF(d, '08/10/2008', DateCreated) >= 0

AND DATEDIFF(d, DateCreated, '08/15/2008') >= 0

I told him to change it to this

AND DateCreated >= '08/10/2008'
and DateCreated <= '08/15/2008'

And that solved that query. If you are interested in some more performance, I have written some Query Optimization items on the LessThanDot Wiki. Below are some direct links

Case Sensitive Search
No Functions on Left Side of Operator
Query Optimizations With Dates
Optimization: Set Nocount On
No Math In Where Clause
Don't Use (select *), but List Columns

If you are interested in some blogposts about dates, take a look at these two which I wrote earlier
How Are Dates Stored In SQL Server?
Do You Know How Between Works With Dates?

Yet Another Date Teaser

It has been a while since my last teaser but here we go

What do you think the following returns?


SELECT CONVERT(datetime,'1/1/1') -CONVERT(datetime,1) + CONVERT(datetime,0)



How about this on SQL Server 2008


SELECT CONVERT(datetime2,'1/1/1'),CONVERT(datetime2,'01/01/01'),CONVERT(datetime2,'0001/01/01')


Now run this on SQL Server 2008

SELECT
ISDATE('1/1/1'),
ISDATE('01/01/01'),
ISDATE('001/01/01'),
ISDATE('0001/01/01')


Now just for fun run these 4 on SQL Server 2008, one of them will fail so run them one by one

SELECT CONVERT(datetime2,'1/1/1')
SELECT CONVERT(datetime2,'01/01/01')
SELECT CONVERT(datetime2,'001/01/01')
SELECT CONVERT(datetime2,'0001/01/01')

Compare the isdate output to the select statement, see the inconsistency?

SQL Teaser: Guess the output

What do you think will be the output?


DECLARE @d datetime
SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)

After that run this to see how to get first and last days for years, quarters, months and weeks(be careful of ISO weeks!)

DECLARE @d datetime
SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d), 0) as FirstDayOfYear,
DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) as LastDayOfYear,
DATEADD(qq, DATEDIFF(qq, 0, @d), 0) as FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) as LastDayOfQuarter,
DATEADD(mm, DATEDIFF(mm, 0, @d), 0) as FirstDayOfMonth,
DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) as LastDayOfMonth,
DATEADD(wk, DATEDIFF(wk, 0, @d), 0) as FirstDayOfWeek,
DATEADD(wk, DATEDIFF(wk, 0, @d)+1, -1) as LastDayOfWeek

SQL Server 2008 Live Meeting Event on August 21st on DateTime function

Don't forget, today is the day of the SQL Server 2008 Live Meeting Event. Here is what I got in my inbox:

We wanted to remind you that on August 21st at 11am Pacific, that we will holding our 3rd Live Meeting event of the month of July. Join Michael Wang as he explains all the interesting features the new function "DateTime" will include. We will some examples of how "DateTime" will work in SQL Server 2008, and will be fielding all your questions as well. So make sure you get there early for all the fun!


New Datetime Data Type
08/21/07 @ 11am Pacific

SQL Server 2008 Has Nanosecond Precision?

It looks like SQL Server 2008 has nanosecond + microseconds precision for the time datatype
If you run the following

[edit]I just looked at BOL and yes nanoseconds = ns, microsecond = mcs when used in dateadd[/edit]

DECLARE @t time
SELECT @t ='0:0'
SELECT @t AS Time1,DATEADD(ms,1,@t) AS TimeMilli,
DATEADD(ns,10000,@t) AS TimeNano1,DATEADD(ns,100,@t) AS TimeNano2


Time1 00:00:00.0000000
TimeMilli 00:00:00.0010000
TimeNano1 00:00:00.0000100
TimeNano2 00:00:00.0000001

Another interesting thing is that you can not use 0,'0' or ' ' to assign a value

These 3 will all fail

DECLARE @t time
SELECT @t =' '

DECLARE @t time
SELECT @t ='0'


DECLARE @t time
SELECT @t =0

But this will succeed

DECLARE @t time
SELECT @ =''