January 20, 2016 at 12:41 pm
Hi friends,
In our application, we use the code below in Oracle database
SET $WeekDay
TO
SELECT to_char($in_date,'DAY')
FROM dual;
where $in_date is variable with date value. This we get week day like Monday, Tuesday etc.
I"m not sure what is the equivalent of this select statement in sql server?
THank you so much
January 20, 2016 at 12:52 pm
--Example
declare @weekday varchar(10)
declare @in_date date='2016-01-20'
select @weekday=datename(weekday,@in_date)
select @weekday
or something like this
declare @weekday varchar(10)
select @weekday=datename(weekday,t.[datetime_column])
from Dual as t where t.ID = [value] -- this should be an unique id
Igor Micev,My blog: www.igormicev.com
January 20, 2016 at 12:57 pm
THank you. In the 1st query, does the date have to be in this specific format or is it possible to change @in_date format? BEcause the input date will be in MM/DD/YYYY format..
Thanks a lot
January 20, 2016 at 1:01 pm
newbieuser (1/20/2016)
THank you. In the 1st query, does the date have to be in this specific format or is it possible to change @in_date format? BEcause the input date will be in MM/DD/YYYY format..Thanks a lot
It can be the one you want, as well as many other datetime formats - https://msdn.microsoft.com/en-us/library/ms187819.aspx
Igor Micev,My blog: www.igormicev.com
January 20, 2016 at 1:04 pm
Thanks got it.
declare @weekday varchar(10)
declare @in_date date='01/20/2016'
select @weekday=datename(weekday,CONVERT(VARCHAR(10),@in_date,101))
select @weekday
January 20, 2016 at 1:06 pm
newbieuser (1/20/2016)
Thanks got it.declare @weekday varchar(10)
declare @in_date date='01/20/2016'
select @weekday=datename(weekday,CONVERT(VARCHAR(10),@in_date,101))
select @weekday
Even if you don't convert it with a style, sql server will recognize it and return the day-name from the date. Just try.
Igor Micev,My blog: www.igormicev.com
January 20, 2016 at 1:12 pm
Oh yes it works !!! THank you
January 20, 2016 at 1:32 pm
I usually avoid using those kind of styles. When possible, keep dates as dates. Otherwise, use ISO 8601 formats which are not language dependent.
For date only use YYYYMMDD
For date and time yyyy-mm-ddThh:mi:ss.mmmZ
January 26, 2016 at 2:20 pm
Igor Micev (1/20/2016)
newbieuser (1/20/2016)
Thanks got it.declare @weekday varchar(10)
declare @in_date date='01/20/2016'
select @weekday=datename(weekday,CONVERT(VARCHAR(10),@in_date,101))
select @weekday
Even if you don't convert it with a style, sql server will recognize it and return the day-name from the date. Just try.
Leaving off the style, the 101, will probably work but it is best to include it as leaving it off can cause problems.
Per your earlier link
When the language is set to us_english, the default order for the date is mdy. You can change the date order by using the SET DATEFORMAT statement.
The setting for SET DATEFORMAT determines how date values are interpreted. If the order does not match the setting, the values are not interpreted as dates, because they are out of range or the values are misinterpreted. For example, 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting. A four-part year is interpreted as the year.
If someone has changed the setting using the SET DATEFORMAT or the server is using a different language you could insert an incorrect date or have the insert fail because it is invalid when the day and month are swapped.
September 9, 2016 at 4:06 am
TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:
DATE values are converted to values in the default date format.
TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.
TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.
The following example uses this table:
CREATE TABLE date_tab (
ts_col TIMESTAMP,
tsltz_col TIMESTAMP WITH LOCAL TIME ZONE,
tstz_col TIMESTAMP WITH TIME ZONE);
The example shows the results of applying TO_CHAR to different TIMESTAMP datatypes.
ALTER SESSION SET TIME_ZONE = '-8:00';
INSERT INTO date_tab VALUES (
TIMESTAMP'1999-12-01 10:00:00',
TIMESTAMP'1999-12-01 10:00:00',
TIMESTAMP'1999-12-01 10:00:00');
INSERT INTO date_tab VALUES (
TIMESTAMP'1999-12-02 10:00:00 -8:00',
TIMESTAMP'1999-12-02 10:00:00 -8:00',
TIMESTAMP'1999-12-02 10:00:00 -8:00');
SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'),
TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM')
FROM date_tab;
TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI:
------------------------------ -------------------------------------
01-DEC-1999 10:00:00 01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00 02-DEC-1999 10:00:00.000000 -08:00
SELECT SESSIONTIMEZONE,
TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF')
FROM date_tab;
SESSIONTIMEZONE TO_CHAR(TSLTZ_COL,'DD-MON-YYYY
--------------- ------------------------------
-08:00 01-DEC-1999 10:00:00.000000
-08:00 02-DEC-1999 10:00:00.000000
ALTER SESSION SET TIME_ZONE = '-5:00';
SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'),
TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM')
FROM date_tab;
TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI:
------------------------------ -------------------------------------
01-DEC-1999 10:00:00.000000 01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00.000000 02-DEC-1999 10:00:00.000000 -08:00
SELECT SESSIONTIMEZONE,
TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF')
FROM date_tab;
SESSIONTIMEZONE TO_CHAR(TSLTZ_COL,'DD-MON-YYYY
------------------------- ------------------------------
-05:00 01-DEC-1999 13:00:00.000000
-05:00 02-DEC-1999 13:00:00.000000
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply