Showing posts with label Dates. Show all posts
Showing posts with label Dates. Show all posts

Saturday, February 13, 2016

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

Sunday, August 17, 2008

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?

    Thursday, January 10, 2008

    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?

    Wednesday, October 10, 2007

    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

    Tuesday, August 21, 2007

    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

    Wednesday, August 01, 2007

    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 @ =''