CODEWITHSUNDEEP
sql
Animesh
Animesh

Reputation: 29

SQL query for date formats

Can anyone help me in writing a query for creating a table which will store date in the format of month/date/year (mm/dd/_ _ _ _)?

I do not want to use varchar, because I will be needing to compare my dates also. I also do not want to use any other date formats because my inputs are being entered in the format of month/date/year (mm/dd/_ _ _ _)?

Upvotes: 0

Views: 94

Answers (3)

nish
nish

Reputation: 1221

You can use varchar and it can be converted using the option 101 to the format you want (mm/dd/yyyy).

SELECT CONVERT(varchar(10), CONVERT(datetime, '2017/10/02', 111), 101)

Upvotes: 2

sam7
sam7

Reputation: 56

You can use varchar and then convert it to your desired format. Here is a sample query.

DECLARE @s DATETIME

SELECT @s = CONVERT(DATETIME, '03/13/2013', 101)

Upvotes: 0

shortchng
shortchng

Reputation: 66

The answer is going to depend on what type of database you are using (SQL Server, MySQL, PostgreSql, etc.) and whether you are doing this with 100% SQL, or if you have an application that uses another language that could manipulate the data as well.

Assuming this is an all SQL application, if you need to compare the dates, you will likely need to use the native Date data type for whatever database you are using.

By contrast, you could store it as a varchar, but then would need to cast it as a date for comparison.

To get the date into the format the you are needing, again depending on the database, you may have some sort of date_format function available to input/output the date in the format that you need. If there is not a native function for your database, someone has likely come up with a solution for that database. Searching for your database type (MySQL, Postgresql, etc.) and "date format" will be a good starting point.

If your application is also using another language, it may also have some native functions to convert the date into the format that you need.

I know this didn't directly answer your question, but hopefully gets you thinking about different ways to solve the problem and gets you pointed in the right direction.

Upvotes: 0

Related Questions