Insert string into SQL as datetime without CAST

Question

I am moving data from different source types like Oracle, MySQL, Access, Sharepoint etc to SQL.

A SQL script is generated in c# like this:

with cte (ID, changedate, SupplierID) 
as (
SELECT 1720,  '1997-12-17 12:00:00 AM',  763
UNION ALL
SELECT 1721,  '1900-01-01 12:00:00 AM',  114)
 select * into components8_2_2011_3  from cte

The datatime fields is created as varchar which is wrong.

In C# I can see that the column type is datetime, but MSSQL does not recognize the string as datetime.

How would I go about getting the above script to create a datetime field WITHOUT adding a CAST statement to the changeDate field?

Nope, thats not right: SQL still creates that field as a varchar. — callisto
– callisto, Commented Aug 2, 2011 at 11:40

Alex K. · Accepted Answer · 2011-08-02 12:07:35Z

2

How about using a "guide row"

with cte (ID, changedate, SupplierID) as (
   --type but no data
   select 0, getdate(), 0 where 0=1 union 

   SELECT 1720,  '1997-12-17 12:00:00 AM',  763
   UNION ALL
   SELECT 1721,  '1900-01-01 12:00:00 AM',  114
)
select * into components8_2_2011_3  from cte

edited Aug 2, 2011 at 12:07

answered Aug 2, 2011 at 11:44

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Filip De Vos Over a year ago

change it a little bit to `... from cte where ID <> 0"

callisto Over a year ago

It pains me that I have only one upvote to give: Thanks!! This is exactly the type of solution I was looking for.

gbn · Accepted Answer · 2011-08-02 12:02:48Z

2

You can't. SQL Server doesn't know it's a datetime unless you tell it. Why should it?

For example:

12:00:00 AM is midnight, so is it date? Or smalldatetime for SQL Server 2005?
Will you store a time part? if so, what resolution (smalldatetime, datetime2 etc)
Does this include any timezone information?
Is it UTC or local time?

If you don't want CAST (or CONVERT) then CREATE the table components8_2_2011_3 first

Edit: Alex K's trick is clever, but do you want full datetime for the next 100 million rows when date would be enough...

edited Aug 2, 2011 at 12:02

answered Aug 2, 2011 at 11:44

gbn

435k84 gold badges602 silver badges690 bronze badges

1 Comment

callisto Over a year ago

Alex K's answer fits my problem like a glove. The incoming data for each new table is unknown ito type and size and name etc. a little bit of extra storage used for the temporary table that I create is an acceptable compromise. Thanks for your input as well. have an upvote for good practice being preached.

bummi · Accepted Answer · 2013-12-05 12:18:42Z

DataTier.ClsGlobal dataBaseObj = new DataTier.ClsGlobal();

    string strmessage;
    int intResult;

    public string login(string username, string password)// login method
    {
        dataBaseObj.sqlconn.Open();

        dataBaseObj.sqlCmd = new SqlCommand("SELECT  clEmail, clPassword FROM Client where clEmail='" + username + "'", dataBaseObj.sqlconn);
        dataBaseObj.sqlDr = dataBaseObj.sqlCmd.ExecuteReader();


        if (dataBaseObj.sqlDr.Read())
        {
            if (dataBaseObj.sqlDr["clPassword"].Equals(password.ToString()))
            {
                strmessage = "client";
                dataBaseObj.sqlconn.Close();
            }
            else
            {
                intResult++;

                strmessage = "login  not succesfull";

                dataBaseObj.sqlconn.Close();

                if (intResult == 3)
                {
                    strmessage = "your Blocked";
                }

            }
        }    // if its not the client is gonna go to workers table 
        else
        {
            dataBaseObj.sqlCmd = new SqlCommand("SELECT wuUsername, wuPassword, wuUserType FROM  WorkUsers where wuUsername'" + username + "'", dataBaseObj.sqlconn);
            dataBaseObj.sqlDr = dataBaseObj.sqlCmd.ExecuteReader();

            if (dataBaseObj.sqlDr.Read())
            {
                if (dataBaseObj.sqlDr["wuPassword"].Equals(password.ToString()))
                {
                    strmessage = "Receptionist";
                    dataBaseObj.sqlconn.Close();
                }
                else
                {

                    intResult++;

                    strmessage = "login  not succesful";

                    dataBaseObj.sqlconn.Close();

                    if (intResult == 3)
                    {
                        strmessage = "your Blocked";
                    }
                }
            }
        }
        return strmessage;
    }

    // this method is for registering the client of the client if not registered
    public string registration(string clID, string clFirstName, string clSurname, string clStreetAddress, string clCity, string clPostCode, string clHomePhone, string clMobilePhone, string clEmail, string clPassword, string clStatus)
    {
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlCmd = new SqlCommand("insert into Client values(@clID,@clFirstName@clSurname,@clStreetAddress,@clCity,@clPostCode,@clHomePhone,@clMobilePhone,@clEmail,@clPassword@clStatus)", dataBaseObj.sqlconn);

        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clID", clID);

        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clFirstName", clFirstName);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clSurname", clSurname);


        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clStreetAddress", clStreetAddress);

        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clCity", clCity);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clPostCode", clPostCode);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clHomePhone", clHomePhone);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clMobilePhone", clMobilePhone);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clEmail", clEmail);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clPassword", clPassword);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clStatus", clStatus);

        intResult = dataBaseObj.sqlCmd.ExecuteNonQuery();
        if (intResult == 1)
        {
            strmessage = "registeed";
        }
        else
        {
            strmessage = "not registeed";
        }

        return strmessage;
    }

    // this is for the admin to register the the receptionist
    public string AdminRegister(string Id, string username, string password)
    {
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlCmd = new SqlCommand("INSERT INTO WorkUsers (wuID, wuUsername, wuPassword) VALUES ('" + Id + "','" + username + "','" + password + "')", dataBaseObj.sqlconn);
        intResult = intResult = dataBaseObj.sqlCmd.ExecuteNonQuery();
        if (intResult == 1)
        {
            strmessage = "registeed";
        }
        else
        {
            strmessage = "not registeed";
        }

        return strmessage;
    }

    // this method is fo receptionist to inser an appoirment
    public string ReceptioInsert(int appID, string appDateNow, string appTimeNow, string appReason, string appDateofBooking, string appTimeofBooking, int clID, int wuID)
    {
        dataBaseObj.sqlconn.Open();

       // appDateNow = Convert.ToString(System.DateTime.Now.Year + "/" + System.DateTime.Now.Month + "/" + System.DateTime.Now.DayOfWeek);
        appDateNow = Convert.ToString(System.DateTime.Now.ToString("m"));
        //appTimeNow = Convert.ToString(System.DateTime.Now.TimeOfDay);
        appTimeNow = Convert.ToString(System.DateTime.Now.ToString("hh:mm:ss"));

        dataBaseObj.sqlCmd = new SqlCommand("SELECT appDateNow, appTimeNow FROM  Appointments where appDateNow='" + appTimeNow + "'AND  where appTimeNow='" + appTimeNow + "'", dataBaseObj.sqlconn);
        if (dataBaseObj.sqlDr.Read())// if it reads it means date and time are booked
        {
            strmessage = "sorry that time is not available";
        }
        else
        {

            dataBaseObj.sqlCmd = new SqlCommand("INSERT INTO Appointments (appID, appDateNow, appTimeNow, appReason, appDateofBooking, appTimeofBooking, clID, wuID) VALUES('" + appID + "','" + appDateNow + "','" + appTimeNow + "','" + appReason + "','" + appDateofBooking + "','" + appTimeofBooking + "','" + clID + "','" + wuID + "')", dataBaseObj.sqlconn);
            intResult = intResult = dataBaseObj.sqlCmd.ExecuteNonQuery();
            if (intResult == 1)
            {
                strmessage = "registeed";
            }
            else
            {
                strmessage = "not registeed";
            }
            dataBaseObj.sqlconn.Close();
        }
        return strmessage;

    }
    // this method is to update the appointment
    public void updateAppointmen(int appID, string appDateNow, string appTimeNow, string appReason, string appDateofBooking, string appTimeofBooking, int clID, int wuID)
    {
        dataBaseObj.sqlconn.Open();
        appDateNow = Convert.ToString(System.DateTime.Now.Year + "/" + System.DateTime.Now.Month + "/" + System.DateTime.Now.DayOfWeek);

        appTimeNow = Convert.ToString(System.DateTime.Now.TimeOfDay);
        dataBaseObj.sqlCmd = new SqlCommand("UPDATE Appointments SET  appDateNow =@appDateNow, appTimeNow =@appTimeNow, appReason =@appReason, appDateofBooking =@appDateofBooking, appTimeofBooking =@appTimeofBooking, clID =@clID, wuID =@wuID where appID='" + appID + "'", dataBaseObj.sqlconn);



        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appDateNow", appDateNow);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appTimeNow", appTimeNow);

        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appReason", appReason);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appDateofBooking", appDateofBooking);

        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appTimeofBooking", appTimeofBooking);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clID", clID);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@wuID", wuID);
        dataBaseObj.sqlCmd.ExecuteNonQuery();
    }
    // this method is to delete the appoinment 

    public void deleteAppointment(int appID)
    {
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlCmd = new SqlCommand("DELETE FROM Appointments whre appID='" + appID + "'");
        dataBaseObj.sqlCmd.ExecuteNonQuery();
    }
    // method for client to view the appoinments
    public DataTable CLientViewAppoitnmet(string date)
    {
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlAdt = new SqlDataAdapter("SELECT appID, appDateNow, appTimeNow, appReason, appDateofBooking, appTimeofBooking, clID, wuID FROM Appointments where appDateNow='" + date + "'", dataBaseObj.sqlconn);
        dataBaseObj.dt = new DataTable("appoitment");
        dataBaseObj.sqlAdt.Fill(dataBaseObj.dt);
        dataBaseObj.sqlconn.Close();
        return dataBaseObj.dt;
    }

// this method is for the client to insert a note
    public string CLinetInformRecep(int anID, int appID, string anNotes)
    {
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlCmd = new SqlCommand("INSERT INTO AppointmentNotes (anID, appID, anNotes) VALUES ('" + anID + "','" + appID + "','" + anNotes + "')", dataBaseObj.sqlconn);
         intResult = intResult = dataBaseObj.sqlCmd.ExecuteNonQuery();
            if (intResult == 1)
            {
                strmessage = "registeed";
            }
            else
            {
                strmessage = "not registeed";
            }
            dataBaseObj.sqlconn.Close();
            return strmessage;
    }
      // this is for the receptionis to view notes added vy the client

    public DataTable selectAppointmentNotes()
    {


        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlAdt = new SqlDataAdapter("SELECT FROM AppointmentNotes", dataBaseObj.sqlconn);
        dataBaseObj.dt = new DataTable("appoitment");
        dataBaseObj.sqlAdt.Fill(dataBaseObj.dt);
        dataBaseObj.sqlconn.Close();
        return dataBaseObj.dt;
    }
    // this methiod is for therapist to view cuurent day appointment
    public DataTable TherapisViewAppoiments()       
    {
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlAdt = new SqlDataAdapter("SELECT appID, appDateNow, appTimeNow, appReason, appDateofBooking, appTimeofBooking, clID, wuID FROM Appointments where appDateNow ='" +System.DateTime.Now.DayOfWeek+"'", dataBaseObj.sqlconn);
        dataBaseObj.dt = new DataTable("appoitment");
        dataBaseObj.sqlAdt.Fill(dataBaseObj.dt);
        dataBaseObj.sqlconn.Close();
        return dataBaseObj.dt;
    }
    // this method here is for the  therapist to view cuurent day appointment with oppoetunit to filter them 
    public DataTable ViewAllAppoiements(int id, string date)
    {
        // this method return the information of appointmenet where the id and date matchs since i have user the operatoe AND 
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlAdt = new SqlDataAdapter("SELECT appID, appDateNow, appTimeNow, appReason, appDateofBooking, appTimeofBooking, clID, wuID FROM Appointments where appID ='" + id + "'AND where appDateNow='" + date +"'", dataBaseObj.sqlconn);
        dataBaseObj.dt = new DataTable("appoitment");
        dataBaseObj.sqlAdt.Fill(dataBaseObj.dt);
        dataBaseObj.sqlconn.Close();
        return dataBaseObj.dt;
    }
    // this method is to update appoitment by therapist
    public void UpdateAppoiment(int appID, string appDateNow, string appTimeNow, string appReason, string appDateofBooking, string appTimeofBooking, int clID, int wuID)
    {
        dataBaseObj.sqlconn.Open();
        appDateNow = Convert.ToString(System.DateTime.Now.Year + "/" + System.DateTime.Now.Month + "/" + System.DateTime.Now.DayOfWeek);

        appTimeNow = Convert.ToString(System.DateTime.Now.TimeOfDay);
        dataBaseObj.sqlCmd = new SqlCommand("UPDATE Appointments SET  appDateNow =@appDateNow, appTimeNow =@appTimeNow, appReason =@appReason, appDateofBooking =@appDateofBooking, appTimeofBooking =@appTimeofBooking, clID =@clID, wuID =@wuID where appID='" + appID + "'", dataBaseObj.sqlconn);



        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appDateNow", appDateNow);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appTimeNow", appTimeNow);

        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appReason", appReason);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appDateofBooking", appDateofBooking);

        dataBaseObj.sqlCmd.Parameters.AddWithValue("@appTimeofBooking", appTimeofBooking);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@clID", clID);
        dataBaseObj.sqlCmd.Parameters.AddWithValue("@wuID", wuID);
        dataBaseObj.sqlCmd.ExecuteNonQuery();
    }
    // this method is to delete the appoinmet
    public string deleteAppoinment(int appID)
    {
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlCmd = new SqlCommand("DELETE FROM Appointments whre appID='" + appID + "'");
        dataBaseObj.sqlCmd.ExecuteNonQuery();
        intResult = intResult = dataBaseObj.sqlCmd.ExecuteNonQuery();
        if (intResult == 1)
        {
            strmessage = "registeed";
        }
        else
        {
            strmessage = "not registeed";
        }
        dataBaseObj.sqlconn.Close();
        return strmessage; 
    }
    // report order by date

    public DataTable listOfCLient()
    {
       dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlAdt = new SqlDataAdapter("SELECT clID, clFirstName, clSurname, clStreetAddress, clCity, clPostCode, clHomePhone, clMobilePhone, clEmail, clPassword, clStatus FROM Client", dataBaseObj.sqlconn);
        dataBaseObj.dt = new DataTable("appoitment");
        dataBaseObj.sqlAdt.Fill(dataBaseObj.dt);
        dataBaseObj.sqlconn.Close();
        return dataBaseObj.dt;
    }
    public  DataTable listOfAppoint()
    {
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlAdt = new SqlDataAdapter("SELECT        appID, appDateNow, appTimeNow, appReason, appDateofBooking, appTimeofBooking, clID, wuID FROM Appointments ORDER BY appDateNow", dataBaseObj.sqlconn);
        dataBaseObj.dt = new DataTable("appoitment");
        dataBaseObj.sqlAdt.Fill(dataBaseObj.dt);
        dataBaseObj.sqlconn.Close();
        return dataBaseObj.dt;
    }

    public DataTable listAppontBytherapy()
    {
        dataBaseObj.sqlconn.Open();
        dataBaseObj.sqlAdt = new SqlDataAdapter("SELECT  Appointments.appID, Appointments.appDateNow, Appointments.appTimeNow, Appointments.appReason, Appointments.appDateofBooking,Appointments.appTimeofBooking, Appointments.clID, Appointments.wuID, WorkUsers.wuUserType FROM Appointments INNER JOIN WorkUsers ON Appointments.wuID = WorkUsers.wuID ORDER BY WorkUsers.wuUserType", dataBaseObj.sqlconn);
        dataBaseObj.dt = new DataTable("appoitment");
        dataBaseObj.sqlAdt.Fill(dataBaseObj.dt);
        dataBaseObj.sqlconn.Close();
        return dataBaseObj.dt;
    }
}
}

Collectives™ on Stack Overflow

Insert string into SQL as datetime without CAST

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related