Covert date (varchar) to date (Datetime)

Question

3.00/5 (1 vote)

See more:

Hi,
I need help on the following.I have a column 'Date' in Staging table with the following
Data type=varchar(50)
Format/Style='dd/mm/yy'
Example='18/11/07'

I need to convert 'Date' column to the following .This conversion would take place from a staging table to a destination table.
Data type = datetime
Format/Style ='mm/dd/yy'
Example='11/18/07'

I have tried the following query

SQL

SELECT Date,
CONVERT(varchar(50),Date,101) 
FROM staging_table

BUT NOTHING CHANGED .Where am I going wrong ?

Posted 18-Oct-12 23:50pm

Rachana Gaur

Updated 19-Oct-12 0:00am

AshishChaudha

v2

Add a Solution

Comments

MT_ 19-Oct-12 5:54am

This is simple select query , what is the output ?

Rachana Gaur 19-Oct-12 5:57am

Nothing same values gives us

Rachana Gaur 19-Oct-12 5:58am

what should I do for this

Richard MacCutchan 19-Oct-12 5:58am

What do you mean by "NOTHING CHANGED"? Your SELECT statement should have returned a datetime value, what exactly did you see?

Rachana Gaur 19-Oct-12 6:03am

I will see same value like we have 16/03/2012 & After this query I had get 16/03/2012

MT_ 19-Oct-12 6:03am

Show a table of output Rachana for all to understand. As Richard says, it ought to show you US format date (mm/dd/yy) if you convert to 101 fomat.

Rachana Gaur 19-Oct-12 6:07am

But i will get same output as I say 25/11/1987 25/11/1987

30/12/1968 30/12/1968
09/01/1984 09/01/1984
31/01/1983 31/01/1983
08/04/1956 08/04/1956
03/05/1959 03/05/1959
02/02/1988 02/02/1988
9/10/1975 9/10/1975
01/11/1980 01/11/1980
08/10/1987 08/10/1987
22/11/1972 22/11/1972
20/12/1972 20/12/1972
26/03/1999 26/03/1999
25/10/2005 25/10/2005
12/08/1985 12/08/1985
28/08/1988 28/08/1988
04/10/1979 04/10/1979
14/11/1981 14/11/1981
14/11/1981 14/11/1981
20/09/1976 20/09/1976
27/12/2008 27/12/2008
24/12/1970 24/12/1970
12/07/1970 12/07/1970
28/02/1962 28/02/1962
18/08/2000 18/08/2000
10/01/2011 10/01/2011
16/05/1955 16/05/1955
01/07/1957 01/07/1957
12/12/1952 12/12/1952
10/03/1969 10/03/1969
NULL NULL
02/05/1978 02/05/1978
19/10/2004 19/10/2004
24/08/1945 24/08/1945
03/03/1975 03/03/1975
20/11/1944 20/11/1944
18/02/1958 18/02/1958
19/01/1987 19/01/1987
09/09/1986 09/09/1986

12/08/1986 12/08/1986
03/11/1979 03/11/1979
12/06/1966 12/06/1966
07/08/2001 07/08/2001
26/06/1965 26/06/1965
13/09/1966 13/09/1966
19/12/1981 19/12/1981
31/10/1989 31/10/1989
15/06/2012 15/06/2012
06/08/1984 06/08/1984
21/12/1953 21/12/1953
13/11/1959 13/11/1959
14/03/1974 14/03/1974

30/10/1951 30/10/1951
06/03/1956 06/03/1956
08/10/1972 08/10/1972
14/12/1952 14/12/1952
24/01/1982 24/01/1982
15/08/1985 15/08/1985
20/04/1959 20/04/1959
19/07/2001 19/07/2001
06/10/1997 06/10/1997
06/10/1953 06/10/1953
13/02/0960 13/02/0960
06/11/1957 06/11/1957
16/12/1968 16/12/1968
03/12/1999 03/12/1999
25/11/1997 25/11/1997

24/07/1982 24/07/1982
29/08/1971 29/08/1971
28/03/1974 28/03/1974
07/12/1985 07/12/1985
01/11/1980 01/11/1980
10/07/1984 10/07/1984
03/01/1982 03/01/1982
19/06/1961 19/06/1961
26/12/1998 26/12/1998
12/05/2009 12/05/2009
20/06/1967 20/06/1967
17/05/1983 17/05/1983
25/08/2001 25/08/2001
29/12/1986 29/12/1986
17/02/1960 17/02/1960
25/03/1985 25/03/1985
12/02/1987 12/02/1987
26/03/1959 26/03/1959
30/03/1958 30/03/1958
01/03/2002 01/03/2002
25/09/1970 25/09/1970
15/06/1981 15/06/1981
8/1/1979 8/1/1979
11/02/1985 11/02/1985
22/09/1988 22/09/1988
10/12/1986 10/12/1986

V. 19-Oct-12 6:09am

What database are you working with?

Rachana Gaur 19-Oct-12 6:10am

sqlserver 2005

MT_ 19-Oct-12 6:32am

Check my updated solution. This should work.

MT_ 19-Oct-12 9:24am

Were you able to solve the problem ?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MT_ · Answer 1 · 2012-10-19T00:10:00

Rachana,

This is because your SQL is set in different format. Now you Convert has input varchar and output also varchar, hence it is not converting correctly.

First you have to cast it to datetime and then convert it to appropriate date format. See below

SQL

set dateformat dmy
select convert(varchar(50),cast('18/11/07' as DateTime),101)

Hope that helps. If it does, mark the answer as solution and/or upvote.

-Milind

V. · Answer 2 · 2012-10-19T00:19:00

You basically need to

insert into destinationtable select from staging table.

from what I gather this

SQL

SELECT Date,
CONVERT(varchar(50),Date,101) 
FROM staging_table

Will convert it to varchar(50), you need to convert the char from the staging table to a datetime.

SQL

SELECT Date,
CONVERT(datetime,Date,101) 
FROM staging_table

or something similar.
(Apologies if the syntax is not 100% correct, I have no SQL-Server to test with.)

Hope this helps.

PS: IMO this is not a C# question, but an SQL/Database question.

Rahul201050 · Answer 3 · 2012-10-28T23:52:00

Solution 3

SELECT convert(varchar, getdate(), 101) AS Date -- Date is temporary Column name

Posted 28-Oct-12 23:52pm

Rahul201050

Covert date (varchar) to date (Datetime)

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0