Try this
Cast('7/7/2011' as datetime)
and
Convert(DATETIME, '7/7/2011', 101)
See CAST and CONVERT (Transact-SQL) for more details.
Answer from gauravg on Stack Overflowt sql - Sql Server string to date conversion - Stack Overflow
sql server - T-SQL Cast versus Convert - Stack Overflow
How to use Convert?
ON convert(date, timestamp) = convert(date, create date)
More on reddit.comShould i convert my CSV files into SQL tables?
What’s your goal? If you are doing one-off data analysis it really doesn’t matter if you load the CSVs into a db or not. If you’re trying to build a portfolio project for data engineering, loading them into a db is a great idea as it shows you are capable of that type of work.
If you’re unfamiliar, SQLite is a really great db for small projects, as the entire db is kept in a single file.
More on reddit.comVideos
Try this
Cast('7/7/2011' as datetime)
and
Convert(DATETIME, '7/7/2011', 101)
See CAST and CONVERT (Transact-SQL) for more details.
Run this through your query processor. It formats dates and/or times like so and one of these should give you what you're looking for. It wont be hard to adapt:
Declare @d datetime
select @d = getdate()
select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,12),12,'yymmdd'
union all
select @d,convert(varchar,@d,112),112,'yyyymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'
CONVERT is SQL Server specific, CAST is ANSI.
CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.
EDIT:
As noted by @beruic and @C-F in the comments below, there is possible loss of precision when an implicit conversion is used (that is one where you use neither CAST nor CONVERT). For further information, see CAST and CONVERT and in particular this graphic: SQL Server Data Type Conversion Chart. With this extra information, the original advice still remains the same. Use CAST where possible.
CONVERT has a style parameter for date to string conversions.
https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
Hey all, I’m trying to understand Convert. For reference I have 0 computer science training and I can do BASIC queries for searching as part of my job.
So more details - I have 3 tables. A stock price table, a table that records stock price awards (grant table) for a person, and an activity table that records different events on that stock award. Grant and activity table can be joined by GRANT_ID.
What I’m trying to do in with a select query is assign a stock price to certain activities (not via insert). my create date on the activity table is a timestamp (ex: 05-APR-24 12.00.00.0000000) as is the create date on the stock price table. My problem is that if I join the stock price table and activity table by dates nothing will happen because of the timestamp, they won’t match. And I don’t care about the time, I just care about the date portion.
So- would CONVERT help me at all? And if so, how do I just do something like “join stock price table and activity table where the Dates only match, not the time”
I hope I’m making a shred of sense, thanks in advance!
ON convert(date, timestamp) = convert(date, create date)
If your post is labeled correctly and you are indeed using the Oracle Database, you would not want to use its CONVERT function, which converts a character string’s (such as VARCHAR2) character set from one to another. See: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CONVERT.html One thing that’s important to understand is a function on one DBMS could have the same name on another, but do something completely different.
That said, one way in which you could solve the problem is to truncate the timestamp to the day level, as in this SQL Fiddle example: https://sqlfiddle.com/oracle/online-compiler?id=0ebf87b1-bc93-4b34-a994-a6d50d9f2c2b