February 6, 2020 at 6:22 pm
Hello All,
I am trying to get the date column in the format 'MM/DD' from the data column. However, it is of type varchar(50) so I am converting it to DateTime and again converting it to 101 to retrieve the format I need. Here is the syntax I am using
CONVERT(varchar(5), CONVERT(datetime, alertQueue.[Pickup_Date], 126), 101) AS PickupDate
So my question is there any other better way of implementing it? and it is okay to use two times of Convert functions or converting two times in terms of coding and performance-wise.
Please suggest? Thanks for your help and reading this and have a great day!
February 6, 2020 at 6:44 pm
What format is it in now? YYYYMMDD? MM/DD/YYYY? etc
Also, why not use a DATE (or DATETIME) column to store dates? That's what they're for. Makes everything so much easier.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 6, 2020 at 6:46 pm
Yeah, I agree with you DateTime or Date column datatypes are good. But not sure who created this table this is what it is. Right now it is YYYY-MM-DD
February 6, 2020 at 6:49 pm
Just CAST() the column as a date, that gives you the most flexibility with the format of the column:
CONVERT(varchar(5), CAST(alertQueue.[Pickup_Date] AS date), 101) AS PickupDate
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 6, 2020 at 6:49 pm
Like this?
DECLARE @x VARCHAR(50) = '2020-02-06'
SELECT @x, REPLACE(RIGHT(@x,5),'-','/')
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 6, 2020 at 9:54 pm
Thanks, it works.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply