Date and Time calculations in SQL Server 2012

I have 2 columns, [Date] and [Time] in my table.

Date is of form dd.mm.yyyy and type varchar (10).
Time is of form hh:mm and type varchar(5)

For a particular date I want to find the previous date, so for example, for 2014.10.19 I want to find out the previous date which would be  2014.10.18 and that is by doing something like:
PRINT 'some text' + @mydate - 1

It complains about conversion error. I have tried CAST and CONVERT but I still get an error.

I need an expert to show me how to achieve this.

Thanks.
LVL 16
Richard OlutolaConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
if date is in dd.mm.yyyy, try
convert(datetime, [Date], 104)-1 preDate

Open in new window

if date is in yyyy.mm.dd, try
convert(datetime, [Date], 102)-1 preDate

Open in new window

ref:
SQL Server Date Formats
http://www.sql-server-helper.com/tips/date-formats.aspx
ste5anSenior DeveloperCommented:
You cannot use simply arithmetic's with DATE and TIME as you could with DATETIME.

Just use DATEADD():

DECLARE @Date DATE = '20141029';
DECLARE @Time TIME = '07:13:23';

SELECT  DATEADD(DAY, -1, @Date) ,
        DATEADD(DAY, +1, @Date) ,
        DATEADD(MINUTE, -10, @Time) ,
        DATEADD(MINUTE, +10, @Time); 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anoo S PillaiCommented:
DECLARE @mydate VARCHAR(10) = '2014.10.19'
SELECT DATEADD ( dd, -1 , CONVERT ( DATE , @mydate , 102 ) ) 

Open in new window

CONVERT ( DATE , @mydate , 102 )
Converting the variable/column to data type date. Your values are in varchar (10) column, hence during conversion SQL should know the current format of the value that is to be converted. That is specified by the format 102
102 = yyyy.mm.dd, refer Convert

DATEADD ( dd, -1 , CONVERT ( DATE , @mydate , 102 ) )
DATEADD would be adding a specified number interval to the date supplied, What interval is being added is decided by the first argument. It can be in days, years, months etc. This is decided by the first parameter, refer DATEADD
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

PortletPaulEE Topic AdvisorCommented:
I have 2 columns, [Date] and [Time] in my table.

Date is of form dd.mm.yyyy and type varchar (10).
Time is of form hh:mm and type varchar(5)

=4 problems
don't use reserved words as names i.e. [Date] and [Time] are not good column names

then:
Storing dates or time as characters is wrong, but even worse you the date it in a form that cannot lead to sensible date sorting as well. Wow, you are just asking for difficulty.

Suggested reading: DATE and TIME ... don't be scared, and do it right (the first time)

and SQL Server Date Styles (formats) using CONVERT()
Richard OlutolaConsultantAuthor Commented:
Thanks to all. I'll check the suggestions soon and get back to you.

R.
PortletPaulEE Topic AdvisorCommented:
Just in case you haven't tried, this is an example of sorting rows by data stored in dd.mm.yyyy format:
    | Stuff That Looks Like Dates | Real Dates In DBMS |
    |-----------------------------|--------------------|
    |                  01.01.2000 |         01.01.2000 |
    |                  01.01.2014 |         01.01.2014 |
    |                  02.02.2000 |         02.02.2000 |
    |                  02.02.2014 |         02.02.2014 |
    |                  05.03.2000 |         05.03.2000 |
    |                  06.03.2014 |         06.03.2014 |
    |                  06.04.2000 |         06.04.2000 |

Open in new window

Notice how the sorting occurs by the dd and we get 1st of January 2000 and 1st of Janary 2014 before 2nd january 2000 etc.

IF however the dates really are stored as dates in the dbms, and we sort by that field, this is the result:
    | Real Dates In DBMS | Stuff That Looks Like Dates |
    |--------------------|-----------------------------|
    |         01.01.2000 |                  01.01.2000 |
    |         02.02.2000 |                  02.02.2000 |
    |         05.03.2000 |                  05.03.2000 |
    |         06.04.2000 |                  06.04.2000 |
    |         08.05.2000 |                  08.05.2000 |
    |         09.06.2000 |                  09.06.2000 |
    |         11.07.2000 |                  11.07.2000 |

Open in new window

now the sorting is sensible.

You can see this operating as a demonstration here http://sqlfiddle.com/#!3/299fe/3
details of that:
**MS SQL Server 2008 Schema Setup**:

    CREATE TABLE Table1
        ([DatesTrue] datetime, [DatesFalse] varchar(10))
    ;
        
    INSERT INTO Table1
        ([DatesTrue], [DatesFalse])
    VALUES
        ('2000-01-01 00:00:00', '01.01.2000'),
        ('2000-02-02 00:00:00', '02.02.2000'),
        ('2000-03-05 00:00:00', '05.03.2000'),
        ('2000-04-06 00:00:00', '06.04.2000'),
        ('2000-05-08 00:00:00', '08.05.2000'),
        ('2000-06-09 00:00:00', '09.06.2000'),
        ('2000-07-11 00:00:00', '11.07.2000'),
        ('2000-08-12 00:00:00', '12.08.2000'),
        ('2000-09-13 00:00:00', '13.09.2000'),
        ('2000-10-15 00:00:00', '15.10.2000'),
        ('2000-11-16 00:00:00', '16.11.2000'),
        ('2000-12-18 00:00:00', '18.12.2000'),
        ('2001-01-19 00:00:00', '19.01.2001'),
        ('2001-02-20 00:00:00', '20.02.2001'),
        ('2001-03-24 00:00:00', '24.03.2001'),
        ('2001-04-25 00:00:00', '25.04.2001'),
        ('2001-05-27 00:00:00', '27.05.2001'),
        ('2001-06-28 00:00:00', '28.06.2001'),
        ('2001-07-30 00:00:00', '30.07.2001'),
        ('2014-01-01 00:00:00', '01.01.2014'),
        ('2014-02-02 00:00:00', '02.02.2014'),
        ('2014-03-06 00:00:00', '06.03.2014'),
        ('2014-04-07 00:00:00', '07.04.2014'),
        ('2014-05-09 00:00:00', '09.05.2014'),
        ('2014-06-10 00:00:00', '10.06.2014'),
        ('2014-07-12 00:00:00', '12.07.2014'),
        ('2014-08-13 00:00:00', '13.08.2014'),
        ('2014-09-14 00:00:00', '14.09.2014'),
        ('2014-10-16 00:00:00', '16.10.2014'),
        ('2014-11-17 00:00:00', '17.11.2014'),
        ('2014-12-19 00:00:00', '19.12.2014'),
        ('2015-01-20 00:00:00', '20.01.2015'),
        ('2015-02-21 00:00:00', '21.02.2015'),
        ('2015-03-25 00:00:00', '25.03.2015'),
        ('2015-04-26 00:00:00', '26.04.2015'),
        ('2015-05-28 00:00:00', '28.05.2015'),
        ('2015-06-29 00:00:00', '29.06.2015'),
        ('2015-07-31 00:00:00', '31.07.2015')
    ;
    
**Query 1**:

    select 
           DatesFalse as [Stuff That Looks Like Dates]
         , convert(varchar(10),DatesTrue,104) as [Real Dates In DBMS]
    from Table1
    order by DatesFalse
    

**[Results][2]**:
    | Stuff That Looks Like Dates | Real Dates In DBMS |
    |-----------------------------|--------------------|
    |                  01.01.2000 |         01.01.2000 |
    |                  01.01.2014 |         01.01.2014 |
    |                  02.02.2000 |         02.02.2000 |
    |                  02.02.2014 |         02.02.2014 |
    |                  05.03.2000 |         05.03.2000 |
    |                  06.03.2014 |         06.03.2014 |
    |                  06.04.2000 |         06.04.2000 |
    |                  07.04.2014 |         07.04.2014 |
    |                  08.05.2000 |         08.05.2000 |
    |                  09.05.2014 |         09.05.2014 |
    |                  09.06.2000 |         09.06.2000 |
    |                  10.06.2014 |         10.06.2014 |
    |                  11.07.2000 |         11.07.2000 |
    |                  12.07.2014 |         12.07.2014 |
    |                  12.08.2000 |         12.08.2000 |
    |                  13.08.2014 |         13.08.2014 |
    |                  13.09.2000 |         13.09.2000 |
    |                  14.09.2014 |         14.09.2014 |
    |                  15.10.2000 |         15.10.2000 |
    |                  16.10.2014 |         16.10.2014 |
    |                  16.11.2000 |         16.11.2000 |
    |                  17.11.2014 |         17.11.2014 |
    |                  18.12.2000 |         18.12.2000 |
    |                  19.01.2001 |         19.01.2001 |
    |                  19.12.2014 |         19.12.2014 |
    |                  20.01.2015 |         20.01.2015 |
    |                  20.02.2001 |         20.02.2001 |
    |                  21.02.2015 |         21.02.2015 |
    |                  24.03.2001 |         24.03.2001 |
    |                  25.03.2015 |         25.03.2015 |
    |                  25.04.2001 |         25.04.2001 |
    |                  26.04.2015 |         26.04.2015 |
    |                  27.05.2001 |         27.05.2001 |
    |                  28.05.2015 |         28.05.2015 |
    |                  28.06.2001 |         28.06.2001 |
    |                  29.06.2015 |         29.06.2015 |
    |                  30.07.2001 |         30.07.2001 |
    |                  31.07.2015 |         31.07.2015 |
**Query 2**:

    
    
    select 
           convert(varchar(10),DatesTrue,104) as [Real Dates In DBMS]
         , DatesFalse as [Stuff That Looks Like Dates]
    from Table1
    order by DatesTrue
    

**[Results][3]**:
    | Real Dates In DBMS | Stuff That Looks Like Dates |
    |--------------------|-----------------------------|
    |         01.01.2000 |                  01.01.2000 |
    |         02.02.2000 |                  02.02.2000 |
    |         05.03.2000 |                  05.03.2000 |
    |         06.04.2000 |                  06.04.2000 |
    |         08.05.2000 |                  08.05.2000 |
    |         09.06.2000 |                  09.06.2000 |
    |         11.07.2000 |                  11.07.2000 |
    |         12.08.2000 |                  12.08.2000 |
    |         13.09.2000 |                  13.09.2000 |
    |         15.10.2000 |                  15.10.2000 |
    |         16.11.2000 |                  16.11.2000 |
    |         18.12.2000 |                  18.12.2000 |
    |         19.01.2001 |                  19.01.2001 |
    |         20.02.2001 |                  20.02.2001 |
    |         24.03.2001 |                  24.03.2001 |
    |         25.04.2001 |                  25.04.2001 |
    |         27.05.2001 |                  27.05.2001 |
    |         28.06.2001 |                  28.06.2001 |
    |         30.07.2001 |                  30.07.2001 |
    |         01.01.2014 |                  01.01.2014 |
    |         02.02.2014 |                  02.02.2014 |
    |         06.03.2014 |                  06.03.2014 |
    |         07.04.2014 |                  07.04.2014 |
    |         09.05.2014 |                  09.05.2014 |
    |         10.06.2014 |                  10.06.2014 |
    |         12.07.2014 |                  12.07.2014 |
    |         13.08.2014 |                  13.08.2014 |
    |         14.09.2014 |                  14.09.2014 |
    |         16.10.2014 |                  16.10.2014 |
    |         17.11.2014 |                  17.11.2014 |
    |         19.12.2014 |                  19.12.2014 |
    |         20.01.2015 |                  20.01.2015 |
    |         21.02.2015 |                  21.02.2015 |
    |         25.03.2015 |                  25.03.2015 |
    |         26.04.2015 |                  26.04.2015 |
    |         28.05.2015 |                  28.05.2015 |
    |         29.06.2015 |                  29.06.2015 |
    |         31.07.2015 |                  31.07.2015 |

  [1]: http://sqlfiddle.com/#!3/299fe/3
  [2]: http://sqlfiddle.com/#!3/299fe/3/0

Open in new window

Richard OlutolaConsultantAuthor Commented:
Thanks all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.