How to Use "Date" Datatype in SQL Server

How Can I use Date Datatype in sql server?

You do not have a problem with the "last two".

However, you do have a problem with all of them, but one point after the other.

Literal dates are dependent on your system's culture

Your dates are interpreted as MM-DD-YYYY. This intrepretation is depenent on your system's culture. The first three are turning into wrong dates but work. The 4th breaks and the fifth is never executed (due to the error before).

So the actual error lies on line 4.

Whenever you deal with dates, use culture independent formats. It is better to use either of the following-

universal format

20150730 (=> the 30th of July in 2015)

ODBC-format

{d'2015-07-30'} or {t'23:30:59'} or {ts'2015-07-30 23:30:59'}

ISO 8601

'2015-07-30T00:00:00'

How to use the 'date' data type in SQL Server

Your problem is that you have some data in your table with old format '552005', so when you want to change the column type you get error. So you have two options:

1- Delete anything from that table, change the column type and start saving dates as datetime or datetime2.

2- Convert all of the current data to a date string eg. '05/05/2005 00:00:00' and then change the column type to datetime.

UPDATE:

Also don't forget to add data type to your SqlParameters:

SqlParameter dob = new SqlParameter("@sinceDateTime", SqlDbType.DateTime);
dob.Value = new DateTime( Int32.Parse(yearDobList.Text), Int32.Parse(monthDobList.Text), Int32.Parse(dayDobList.Text));
ca.Parameters.Add(dob);

SQL: how to specify a date format on creating a table and fill it

You don't need to specify the format in the table definition as dates are stored in a binary format.

CREATE TABLE APP(
    ID   INT NOT NULL,
    DT DATE,
    ADDRESS  NVARCHAR (100) ,
    PRIMARY KEY (ID)
);

When you try to insert into that table however, the server will try to convert the string to a date before inserting it. This can be problematic as it is unable to tell if 12.11.2017 is the 12th of November or 11th of December. To figure this out it uses the localization settings of the user account that is performing the operation.

Often you will find that the account that is running the operation is set to USA format, month day then year (MDY), when what you want is day month year (DMY) format. One way to tell it what the sequence of the date's parts is to use the DATEFORMAT setting like this:

SET DATEFORMAT dmy;

INSERT INTO APP (ID, DT)
VALUES (1,'22.12.2016')

Another alternative is to cast the string to a date using the CONVERT function and tell it what the date format is. The formats have numeric codes like 104 for German format Like this:

INSERT INTO APP (ID, DT)
VALUES (2,CONVERT(date,'22.12.2016',104))

How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

• No varchar<->datetime conversions required
• No need to think about locale

SQL Server - Cast specific string date format to date datatype

Since date suggests you don't care about time:

SELECT CONVERT(date, LEFT('20220122T090159', 8));

If you need time too, it gets quite ugly.

DECLARE @funny_string char(15) = '20220122T090159';

SELECT TRY_CONVERT(datetime, 
  LEFT(@funny_string, 8) + ' ' 
  + STUFF(STUFF(RIGHT(@funny_string, 6), 3, 0, ':'),6, 0, ':'));

How to select date without time in SQL

I guess he wants a string.

select convert(varchar(10), '2011-02-25 21:17:33.933', 120)

120 here tells the convert function that we pass the input date in the following format: yyyy-mm-dd hh:mi:ss.

---

Related Topics